Open a report after selecting a customer from a combo box

[Heeeeelp]

Hi Everyone,

I've created a report that currently is listing sales information, all orders and total sales for all customers. I have created a seperate form with a combo box that allows the user to select a company name from the list. Once a name is selected, I would like the report to only display the information for that one customer.

I have made the following decleration:
Public pubID as Integer

The following event is in the after update event of the form with the combo box.
Private Sub companyName_AfterUpdate()


On Error GoTo Err_openReport_Click
pubID = Me.CompanyName.Column(0)
Dim stDocName As String

stDocName = "Customer Report"
DoCmd.openReport stDocName, acPreview


Exit_openReport_Click:
Exit Sub

Err_openReport_Click:
MsgBox Err.Description
Resume Exit_openReport_Click
End Sub

I would appreciate some help with the code that needs to go into the Report so that it displays only the company that the user selected.

Thank you in advance for your help.

Regards,
Tess

 

[TheAceMan1]

How are ya Heeeeelp . . .

Quickest and easiest I can think of is to return [blue]pubID[/blue] thru a function, to the [blue]Where Clause[/blue] of the reports query or SQL.

In a module in the modules window, copy/paste the following:

[blue]Public Function gblPubID()
   gblPubID = pubID
End Function[/blue]

The Where Clause of the report would resemble:

[blue]   WHERE [PubID]=gblPubID()[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Heeeeelp]

Thank you AceMan, I've set the code up but there's an error in passing the customer ID to the pubID and I'm not sure how to fix it. When I run the form with the combo box and select a company name, I receive a message asking me to enter parameter value for pubID.

I would greatly appreciate some help with this. I've used the above code in another application and it works perfectly, I don't understand what's causing the problem here.

Regrds,

Tess