Query Criteria From Multiple Combo Boxes on User Form

[sherlocked]

Ok, so.

I have a user form with six different dropdown boxes. I would like to create a query that gets its criteria from users selecting values from one or more of these boxes.

I have tried:

[forms]![frmName]![comboboxname] or [forms]![formName]![comboboxname] is null

in the corresponding query fields and get a "query is too complex" error message, even when only selecting one criteria.

What am I doing wrong? :banghead: Your help is greatly appreciated!

 

[MarkK]

I doubt if you can do that effectively without using VBA to build the query's WHERE clause on the fly. The problem is that if a user doesn't select anything in one of the combos, then that constraint should be completely absent from the query, and that's impossible when you pre-write the query.

What I usually do is write a custom property for each criteria, which returns the SQL for that criteria if there is data, or "" if the control is null. So say I have a table of people and I want to search by < BirthYear (so older than) and Lastname contains, I might do . . .

Property Get BirthYearSQLCriteria as String
   If Not IsNull(Me.cboBirthYear) Then
      BirthYearSQLCriteria = "OR " & Me.cboBirthYear & " <= BirthYear "
   End If
End Property

Property Get LastnameContainsSQLCriteria as String
   If Not IsNull(Me.txtLastName) Then
      LastnameContainsSQLCriteria = "OR Lastname LIKE '*" & Me.txtLastName & "*' "
   End If
End Property

. . . so you can see how those return valid snippets of a WHERE clause based on the values in the search controls. Then you can construct a where clause on the fly like . . .

Property Get SQLWhere As String
   dim tmp as string
   tmp = Me.BirthYearSQLCriteria & me.LastnameContainsSQLCriteria
   If tmp <> "" then SQLWhere = "WHERE " & Mid(tmp, 4)
End Property

. . . and then it's easy enough to combine that with an SQL select clause, like . .

Property Get SQL as string
   SQL = _
      "SELECT * FROM MyTable " & _
      Me.SQLWhere
End Property

. . . and then on every after update of your search controls, you can change the RowSource of a list . . .

Private Sub txtLastName_AfterUpdate()
   Me.lstSearchResults.RowSource = Me.SQL
End Sub

Hope this helps,

 

[MarkK]

One huge advantage, if you program with classes and you use the locals window for debugging, is that custom Properties of your objects ARE ALL evaluated and displayed in that view. Functions, being methods of an object, are not. So if your classes contain properties, including properties that expose other classes, the treeview in the locals windows becomes an incredibly rich diagnostic tool.

I've attached a jpg of the locals window view of a cOrder class I'm currently working on. I've expanded the cCustomer and cOrderStatus nodes (which are classes exposed by the cOrder class as properties) and all the values you see are custom properties, even the one that results in an error!!!

 

[sherlocked]

Thanks for your replies, however I am a total beginner with VBA and don't know how to interepret your instructions at all.

Can we spoon-feed this in some way so that I can be sure to get it right?