Running a Querry based report from a combo box

[hobman]

I have a report that I am running based on a querry. The querry has parameter values that the user must enter in order to run the report. The parameteres are: date (which the user enteres) and the second parameter is the Name of anaysts (which I want the user to select from a drop down combo box).

I have the combo box on a form with a list of the names to be selected. Any help would be appreciated.

 

[dhookom]

I'm not totally sure what your question is. If you want to use the combo box as a criteria for the report, I would certainly add a text box to the form for the date field also. Remove these criteria from the query. Then use code to set the where clause of the DoCmd.OpenReport method. For instance add a command button to open the report:

Dim strWhere as String
strWhere = "True "
If Not IsNull(Me.txtDate) Then
   strWhere = strWhere & " And DateField = #" & Me.txtDate & "# "
End If
If Not IsNull(Me.cboAnalyst) Then
    strWhere = strWhere & " And [Analyst]=""" & me.cboAnalyst & """"
End If

This code assume the analyst field is text.

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]

 

[dhookom]

oops, pressed submit too quick. After creating the strWhere value, use code like:

DoCmd.OpenReport "rptYourRptName", acPreview, , 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]

 

[FancyPrairie]

Check out this FAQ FAQ181-5497 It contains a function that will build the Where clause for you. It works for text boxes, ranges, list boxes, combo boxes, option groups, and check boxes.

You only have to do 3 things to make it work.
1. Create a new module and copy and paste the code from the FAQ.
2. Set the tag properties of each of the controls as specified in the FAQ
3. Open the report

If you use this method, you don't have to worry about coding the next db you put together. Just use the same routine (BuildWhere)

 

[hobman]

Thank you for all your help everyone. The code worked great.

 

[shepshedlad]

FancyPrairie, I really would like to know how to use the info you posted (just starting on Access&VB). In step 2 the 'tag' I'm getting lost in terms of do I need to go through the code and alter the code?

All I am look for is an ability to print a report from a selection made via a combo box but am struggly to understand the syntax of how to do it.

Any help whould be sooo apreciated.
Rob.