Selecting and displaying data from a list box on a Form

[jabrjo]

Hello,
I am trying to create a selection tool on an Access form that will allow users to select and retrieve data based on selections specified in a series of list/combo boxes. I have attached an example of what I would like the form to look like, however, i cant figure out how actually get a working version.

Thanks!

[ssanfu]

Use the selected controls to create a filter or WHERE string that limits the records when you click the button.

Allen has an example pf using VBA to create the filter:
http://www.allenbrowne.com/ser-62code.html

[jabrjo]

I'm definitely no VBA pro. Is there a way to do this in design view?

[ssanfu]

Depends on what happens when you click the button.
If you are opening a form/report the form recordsource (query) can allow for Null entries in the controls;
see http://access.mvps.org/access/queries/qry0001.htm

What code is in the button click event?

[jabrjo]

Sorry for the delay. The button runs a query based on a selection in a combo box. Code looks like this:

Private Sub Command107_Click()
On Error GoTo Err_Command107_Click

Dim stDocName As String

stDocName = "qryEvents"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command107_Click:
Exit Sub

Err_Command107_Click:
MsgBox Err.Description
Resume Exit_Command107_Click

End Sub

I figured out how to query data based on the combo box selection using the following:[Forms]![frmEvents]![cmbBLH]. However, I am having trouble dealing with the Null entries. For example, if i want to look up transactions for all stores, without populating the customer id field, the query just comes back blank. If I combine [Forms]![frmEvents]![cmbBLH] and [Forms]![frmEvents]![cmbBLH] is null, i get the entire database without a query. What am I doing wrong? I would like to be able to query data based on multiple selections or just a single selection. Does that make sense?

And again, I am not very familiar with VB, so if this can be done in design view, that would be great.

Thanks for your help!

[jabrjo]

I did some searching and i think i found it: Like [Forms]![frmEvents].[cmbBLH] & "*".

Now, on to my next issue - any idea how to query a date range on a form? I'm assuming will use two text boxes and add another line similar to Like [Forms]![frmEvents].[cmbBLH] & "*". I want this to be an options as well so i dont want to use a parameter prompt (unless there is an easy way to add a check box/selection box that prompts the parameter query).

thanks!

[jabrjo]

Steve, do you see any potential problems with my solution? If not, I'll mark this as solved.

[June7]

The LIKE operator and wildcard will work only with text fields.

Review: http://www.allenbrowne.com/ser-62.html

[ssanfu]

If I combine [Forms]![frmEvents]![cmbBLH] and [Forms]![frmEvents]![cmbBLH] is null

The "AND" should be an "OR". See http://access.mvps.org/access/queries/qry0001.htm

Steve, do you see any potential problems with my solution? If not, I'll mark this as solved.

Looks like it should work. I would advise renaming controls to more descriptive names. Trying to trace through code when you see names like "Command107" or "Combo36" is frustrating.

[jabrjo]

Thanks for your help!