42575

Controlling an Access SQL query from multiple combo boxes on a form

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?

Answer1:

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.

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:

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

Recommend

  • Write Query to Compare time in google fusion table
  • Filtering Attributes with Weka
  • Angular Material - Getting index of row in data table
  • Laravel - Dynamic relationship using hasManyThough() and unique merge
  • App Script HtmlService use with font like Google Web Fonts?
  • Is there a greater chance to collide when comparing GUIDs based on a hash vs \"Guid.NewGuid()?
  • Is it possible to collect a stream to two different collections using one line?
  • How to show a specific VC , more like navigate to a stack of VC
  • SVG Fill Width to Child Elements
  • Javascript - Waiting for event before proceeding
  • Merging a large number of files from one directory into a data frame in R
  • How many percent of the tweets does twitter sample API give?
  • Applescript to ping test each client prior to ssh connection
  • Displaying pdf files using the PDFKit interface
  • How to authenticate user name and password against Active Directory Federation Services (ADFS)?
  • How To Delete All Words After X Characters
  • HABTM associations in Rails : collecting and counting the categories of a model's children
  • Plotting A Hyperboloid
  • Extjs, handling success or failure when doing a standard submit in a form
  • How to change default stop edit behavior in jtable
  • Run EF6 Query in separate Thread on WinForm Button Click Event
  • Adding independent aspx/asmx pages into DotNetNuke
  • Merge Module leaving files during uninstall
  • xtable - background colour of added rows
  • Symfony2 - Doctrine - no changeset in post update
  • Lock Horizontal View
  • Find group of records that match multiple values
  • JqueryMobile Popup menu is not working
  • Run multiple queries from 1 SQL file showing result in multiple tables
  • Copy to all folders batch file?
  • gspread or such: help me get cell coordinates (not value)
  • Custom Tabgroup Appcelerator
  • Make VS2015 use angular-cli ng at build time in a .NET project
  • PHPUnit_Framework_TestCase class is not available. Fix… - Makegood , Eclipse
  • Projection media query: browser support and workarounds?
  • Jenkins: How To Build multiple projects from a TFS repository?
  • Different response to non-authenticated users and AJAX calls
  • How do I fake an specific browser client when using Java's Net library?
  • Apache 2.4 - remove | delete | uninstall
  • Run Powershell script from inside other Powershell script with dynamic redirection to file