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 - rptClientsByCountry - which is based on a query - qryClientsByCountry - 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.OpenReport "rptClientsByCountry", acViewPreview

end sub

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

Thanks in advance!

[maxamis4]

It works just like a form

 
    stDocName = "frm Name"
 
    stLinkCriteria = "[Report Criteria]=" & Me![form criteria]
    DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
 

[jamjar]

It works just like a form

 
    stDocName = "frm Name"
 
    stLinkCriteria = "[Report Criteria]=" & Me![form criteria]
    DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
 

This method implies you get rid of the parameters per se.
You can pass parameters to the query in code, but I haven't figured out how to use these to run a Select query. For an action query something like this works:

  Dim qdf As QueryDef
    Set qdf = CurrentDb.QueryDefs("yourQuery")
    qdf.Parameters("myCountry").Value = Me.cmbCountry
    qdf.Execute
    qdf.Close
    Set qdf = Nothing
 

but if you try to Execute a select query it doesn't work. Ditto if you replace the execute with DoCmd.OpenReport you still receive a prompt for the parameter.
So like karanj I'm curious if anyone knows if/how you can pass a parameter to a select query so the user does not receive the prompt?

James

[karanj]

This method implies you get rid of the parameters per se.
...
if you replace the execute with DoCmd.OpenReport you still receive a prompt for the parameter.
So like karanj I'm curious if anyone knows if/how you can pass a parameter to a select query so the user does not receive the prompt?

Yep, exactly - this kind of basic modularisation would be very helpful! The "official" solution that I've found however is a little disheartening - see this article on the office.microsoft.com site. Surely that means that the query-report-form will effectively be coupled quite tightly?

[karanj]

It works just like a form

 
    stDocName = "frm Name"
 
    stLinkCriteria = "[Report Criteria]=" & Me![form criteria]
    DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
 

here effectively the form has to "know" the table/sql - I'd prefer to avoid this if possible so that the queries can be built by one person and the form/report by another. This seems to me something rather straightforward...

[jamjar]

Yep, exactly - this kind of basic modularisation would be very helpful! The "official" solution that I've found however is a little disheartening - see this article on the office.microsoft.com site. Surely that means that the query-report-form will effectively be coupled quite tightly?

I would think so. The query would only be useful for the one form. I guess that means whatever form you may be starting with, you need to call the query's parameter form up before running the report based on it, and populate the parameter form with any selections that may already have been made in the originating form?

James

[maxamis4]

Let it stand that stlinkcriteria can be defined to what ever you want

here is an example

       stLinkCriteria = "[fID]=" & Me![fID]
 

as well you could use a recordset

[jamjar]

Let it stand that stlinkcriteria can be defined to what ever you want

here is an example

       stLinkCriteria = "[fID]=" & Me![fID]
 

Agreed.
There have been times when I have wished to be able to use parameters instead, but I can't remember a good example.

[cfs]

One sulution I think:

Create an invisible text-field in the active form (assuming you use a form to invoke the VBA-code, or have an active form that stays open). Use the VBA-code to write the desired parameter to the text-field (be sure to to this the right place regarding loops etc).

In the query, assign the parameter in the query to the invisible text-field. As you selection changes, so does the text-field, and the query based upon it shoul return the right results. at least this worked for me :)

[truthlover]

It works just like a form

 
stDocName = "frm Name"
 
stLinkCriteria = "[Report Criteria]=" & Me![form criteria]
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
 

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!

[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.SurveyWorkOrderID]=" _
& Me![tbl_SurveyWorkOrder.SurveyWorkOrderID]

[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.SurveyWorkOrderID]=" _
& Me![tbl_SurveyWorkOrder.SurveyWorkOrderID]