Selecting criteria for a query from a combo box

[domino3]

I've asked this before, but still don't understand what to do.

I have a report which is used to select colours of badges to be printed. At the moment the user runs the report and is asked to type in what colour they want. I would like to give the user a drop down list to choose from, so that they don't put in an incorrect choice.

I have set up a form (F_badge_colours) with a combo box in it with the colour choices in (combo_colours). I have then tried putting all this in the query criteria which runs the report ( [Forms]![F_badge_colours]![Combo_colours] ). But all I get is a dialog box with the above formula in it.

Can someone please explain what I am doing wrong and how to make it work? Thank you.

 

[dhookom]

The form must be open when you attempt to open the query or report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[domino3]

Thanks. That now works.

However, does that mean that it isn't possible to use the same form (F_badge_colours) as the criteria chooser for several queries and reports? I have several reports that run from different queries and tables but that have the same badge criteria.

 

[dhookom]

You can use the same form for multiple reports/queries.

I prefer to use code to build a "Where" clause for the DoCmd.OpenReport method. Sample code would look like:

Dim strWhere as String
strWhere = "1=1 "
If not IsNull(Me.Combo_colours) Then
    strWhere = strWhere & " AND [Colour]=""" & Me.Combo_colours & """"
End If
DoCmd.OpenReport "rptYourReport", acviewpreview, , strWhere

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[domino3]

I'm sorry. I don't understand what the where clause would be attached to.

 

[dhookom]

How are you opening your report? Normally this would be with code from the On Click event of a command button on your report. If you don't have this, go to your form design and use the command button wizard to open your report. Then modify your code generated by the wizard to look something like the previous message.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[domino3]

Hi. Thanks for replying. It now works for running reports.

However, I've tried doing the same to run queries and I keeo getting a compile error.

The code I have put in is
----------------------------------------
Private Sub Command5_Click()
On Error GoTo Err_Command5_Click

Dim strWhere As String
strWhere = "1=1 "
If Not IsNull(Me.Combo_mailtype) Then
strWhere = strWhere & " AND [mail_type]=""" & Me.Combo_mailtype & """"
End If
DoCmd.OpenQuery "q_labels", acViewPreview, , strWhere

Exit_Command5_Click:
Exit Sub

Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click

End Sub

---------------------------------

What I get is a message that says I have the wrong number of arguments or an invalid property assignment. I don't know what that means. I've tried redoing the code and picking from the options the system offers, but it doesn't seem to help.

 

[PHV]

Place your cursor inside the word OpenQuery in your code and press the F1 key.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[domino3]

I'm sorry. I've tried that, and I still can't see what the problem is.

 

[earthandfire]

I think PHV is pointing you to the parameter list for OpenQuery.

As an alternative approach...
Type DoCmd.OpenQuery and check out the parameters - you list doesn't match those that are available.

Hope this helps.

 

[domino3]

Thanks for your help.

I have the query that I am using in the above combo box as the query which is used in a word mailmerge. Is it possible to get word to automatically open the form to trigger the combo box? When I tried running a mailmerge today, it went straight from the query without opening the form first.

 

[dhookom]

A query will never open a form.

Did you know that you can set a filter from within Word rather than in Access?

When I do any merging with Word, I always export the merge records to a Word Merge File. My main Word document then references this separate file rather than caring about Access queries or tables.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[domino3]

What I am trying to do is make it as easy as possible for the users of the system. I'm not sure that they would cope with exporting merge records to word.

But, if it isn't possible to have the drop down combo box appear in the word document, is there any other way of supplying the list of choices before merging? I currently use the same queries in the database as for the mail merge, and ideally I'd rather not have to have two copies of each query.

 

[dhookom]

You can use code to do everything so there is no perceived difference for your users.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]