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:
These fields are called Status and Description respectively.
The query I'm filtering is called
These are the names of my Command buttons:
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
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
qryCorrespondence.NID are available in the SELECT statement of
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.Answer2:
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:
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