Setting report query parameters from VBA

[morsagmon]

I have a report that is based on a query with many parameters (e.g. [piEnterDate]).

On the Report_Open event, after I have collected values for all of the query parameters and stored them in public variables, all I need to do now is to assign these values to the query's parameters.

What is the exact syntax to do that? how do I address this query's parameters from VBA?

I presume I don't need to go through the QueryDef object, as the query is automatically loaded by the report itself. Correct? I just need to address the parameters and assign values to them.

Thanks!
Mor

 

[boblarson]

1. Get rid of all of the parameters off the query.

2. Then you can use the Where Clause of the DoCmd.OpenReport code to specify the parameters based on your variables.

 

[DCrake]

This is becoming a popular question of late and as usual I recommend you take a look at this link. Read the documentation prior to attempting to replicate it as it will give you a more clearer understanding of how this works.

 

[morsagmon]

Thank you guys!

Bob, I adopted your strategy - much better

Thanks!

 

[dfenton]

This is becoming a popular question of late and as usual I recommend you take a look at this link. Read the documentation prior to attempting to replicate it as it will give you a more clearer understanding of how this works.

I would dissent from that article's approach -- relying on public variables is simply a terrible, terrible way to do this. Anything that relies on global variables of any form is, in my opinion, likely an erroneous approach to the problem.

 

[DCrake]

This may be an opinion that you have based on your own experiences, however, having used this approach for many years without any significant issues I take umbridge at your comments.

To openly disrespect another persons approach to a solution without qualifying your statement is in my opinion totally unprofessional. If you can substanciate your reasoning for not employing such a proven solution I would be glad to hear it.

I also suggest you read the forum regarding disrespecting other members of this forum.

 

[MarkK]

dfenton:
What is the value of branding something 'erroneous' and 'terrible, terrible' and offering no argument or alternative? At the root of much 'wrongness' is ignorance, so my request to you in the future is that by way of contribution you bring enlightenment and education. That'll raise the bar for us all.
Thanks for your consideration,

 

[dfenton]

1. Global Variables are generally bad programming precisely because they are global -- i.e., not appropriately limited in scope. The downside of that is that they can be changed globally and so you don't really know what's in them when you call them (they could have been changed somewhere else in your code that you're not expecting). Using properly narrow scope insures that you know that the value stored in the variable you're using is the one you expect.

2. Globals are unreliable in that they are so easily reset.

I could say more, and outline the alternatives, but I've done this numerous times in several different forums.

I think you will find that most experienced Access developers would tend to agree with me that global variables should used sparingly, and only when they are unavoidable because global scope is the appropriate one.

And, by the way, ya all come across to me as timid little weasels if you get so bent out of shape from something as innocuous as my post on this subject yesterday. You seem to be taking the criticism of using global variables personally, and I don't know why that would be, unless you're so remarkably insecure that you can't distinguish criticism of someone's words from criticism of the person.

 

[DCrake]

I am still not satifeid with your explanation. Take the most common senario where a person is attempting to use a query as an underlying recordsource for a report. This report may be called from a variety of sources in the application. So if the query where condition is referring to a form such as

=Forms!WhatEverForm.WhateverControl

Then the report can only be run if the nominated for is loaded. By using a public/global variable in the way I suggested leaves the query open to be called from any part in the application.

To the unanniciated I define a public variable in a standard module

Public DtmFilterDate As Date

Then I create a public Function

Public Function GetFilterDate() As Date
   'Substitute Todays' date if public variable has not been populated
   GetFilterDate = Iff(IsEmpty(DtmFilteDate),Date,DtmFilterDate)
End Function

Then in any form that may be used to pass a date value to the variable

In the AfterUpdate Event of the control

DtmFilterDate = Me.ActiveControl

Then in the query Design beneath ANY date field (in this example)

=GetFilterDate()

So it does not matter where this query is used be it in a report or a subform or a recordset, whatever it is not reliant on a phyiscal control being accessable from a loaded form.

Even if you had a control on a hidden form that held the date value in the field you would still need to refer to that form to obtain the desired date.

In conclusion as good programmer would code their application so that there would be no collision between code.

 

[crillux]

I could say more, and outline the alternatives, but I've done this numerous times in several different forums.

The discussion is interesting for a newbie like me. Where can the alternatives be found?

 

[MarkK]

I like some parts of what you have going on DCrake, but I offer that the way you set and retrieve your global variable strongly suggests--to me--that you'd rather use a property. Consider the following code ...

private m_date as variant

property get GlobalDate as date
  if isempty(m_date) then m_date = date()
  GlobalDate = m_date
end property

property let GlobalDate(ByVal dValue as date)
  if dValue < #1/1/1990# then 
    m_date = empty
  else
    m_date = dValue
  end if
end property

This gives you precise control over what happens when you read AND write the property.

But I agree with Mr Fenton that minimizing the scope of variables is generally prefered and in practice I never use global variables. Rather, I want every object to be able to fend for itself. If, for instance, object A needs a value from some critical process B, then I get A to run B directly.

A -> B

This is very simple and scalable, since I can easily add objects E, F, and G that consume B, and nothing can break.

E -> B

And if business logic at B changes, all consumers continue to function since none of this code cares about any other code.

By contrast, the pattern where code A reads global variable D is complex--assuming critical process B produces D. Here you must have some other code C to execute B, store the value at D, and then run A.

C -> ( B -> D <- A )

This process, to me, looks much more fragile, and sort of looks like the tail wagging the dog. Remember that A is the consumer and B is the producer.
Cheers,