MS Access cutom query using VB code

Here's a sample from one of my forms.

Private Sub cmdViewResults_Click()
   On Error GoTo Err_Proc
    Call BuildSQL
    DoCmd.OpenForm "frmSearchResults", , , , , , Me.name
    Me.Visible = False

Exit_Proc:
   On Error GoTo 0
   Exit Sub

Err_Proc:
    Select Case Err.Number
        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdViewResults_Click of VBA Document Form_frmClientSearch"
    End Select
End Sub
Public Sub BuildSQL()
Dim strSQL As String
Dim strSelect As String
Dim strSelectForExport As String
Dim strWHERE As String
Dim strCondition As String
Dim strOrderBy As String

    strSelect = "Select * from qClientList "
    strSelectForExport = "Select * from qClientListExport "
    strOrderBy = " ORDER BY FullName"
    strWHERE = ""
    
    If Me.txtFirstName & "" = "" Then
    Else
        strWHERE = "FirstName Like " & QUOTE & Me.txtFirstName & "*" & QUOTE
    End If
    If Me.txtLastName & "" = "" Then
    Else
        strCondition = "LastName Like " & QUOTE & Me.txtLastName & "*" & QUOTE
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.txtcity & "" = "" Then
    Else
        strCondition = "City Like " & QUOTE & Me.txtcity & "*" & QUOTE
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.txtaddress & "" = "" Then
    Else
        strCondition = "Address Like " & QUOTE & Me.txtaddress & "*" & QUOTE
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.chkBUP = True Then
        strCondition = "BUP = True"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.cboCareMgrID & "" = "" Then
    Else
        strCondition = "CareMgrID = " & Me.cboCareMgrID
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.cboGenderID & "" = "" Then
    Else
        strCondition = "GenderID = " & Me.cboGenderID
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.txtZip & "" = "" Then
    Else
        strCondition = "Zip = '" & Me.txtZip & "'"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If

    If Me.cboRaceID & "" = "" Then
    Else
        strCondition = "RaceID = " & Me.cboRaceID
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.cboDiagnosis & "" = "" Then
    Else
        strCondition = "Diagnosis = '" & Me.cboDiagnosis & "'"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.txtpid & "" <> "" Then
        strCondition = "PID = '" & Me.txtpid & "'"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.txtClientID & "" <> "" Then
        strCondition = "ClientID = " & Me.txtClientID
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.txtems & "" <> "" Then
        strCondition = "EMS = '" & Me.txtems & "'"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
        
    If Me.txtPhone & "" <> "" Then
        strCondition = "(Phone = '" & Me.txtPhone & "' OR CellPhone = '" & Me.txtPhone & "')"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    
    If Me.cboCounty & "" <> "" Then
        strCondition = "County = '" & Me.cboCounty & "'"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
        
    If Me.cboLevelID & "" <> "" Then
        strCondition = "LevelID = " & Me.cboLevelID
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    
    If Me.cboTier & "" <> "" Then
        strCondition = "PCATier = " & Me.cboTier
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    
    If Me.cboProgramID & "" = "" Then
    Else
        strCondition = "ProgramID = " & Me.cboProgramID
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    
    'Funding
    If Me.chkT19 = True Then
        strCondition = "T19 = True"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.chkSelf = True Then
        strCondition = "Self = True"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.chkMFP = True Then
        strCondition = "MFP = True"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    
    If Me.chkNOTT19 = True Then
        strCondition = "Not(T19 = True)"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.chkNOTSelf = True Then
        strCondition = "Not (Self = True)"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.chkNOTMFP = True Then
        strCondition = "Not(MFP = True)"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If

    Select Case Me.fraStatus
        Case 1
            strCondition = "StatusID = 1318"          'Open
        Case 2
            strCondition = "StatusID = 1319"          'Closed
        Case 4
            strCondition = "StatusID = 1427"          'Pending
        Case Else
            strCondition = ""
    End Select
    If strWHERE = "" Then
        If strCondition = "" Then
        Else
            strWHERE = strCondition
        End If
    Else
        If strCondition = "" Then
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    
    
'query for form/report
    If strWHERE = "" Then
        strSQL = strSelect
    Else
        strSQL = strSelect & " WHERE " & strWHERE
    End If

    strSQL = strSQL & strOrderBy
    Me.txtQuery = strSQL
    
'query for Excel
    If strWHERE = "" Then
        strSQL = strSelectForExport
    Else
        strSQL = strSelectForExport & " WHERE " & strWHERE
    End If
    
    strSQL = strSQL & strOrderBy
    Me.txtQueryExport = strSQL
End Sub

Select allOpen in new window

When creating query SQL strings with code ("Dynamic SQL Generation"), be aware of what happens if you put the SQL in a querydef (Access DAO query object) as compared to just setting the form's Recordsource or the control's Rowsource property to the SQL string.

