Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2002

    Unanswered: querydefs in VBA

    I would like to build a query in VBA that uses a text string criteria given in a combo box yet if the combo box is empty gives all records.
    I realise that i could use an if statement and write two different querydefs but there are 4 combo boxes and so the combinations are numerous.

    Below is the code that builds the query that selects the records that correspond to what is entered into the combo box. What i would like to know is what to add to this code to get all records if the combo box is empty.

    Thanks very much

    Dim db As Database
    Dim rs As Recordset
    Dim qry As QueryDef
    Dim strSql As String
    Dim strCode, strAdd, strPostcode, strName As String

    strCode = Me.cboAdvisorCode

    strSql = "SELECT tblsalesrecord.AdvisorCode, tblsalesrecord.ContractDate, tblsalesrecord.CustomerName, tblsalesrecord.Address1, tblsalesrecord.Postcode " _
    & "FROM tblsalesrecord " _
    & "WHERE ((tblsalesrecord.AdvisorCode) = " & chr(39) & strCode & chr(39) & ")"

    Set qry = db.CreateQueryDef("qrytest", strSql)

  2. #2
    Join Date
    Oct 2001
    Bay Area, CA
    If you do it in VBA, it should be easy for you to test if the combo box is empty

    if isnull(me.combo1) = true then ...
    if not len(me.combo1) > 0 then ....

    But I would try to fix this in a query where you reference the combo boxes.

    SELECT * FROM tblData WHERE Field1 Like "*Forms!frmMain!Combo1" AND Field2 Like "*Forms!frmMain!Combo2";

    if the fields is empty, the data will be filtered by *, and thus selects all records

    Hope this helps...

  3. #3
    Join Date
    Jan 2002
    Works a treat. Thanks for that I think i was over complicating things.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts