Creating a macro to run a query with criteria entered into a form

[Derek Wittman]

I have a simple database (4 real tables) that I would like
to create a query on. My goal is this - to create a form
that, when options (buttons?) are selected, these options
are considered for criteria. When the GO button is
clicked, the criteria are used in either OR or AND,
depending on the need of the user. Basically, I want this
to be a seamless approach for Joe User to be able to run
queries without having to set them up.

For example, Joe can depress a Candy option (radio) button, and a
Selection option (radio) button, then click GO and the "desc" field in
the 'behind-the-scenes' query will be

(Like *Candy* or Like *Repack*) and Like *Selection*

If I understand correctly, I need to pass the criteria
through a Macro, which will execute the query WITH the
criteria selected. Can someone help? (I have NO VBA
programming, but a little SQL)

 

[Steve Schapel]

Derek,

First of all, this is not a job for a macro. If you only wanted to
base your query's output to be based on the data criteria selections
on the form, this would be pretty easy to write directly into the
criteria in the design view of the query. But if you want the user to
be able to designate 'And' or 'Or' operators, you will need to use a
VBA procedure to construct the WHERE clause of your query. The
process of doing so is not simple, and the details of how you set it
up will depend on the details of your actual data and the range of
choices you want to offer Joe User. Here's one possible scenario: an
Option Group for each of the criteria choices you wish to include
(bear in mind the potential hassle if you want to add further ones in
the future), and 4 option buttons within each Option group, to
represent Include, Exclude, Optional, and Ignore. So, the On Click
event procedure for the button could possibly look something like this
(sketch)...

Private Sub GoButton_Click()
Dim strSQL As String
Dim swWhere as Boolean
swWhere = False
strSQL = "SELECT * FROM YourTable"
If Me.CandyChoice = 1 Then
If swWhere Then
strSQL = strSQL & " And Description Like '*Candy*'"
Else
strSQL = strSQL & " WHERE Description Like '*Candy*'"
End If
swWhere = True
End If
If Me.CandyChoice = 2 Then
If swWhere Then
strSQL = strSQL & " And Description Not Like '*Candy*'"
Else
strSQL = strSQL & " WHERE Description Not Like '*Candy*'"
End If
swWhere = True
End If
If Me.RepackChoice = 1 Then
If swWhere Then
strSQL = strSQL & " And Description Like '*Repack*'"
Else
strSQL = strSQL & " WHERE Description Like '*Repack*'"
End If
swWhere = True
End If
.... etc ...
DoCmd.OpenForm "YourForm"
Forms!YourForm.RecordSource = strSQL
End Sub

- Steve Schapel, Microsoft Access MVP