Build Query Based on selection

[jstarace]

I am working with a contact management database and need to create a form that allows the user to create a query using states (NY, CA) as criteria. Ideally the form would contain each state with a check box. The query should be based on the selected states. I figure I would have to create a tmp table to store the selections, but I am unsure of the code to use to run the query, store it, and export it later.

Does anyone have suggestions on the proper procedure to get this done?

 

[Tranman]

Hi,
Creating a query is a pretty straightforward process. With the DAO, you can just do something like this:

Public Function MakeQuery(strState as String)
Dim dbs As Database
Dim qdf As QueryDef
Dim strSQL As String

Set dbs = CurrentDb
strSQL = "SELECT * FROM STATES WHERE STATENAME = '" &
strState & "';"
' Create the query.
Set qdf = dbs.CreateQueryDef("STATEQUERY", strSQL)
DoCmd.OpenQuery qdf.Name 'If you want to open it
Set dbs = Nothing
End Function

In this case, you'd just add a command button to your form that figured out what state was checked, and passed the state as an argument to the function. (This only works with a single state, at present.) If you want to look at all of the checkboxes on your form, you could use a for..each loop, and look at the control type or name of each object in the controls collection.

What I'd probably do is have the click event of the command button figure out all of the states that are checked, and add their names to a global collection that was accessable from the MakeQuery function, or alternatively, you could just add all of the state names to an array and pass the array to the function as an argument.

Anyway, that's what I'd do....

Tranman