Putting the SQL string in a querydef object and using that querydef as a recordsource/rowsource is NOT something you can do if you are operating a shared Access database application.  If more than one person is running the app, whoever changes the SQL property of the querydef last WINS!  (EVERYONE suddenly sees their query results change to the latest SQL results!)  Changing the SQL in a querydef is NOT something you want to do in a shared Access app.

However, the SQL string as a Recordsource/Rowsource IS a shared app technique.  It is private to just the one user's session - no one else is affected by it.  Be aware that the length of text in a Recordsource/Rowsource is limited, so your query text can't be too long.  Use short data source aliases to minimize the number of characters ([tblLongTableName] AS [tblLTN]).  You can also create a recordset and set the form or control's query source to the recordset (Set frmFormName.Recordsource = rsDAORecordset)

Just thought I'd throw that in to prevent someone from learning it the hard way as I did.

<<
Putting the SQL string in a querydef object and using that querydef as a recordsource/rowsource is NOT something you can do if you are operating a shared Access database application
>>

With a standard/proper split database configuration (tables in a shared back-end, queries/forms/reports in a separate front-end on individual user's computers), this is not a problem.

(If you have a shared user interface in a multi-user environment, you should split the database.)

What I meant by a "shared Access database application" is a shared front-end.  Doesn't matter if the db is split or not, only if two or more people have the same database file open at the same time.  However, it is recommended that you use a split database design is a multi-user, distributed environment.

Giving each user his own front-end, however, does solve the problem that you have with shared front-ends, but we don't know what situation the author has since he didn't say.

I'll let him choose what he thinks best since the rest of us don't know his whole situation.

Sorry,
I should have added that the sql string is placed in a control on the search form and the display form picks it up and uses it as its RecordSource.  There is no point in creating a querydef on the fly.

When building SQL statements in code, it's helpful to keep the "base version" (Select clause) of the query in a property, then just change the parts you need, like the Where clause.  But how do you find and replace the Where clause?

We've written code to automatically find and replace the Where clause in a SQL statement.  It's a free download called "J Street SQL Tools" on our free J Street Downloads page at www.JStreetTech.com/downloads.

Take the text and paste it into a new module called basJStreetSQLTools.

To use it, try the function ReplaceWhereClause.  You send in a whole SQL statement and the new desired Where clause, and it locates and snips out the old one, inserts your new one, and gives you back the new statement.  If you send in a null or empty Where clause, the function just removes any existing one from the statement.

Rebuilding a whole SQL Statement in code is laborious and often leads to hard-to-diagnose errors.  Our replacement technique allows you to store the whole query structure in a property or saved query without having to rebuild it each time in code.  All you have to do is rebuild the Where clause.  It's one of the most useful functions we've ever written - it's used in every single Access application we build.  It works with both Access and SQL Server query syntax, so it works fine on passthrough queries too.

By the way, there's also a ReplaceOrderByClause function that does the same for sorting.

Cheers,
Armen Stein
J Street Technology

I am a novice VB developer and new to Access.
I am also going to use the database as a shared database for MAC and Windows users that will access the database from a terminal server session.
Sorry but I am not comprehending a lot of the code with the arbitrary field names.
A little more detail may help to clarify.
I have a media database with the following fields - Subject, Orientation, Environment, Season, Angle, Miscellaneous and File Name.
I want to search matches for Subject, Orientation, Environment, Season, Angle and Miscellaneous and add the File Names found to a list box.
Please comment as much of the code as possible.

Don,
If you need someone to write the code for you, you should probably find an expert and pay him.  If you want to learn how to do this yourself, you are going to need to jump in and try to understand the VBA code that I posted.  I picked a search form that had a lot of fields with names that you should recognize.  There are also a bunch that are application specific but which might make sense to you such as "Diagnosis" while others such as "PID" won't ring any bells.  The point is that the code gives different kinds of search example such has how to do a partial search using an address field so you can search for "Main St" without having to know the specific house number.  There are others that are numeric, dates and full valued text strings.

Try to pull something together using your own field names.  We'll be happy to look at it and help you out.

How do I add the results of the query for a given field to a listbox on my form ?

You set the RowSource of the combo to the SQL string that you constructed.

Me.SomeCombo.RowSource = strSQL

How will that query just retrieve data from one field that matches all the other fields I selected for the query ?

Queries retrieve whatever you tell them to.

Select fld1, fld2, fld3 From tbl1;   --- retrieves ALL rows in the table

Select fld1, fld2, fld3 From tbl1 WHERE fldx = Forms!yourform!txtfldx;  --- retrieves only rows where fldx = the value in the text box on the referenced form.

The code I posted builds a string similar to the second one.  There may be more than one criteria.  Unless you want to drive yourself crazy, the criteria will ALWAYS be connected either by all AND's or all OR's depending on your desired output.

I understand how the query command works now and I have built the code to create the query string to retrieve all entries that match the criteria users input.
I just don't know how to add all the results of one of the retrieved fields to a list box.
I would assume I need a record counter loop to populate the results to the list box ??

Thank you all for guiding me to the solution

You're welcome.