Question:

I've got myself in a right mess following all kinds of semi-relevant tutorials trying to figure out how to send combo box fields residing on a form to a query.

I'm using the query to generate subsets of contact details from which to make labels for mailshot campaigns. I'd like the comboboxes to be either null or used in conjunction, so I can filter on combinations of choices. I've got my comboboxes doing a lookup to retrieve their values and found a good tutorial on working with IS NULL, but I don't understand the VBA necessary create 'view results' and 'clear form' command buttons.

These are the names of my comboboxes as referenced in the query fields criteria:

[Forms]![SearchForm]![cboStatus] [Forms]![SearchForm]![cboNewsletter]

These fields are called Status and Description respectively.

The query I'm filtering is called

qryFilter

These are the names of my Command buttons:

cmdResults cmdClear

cmdResults should send each of the combo box values to the query whether they are null or selected, but I cant get it working and clear should make the comboboxes null.

I'd be looking to expand this to include more criteria but I want to get it working first!

Any help is appreciated, thanks in advance, Rob

EDIT: Tried to adapt this code from Patrick:

Private Sub cmdResults_Click() Dim tsSql As String tsSql = "SELECT * FROM qryAll WHERE " If cboNewsletter <> "" And Not IsNull(cboNewsletter) Then tsSql = tsSql & "qryCorrespondence.NID = " & cboNewsletter & " " If (cboStatus <> "" And Not IsNull(cboStatus)) Then tsSql = tsSql & " AND " End If End If If cboStatus <> "" And Not IsNull(cboStatus) Then tsSql = tsSql & "tblCustomers.Status = " & cboStatus & " " End If Dim rs As New ADODB.Recordset rs.Open tsSql, CurrentProject.AccessConnection, 3, 3 End Sub

The last line gives me an error though, it highlights the following line in the debugger:

rs.open tsSql, CurrentProject.AccessConnection, 3, 3,

And says syntax error in WHERE clause

Any suggestions?

This is a typical situation for a Query By Form interface. My practice is to have a subroutine in the form's module that writes the WHERE clause, something like this:

Private Function GetWhere() As String Dim strTemp As String If Not IsNull(Me!cboStatus) Then strTemp = strTemp & " AND tblCustomers.Status = " & Chr(34) & Me!cmbStatus & Chr(34) End If If Not IsNull(Me!cboNewsletter) Then strTemp = strTemp & " AND qryCorrespondence.NID = " & Chr(34) & Me!cboNewsletter & Chr(34) End If strTemp = Mid(strTemp, 6) If Len(strTemp) > 0 Then GetWhere = " WHERE " & strTemp End If End Function

(the code above assumes that tblCustomers.Status and qryCorrespondence.NID are available in the SELECT statement of qryAll)

In the OnClick() event of cmdResults, you would use it this way:

Dim strSQL As String strSQL = "SELECT * FROM qryAll" strSQL = strSQL & GetWhere() [do with strSQL whatever you want]

The above code would return all records if neither combo box has a value selected.

For your cmbClear, you'd just have code that sets the two combo boxes to Null.

There really is no clean way to do this in Access.

Say I have three combo boxes named:

cmbName cmbCity cmbState

And a button named:

btnDoWork

Now If I want to run a query based on the contents of the combo buttons based on the button click It could look something like this:

Private Sub btnDoWork_Click() Dim tsSql as String tsSql = "SELECT * FROM tblUser WHERE " If cmbName <> "" and Not ISNull(cmbName) Then tsSql = tsSql & "user_name = " & cmbName & " " if (cmbCity <> "" and Not IsNull(cmbCity)) or (cmbState <> "" and Not IsNull(cmbState)) tsSql = tsSql & " AND " end if End If if cmbCity <> "" and not isnull(cmbcity) then tsSql = tsSql & "city = " & cmbcity & " " if cmbState <> "" and Not IsNull(cmbState) then tsSql = tsSql & " AND " end if end if if cmbState <> "" and not is null(cmbState) then tsSql = tsSql & "state = " & cmbState end if MyControl.RowSource = tsSql End Sub

I'm sure you can alter it to include nulls if you like. My version excludes nulls.

If you are trying to return the results in a multicolumn list box or some other control you would need to make sure that the controls Row Source Type is set to Table/Query, and you would set the controls source as such:

MyControl.RowSource = tsSql