linksbad.blogg.se

There really is no clean way to do this in Access. The above code would return all records if neither combo box has a value selected.įor your cmbClear, you'd just have code that sets the two combo boxes to Null. In the OnClick() event of cmdResults, you would use it this way: Dim strSQL As String

(the code above assumes that tblCustomers.Status and qr圜orrespondence.NID are available in the SELECT statement of qryAll) StrTemp = strTemp & " AND qr圜orrespondence.NID = " & Chr(34) & Me!cboNewsletter & Chr(34) StrTemp = strTemp & " AND tblCustomers.Status = " & Chr(34) & Me!cmbStatus & Chr(34) 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 This is a typical situation for a Query By Form interface. The last line gives me an error though, it highlights the following line in the debugger: rs.open tsSql, CurrentProject.AccessConnection, 3, 3, Rs.Open tsSql, CurrentProject.AccessConnection, 3, 3 TsSql = tsSql & "tblCustomers.Status = " & cboStatus & " " If cboStatus "" And Not IsNull(cboStatus) Then If (cboStatus "" And Not IsNull(cboStatus)) Then TsSql = tsSql & "qr圜orrespondence.NID = " & cboNewsletter & " " If cboNewsletter "" And Not IsNull(cboNewsletter) Then Tried to adapt this code from Patrick: Private Sub cmdResults_Click() I'd be looking to expand this to include more criteria but I want to get it working first!Īny help is appreciated, thanks in advance, These are the names of my Command buttons: cmdResultsĬmdResults 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. The query I'm filtering is called qryFilter

These fields are called Status and Description respectively. These are the names of my comboboxes as referenced in the query fields criteria: !! 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. I'd like the comboboxes to be either null or used in conjunction, so I can filter on combinations of choices. I'm using the query to generate subsets of contact details from which to make labels for mailshot campaigns. 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.