By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,501 Members | 2,681 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,501 IT Pros & Developers. It's quick & easy.

Setting a parameter for a report based on a query

P: 3
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!
Jun 6 '07 #1
Share this Question
Share on Google+
12 Replies


maxamis4
Expert 100+
P: 295
It works just like a form

Expand|Select|Wrap|Line Numbers
  1.  
  2.     stDocName = "frm Name"
  3.  
  4.     stLinkCriteria = "[Report Criteria]=" & Me![form criteria]
  5.     DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
  6.  
Jun 6 '07 #2

jamjar
P: 50
It works just like a form

Expand|Select|Wrap|Line Numbers
  1.  
  2.     stDocName = "frm Name"
  3.  
  4.     stLinkCriteria = "[Report Criteria]=" & Me![form criteria]
  5.     DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
  6.  
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:
Expand|Select|Wrap|Line Numbers
  1.   Dim qdf As QueryDef
  2.     Set qdf = CurrentDb.QueryDefs("yourQuery")
  3.     qdf.Parameters("myCountry").Value = Me.cmbCountry
  4.     qdf.Execute
  5.     qdf.Close
  6.     Set qdf = Nothing
  7.  
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
Jun 6 '07 #3

P: 3
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?
Jun 7 '07 #4

P: 3
It works just like a form

Expand|Select|Wrap|Line Numbers
  1.  
  2.     stDocName = "frm Name"
  3.  
  4.     stLinkCriteria = "[Report Criteria]=" & Me![form criteria]
  5.     DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
  6.  
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...
Jun 7 '07 #5

jamjar
P: 50
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
Jun 7 '07 #6

maxamis4
Expert 100+
P: 295
Let it stand that stlinkcriteria can be defined to what ever you want

here is an example

Expand|Select|Wrap|Line Numbers
  1.        stLinkCriteria = "[fID]=" & Me![fID]
  2.  
as well you could use a recordset
Jun 7 '07 #7

jamjar
P: 50
Let it stand that stlinkcriteria can be defined to what ever you want

here is an example

Expand|Select|Wrap|Line Numbers
  1.        stLinkCriteria = "[fID]=" & Me![fID]
  2.  
Agreed.
There have been times when I have wished to be able to use parameters instead, but I can't remember a good example.
Jun 8 '07 #8

P: 1
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 :)
Mar 17 '08 #9

truthlover
100+
P: 107
It works just like a form

Expand|Select|Wrap|Line Numbers
  1.  
  2. stDocName = "frm Name"
  3.  
  4. stLinkCriteria = "[Report Criteria]=" & Me![form criteria]
  5. DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
  6.  
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdPrintSelected_Click()
  2.     stDocName = "rpt_SurveyWorkOrder"
  3.  
  4.         stLinkCriteria = "[tbl_SurveyWorkOrder.SurveyWorkOrderID]=" _
  5.         & Me![tbl_SurveyWorkOrder.SurveyWorkOrderID]
  6.         DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
  7. End Sub
Sorry if this is a dumb question, I'm only just learning VBA

Thanks!
May 22 '08 #10

truthlover
100+
P: 107
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdPrintSelected_Click()
  2.     stDocName = "rpt_SurveyWorkOrder"
  3.  
  4.         stLinkCriteria = "[tbl_SurveyWorkOrder.SurveyWorkOrderID]=" _
  5.         & Me![tbl_SurveyWorkOrder.SurveyWorkOrderID]
  6.         DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
  7. End Sub
Sorry if this is a dumb question, I'm only just learning VBA

Thanks!
May 27 '08 #11

maxamis4
Expert 100+
P: 295
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]
Jun 22 '08 #12

truthlover
100+
P: 107
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]
Jun 30 '08 #13

Post your reply

Sign in to post your reply or Sign up for a free account.