Setting a parameter for a report based on a query

[karanj]

Hi all,

I'm facing an issue with Access that I haven't been able to find a solution for, despite hours of googling, so I hope someone here can help!

I've got a report - rptClientsByCou ntry - which is based on a query - qryClientsByCou ntry - that takes a parameter, myCountry, to filter the results. I need to be able to generate this report depending on the user's choice of country, preferably from a drop-down list.

Leaving aside for a minute the form part, I can't figure out how to pass a string to the report such that it accepts it as the parameter to the underlying query. Psuedo code would be:

sub command_click()

get current value of country drop down

set parameter myCountry on report

DoCmd.OpenRepor t "rptClientsByCo untry", acViewPreview

end sub

Can this be done? Or must I resort to setting the Where condition on the DoCmd.OpenRepor t?

Thanks in advance!

[truthlover]

Not sure if anyone saw this since it was the holiday weekend:

What am I doing wrong? This is my modification of the code, but I'm getting a "Compile Error: Variable not defined" for both the stDocName and stLinkCriteria.

Private Sub cmdPrintSelected_Click()
    stDocName = "rpt_SurveyWorkOrder"
 
        stLinkCriteria = "[tbl_SurveyWorkOrder.SurveyWorkOrderID]=" _
        & Me![tbl_SurveyWorkOrder.SurveyWorkOrderID]
        DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
End Sub

Sorry if this is a dumb question, I'm only just learning VBA

Thanks!

[maxamis4]

Not sure if you ever solved this but it looks like you are seetin your survey ID equal to the table. Which really has no reference. What you would need to do is set your report workid field what ever the name equal to the form survey id number.

Example

stLinkCriteria = "[Report.SurveyWo rkOrderID]=" _
& Me![tbl_SurveyWorkO rder.SurveyWork OrderID]

[truthlover]

Yes, I did get help on that. Thanks for replying!

Not sure if you ever solved this but it looks like you are seetin your survey ID equal to the table. Which really has no reference. What you would need to do is set your report workid field what ever the name equal to the form survey id number.

Example

stLinkCriteria = "[Report.SurveyWo rkOrderID]=" _
& Me![tbl_SurveyWorkO rder.SurveyWork OrderID]