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

Setting query parameters from VBA for a Report

P: 12
Hello Experts!

Does anyone know if there is a way to set the values of query parameters from VBA for a report's recordsource? (i.e., I want to set the values of the parameters and NOT have the user prompted for them).

Based on a Microsoft page (http://support.microsoft.com/kb/287437), I tried the following approach:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2.  
  3.     Dim sql As String
  4.     Dim dbsSoftwareConfiguration As Database
  5.     Dim qdf As QueryDef
  6.     Dim rst As Recordset
  7.     Dim stDocName As String
  8.  
  9.     Set dbsSoftwareConfiguration = CurrentDb
  10.  
  11.     'if rpt form is open and "include all sites" box is NOT checked
  12.     If myUtil.FrmIsOpen("frmReportBuilder") And (Not Forms!frmReportBuilder.includeAllSitesCheckBox) Then
  13.  
  14.         sql = "PARAMETERS [pSite_ID] Long; SELECT * FROM qryObsoleteMediaBySite;"
  15.         Set qdf = dbsSoftwareConfiguration.CreateQueryDef("tempQry", sql)
  16.         qdf.Parameters![pSite_ID] = Forms!frmReportBuilder.obsoleteSiteMediaComboBox
  17.  
  18.         Set rst = qdf.OpenRecordset
  19.         Me.RecordSource = qdf.Name
  20.  
  21.         'Me.titleTextBox = "Obsolete Media for " & _
  22.             Forms!frmReportBuilder.obsoleteSiteMediaComboBox.Column(1)
  23.  
  24.     Else
  25.  
  26.         Me.RecordSource = "qryObsoleteMedia"
  27.         'Me.titleTextBox = "Obsolete Media Across All Sites"
  28.     End If
  29.  
  30.     Set qdf = Nothing
  31.     Set rst = Nothing
  32.     Set dbsSoftwareConfiguration = Nothing
  33. End Sub
  34.  
The problem is that setting the recordsource instead of the recordset appears to rerun the query, ignoring the parameter values passed in -- resulting in the standard prompt to the user. Setting the recordset directly is apparently not an option for reports (based on the Microsoft page linked to above). (Note: I have used this method and set the recordset property directly for forms, and it does work in that situation).

I've also tried using the following code to set the parameter directly in the query, but the user is still prompted for the parameter:

Expand|Select|Wrap|Line Numbers
  1. dbsSoftwareConfiguration.QueryDefs("qryObsoleteMediaBySite").Parameters("pSite_ID") = _
  2.             Forms!frmReportBuilder.obsoleteSiteMediaComboBox
  3.  
Because I am using the same saved queries in multiple places, I'd rather not bind the criteria in the query directly to the form. If I do that, I have to save a different version of the same query for each form or report I want to use it with.

Any ideas for how to programatically set the value of a parameter for a query that is the recordsource for a report?

Thanks in advance for any ideas!

Dana
Apr 10 '07 #1
Share this Question
Share on Google+
5 Replies


Denburt
Expert 100+
P: 1,356
Looks like your taking an interesting approach, sorry I don't have time right now to test out the code you are using. I have one instance where I have to change the query to set my criteria so in the reports on close event I stipulate a clean up function that returns the query to it's original state and the criteria no longer exists (just an idea).
Apr 11 '07 #2

P: 12
That makes sense.. So instead of trying to set the parameter value, I can just access the .sql property of the query def and replace the "parameter" part of the string with a reference to the appropriate control on form. That might actually work.

I'll play around with it this afternoon and let you know. Thanks!
Apr 11 '07 #3

Denburt
Expert 100+
P: 1,356
Your quite welcome glad we could help. Sounds like your on the right track. Let us know how it goes.
Apr 11 '07 #4

P: 12
Your suggestion worked quite well. I wrote a little "find and replace" type of function, and just before I open the report I use it on the string in the .sql property of the saved query to replace any instances of my parameter ("[pSite_ID]") with a reference to the control on the form ("[Forms]![frmReportBuilder].[obsoleteSiteMediaComboBox]"). Then after I run the open report command, I use the "find and replace" function again to change the references to the control back to the parameter name.

This means that I can get rid of all my querydef stuff in the open event of the form-- I don't need it anymore.

Works like a champ!

I had it in my head that there had to be a way to pass parameter values to the query, so it didn't even occur to me to just change the sql statement :-) Thanks for the idea!

Dana
Apr 11 '07 #5

Denburt
Expert 100+
P: 1,356
Your quite welcome, if you need anything else give us a holler.
Apr 11 '07 #6

Post your reply

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