Access 97 - query by form using many combo boxes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I know I have to use code or sql for this as there are approx 30 combo boxes.
I have been looking for similar examples...with no luck...The user will
select the information they have, by the combo box on the search form. The
query should then locate only the records that match what the user has
selected. any insight, nudge in the right direction would be very very much
appreciated.
 
Darren said:
I know I have to use code or sql for this as there are approx 30
combo boxes. I have been looking for similar examples...with no
luck...The user will select the information they have, by the combo
box on the search form. The query should then locate only the
records that match what the user has selected. any insight, nudge in
the right direction would be very very much appreciated.
Click to expand...

What I typically do is build the query SQL in code.

Dim strSQL as String

strSQL = "SE:ECT * FROM TableName WHERE 1 = 1 "

If Not IsNull(Me.CboBox1) Then _
strSQL = strSQL & "AND SomeField = " & Me.cboBox1 & " "

If Not IsNull(Me.CboBox2) Then _
strSQL = strSQL & "AND SomeOtherField = '" & Me.cboBox1 & "' "

etc...

What I'm doing is creating a base SQL statement that will return all records and
then each ComboBox is tested for null. Those that have something entered cause
an additional criteria to be added to the SQL string. Those that are null are
ignored.

When the code has tested all 30 ComboBoxes the string variable will contain a
valid SQL statement that only filters on the choices that the user made. You
then use the SQL string directly (to create a RecordSet) or use it to set the
SQL Property of a previously saved query and then use the query.

Another variation is to use this method to create just the WHERE clause that can
be used with DoCmd.OpenForm or DoCmd.OpenReport.
 
Rick thanks for your reply. (Sorry it took so log to say so)

Unfortunately this goes way past my current knowledge level...I have a lot
more to learn it would seem...I understand what you were trying to pass on to
me.

I'm just not able to figure out how to the sqlSQL statement.
 
Back
Top