Combobox to filter report

[Sharkun]

I've looked on this forum and other forums for similiar topics to try and piece it together myself. Not much luck.

I have a report based on a query. I have a form with a combobox(Combo5) that lists all the customers that have been entered tickets into our system.

All I want is to select a customer from the combobox, click the cmd button(CustomerSearchCommand) and have the report (CustomerSearchRa) filter the report to the value in the combobox(combo5)



In trying different things I've gotten confused on what my original code was for this command button.

Here's my command box code
Private Sub CustomerSearchCommand_Click()
On Error GoTo Err_CustomerSearchCommand_Click
Dim stDocName As String
stDocName = "Customer Search Ra"
DoCmd.OpenReport stDocName, acPreview, , "NOT SURE WHAT TO PUT HERE"
Exit_CustomerSearchCommand_Click:
Exit Sub
Err_CustomerSearchCommand_Click:
MsgBox Err.Description
Resume Exit_CustomerSearchCommand_Click

End Sub

The report does generate with all the entries in the query. I've tried putting values related to Combo5 in the "NOT SURE WHAT TO PUT HERE" area but still can't get it to work.

Thanks in advance.

[pbaldy]

See if this helps:

BaldyWeb wherecondition

The wherecondition syntax is the same for OpenReport.

[Sharkun]

Still showing all records on the report.

Here's my line of code now after reading the baldy's page. The value in both the combo box and the report are text.

Dim stDocName As String
stDocName = "Customer Search Ra"
DoCmd.OpenReport stDocName, acPreview, "Customer = '" & Me.Combo5 & "'"

Looking at the example below.

DoCmd.OpenForm "SecondFormName", , , "FieldName = '" & Me.ControlName & "'"

OpenForm would be replaced by OpenReport

SecondFormName is replaced by stDocName (which is defined as "Customer Search Ra")

Confused on Fieldname. Would that be the field on the report I'm searching by? If so that would be replaced by "Customer" like I have in my line of code.

Me.ControlName would be the name of the value I want to search by that I pick in my form. "Combo5" is the name of the combobox that lists all my customers.

[pbaldy]

I think you have it in the wrong position. Try (note the extra comma):

DoCmd.OpenReport stDocName, acPreview, , "Customer = '" & Me.Combo5 & "'"

[Sharkun]

 DoCmd.OpenReport stDocName, acPreview, , "Customer = '" & Me.Combo5 & "'"

Added the extra comma I was missing. Now it will show the Titles for each data field in the report, but no data. If I open the report it asks me for a value, I type in a customer and I get the report with values for that customer. Same thing happens for the query my report is based on.

Any Ideas?

Thanks

[pbaldy]

What is the SQL of the query? It sounds like you have a criteria in there.

[Sharkun]

Forms![Customer Search Form]![Combo5]

[pbaldy]

Is that the criteria in the query? If so, you'd either want that or the wherecondition argument of OpenReport, not both. Is the form open when the query/report runs? If so, you shouldn't get prompted.

[Sharkun]

That's the criteria in the query, and yes the form is open when the report opens.

What I would think that is with that criteria in the query I woudn't need the where command in the open report line? So my Openreport line should look like this

DoCmd.OpenReport stDocName, acPreview

If I leave it at that then I just get one page of report headers with no data.

****

Just noticed this.

If I have the form open with a customer selected. and I manually open the report or query I get no data. If I close the form and open either query or report it prompts me I put in a value and I get results. So I don't know but something doesn't like the value I'm passing from my combo box.

[pbaldy]

Like I said, you'd want one or the other, not both. Can you post the db?

[Sharkun]

Here's the link.

http://www.filedropper.com/db2_1

Customer Search is the Form I'm having problems with.

[pbaldy]

I don't go to unknown sites for downloads. You should be able to attach it here, after compacting and zipping.

[Sharkun]

Attachment 2650

Sorry I didn't see the attach file.

[pbaldy]

No problem; I'm just paranoid. This appears to work:

DoCmd.OpenReport stDocName, acPreview, , "Customer = '" & Me.Combo5.Column(1) & "'"

There is no criteria in the query.

[Sharkun]

Thanks a bunch.