Get Query Parameters from Report's RecordSource?

[David Horowitz]

Hi. I have a query named Query1. Let's say it looks like this:

Select * from Table1 where Field1=[Enter Value]

So whenever you run the query, it prompts for "Enter Value".

So now, I have Report1, whose RecordSource is Query1.

So when I run Report1, Access prompts "Enter Value". Everything's good so
far.

Well, the thing is, I need to do some code inside the report for some
purpose which I won't go into, and I need to know what value the user
entered for [Enter Value]. Is there some way for me to get that value?

I know it would seem like I could just query maybe any Detail record and see
what value Field1 has, and that would be it, but my case is actually a
little more complicated, because my query is actually more like:

Select * from Table1 where TheDate between [Start Date] and [End Date]

and I want to know what the Start Date and the End Date are inside the VBA
code because I need to do something with it.

TIA!

David

 

[Danny J. Lesandrini]

Rewrite the query like this ...
Select *, [Enter Value] As UserEnterValue
from Table1 where Field1=[Enter Value]

or more accurately ...

Select *,[Start Date] As StartDate, [End Date] As EndDate
from Table1 where TheDate between [Start Date] and [End Date]

 

[Allen Browne]

You should be able to put text boxes on your report bound to the parameters,
e.g.
=[Start Date]

You could then refer to the value of the text box in your code.

 

[David Horowitz]

Wow, thanks Danny! That looks like it may help, I think I'm missing one more
thing:

How would I then get the values while inside, say, GroupHeader0_Format?

Thanks!

Dave

Rewrite the query like this ...
Select *, [Enter Value] As UserEnterValue
from Table1 where Field1=[Enter Value]

or more accurately ...

Select *,[Start Date] As StartDate, [End Date] As EndDate
from Table1 where TheDate between [Start Date] and [End Date]
--

Danny J. Lesandrini
(e-mail address removed)
http://amazecreations.com/datafast/

Hi. I have a query named Query1. Let's say it looks like this:

Select * from Table1 where Field1=[Enter Value]

So whenever you run the query, it prompts for "Enter Value".

So now, I have Report1, whose RecordSource is Query1.

So when I run Report1, Access prompts "Enter Value". Everything's good so
far.

Well, the thing is, I need to do some code inside the report for some
purpose which I won't go into, and I need to know what value the user
entered for [Enter Value]. Is there some way for me to get that value?

I know it would seem like I could just query maybe any Detail record and
see what value Field1 has, and that would be it, but my case is actually
a little more complicated, because my query is actually more like:

Select * from Table1 where TheDate between [Start Date] and [End Date]

and I want to know what the Start Date and the End Date are inside the
VBA code because I need to do something with it.

TIA!

David

 

[Duane Hookom]

To reference the values from within code, you must bind a control to the
field/column.

--
Duane Hookom
MS Access MVP
--

Wow, thanks Danny! That looks like it may help, I think I'm missing one
more thing:

How would I then get the values while inside, say, GroupHeader0_Format?

Thanks!

Dave

Rewrite the query like this ...
Select *, [Enter Value] As UserEnterValue
from Table1 where Field1=[Enter Value]

or more accurately ...

Select *,[Start Date] As StartDate, [End Date] As EndDate
from Table1 where TheDate between [Start Date] and [End Date]
--

Danny J. Lesandrini
(e-mail address removed)
http://amazecreations.com/datafast/

Hi. I have a query named Query1. Let's say it looks like this:

Select * from Table1 where Field1=[Enter Value]

So whenever you run the query, it prompts for "Enter Value".

So now, I have Report1, whose RecordSource is Query1.

So when I run Report1, Access prompts "Enter Value". Everything's good
so far.

Well, the thing is, I need to do some code inside the report for some
purpose which I won't go into, and I need to know what value the user
entered for [Enter Value]. Is there some way for me to get that value?

I know it would seem like I could just query maybe any Detail record and
see what value Field1 has, and that would be it, but my case is actually
a little more complicated, because my query is actually more like:

Select * from Table1 where TheDate between [Start Date] and [End Date]

and I want to know what the Start Date and the End Date are inside the
VBA code because I need to do something with it.

TIA!

David

 

[Danny J. Lesandrini]

Yeah, I realize I forgot to mention that. You can treat these query
columns as a field. Set the control source of a text box to StartDate.

I see that someone posted an answer where you could set the expression,
[Start Date] as the control source. I wasn't aware that it would work
that way, but if so, that's even easier. Give them both a try.

--

Danny J. Lesandrini
(e-mail address removed)
http://amazecreations.com/datafast/

Wow, thanks Danny! That looks like it may help, I think I'm missing one more thing:

How would I then get the values while inside, say, GroupHeader0_Format?

Thanks!

Dave

Rewrite the query like this ...
Select *, [Enter Value] As UserEnterValue
from Table1 where Field1=[Enter Value]

or more accurately ...

Select *,[Start Date] As StartDate, [End Date] As EndDate
from Table1 where TheDate between [Start Date] and [End Date]
--

Danny J. Lesandrini
(e-mail address removed)
http://amazecreations.com/datafast/

Hi. I have a query named Query1. Let's say it looks like this:

Select * from Table1 where Field1=[Enter Value]

So whenever you run the query, it prompts for "Enter Value".

So now, I have Report1, whose RecordSource is Query1.

So when I run Report1, Access prompts "Enter Value". Everything's good so far.

Well, the thing is, I need to do some code inside the report for some purpose which I won't go into, and I need to
know what value the user entered for [Enter Value]. Is there some way for me to get that value?

I know it would seem like I could just query maybe any Detail record and see what value Field1 has, and that would
be it, but my case is actually a little more complicated, because my query is actually more like:

Select * from Table1 where TheDate between [Start Date] and [End Date]

and I want to know what the Start Date and the End Date are inside the VBA code because I need to do something with
it.

TIA!

David

 

[David Horowitz]

Hi Danny,

I figured out the answer to my last question -- put hidden textbox(es) on
the report and just do Me!StartDate and Me!EndDate -- thanks!

But, I realize I have this other problem. I need to use the same parameters
to requery the query again. I'm dealing with legacy code that I don't want
to completely re-write, otherwise I'm sure there are better ways to do this.

So now what I have is regular ADO code that runs a SQL query such as:

"Select * from Query1 where blah blah order by blah blah"

but I need to specify Query1's parameters (StartDate and EndDate).

Any help?

Thanks so much!

David

David

Rewrite the query like this ...
Select *, [Enter Value] As UserEnterValue
from Table1 where Field1=[Enter Value]

or more accurately ...

Select *,[Start Date] As StartDate, [End Date] As EndDate
from Table1 where TheDate between [Start Date] and [End Date]
--

Danny J. Lesandrini
(e-mail address removed)
http://amazecreations.com/datafast/

Hi. I have a query named Query1. Let's say it looks like this:

Select * from Table1 where Field1=[Enter Value]

So whenever you run the query, it prompts for "Enter Value".

So now, I have Report1, whose RecordSource is Query1.

So when I run Report1, Access prompts "Enter Value". Everything's good so
far.

Well, the thing is, I need to do some code inside the report for some
purpose which I won't go into, and I need to know what value the user
entered for [Enter Value]. Is there some way for me to get that value?

I know it would seem like I could just query maybe any Detail record and
see what value Field1 has, and that would be it, but my case is actually
a little more complicated, because my query is actually more like:

Select * from Table1 where TheDate between [Start Date] and [End Date]

and I want to know what the Start Date and the End Date are inside the
VBA code because I need to do something with it.

TIA!

David

 

[Darryl Kerkeslager]

So now what I have is regular ADO code that runs a SQL query such as:

"Select * from Query1 where blah blah order by blah blah"

but I need to specify Query1's parameters (StartDate and EndDate).

Dim cmd1 As ADODB.Command
Dim prm1 As ADODB.Parameter
Dim prm2 As ADODB.Parameter
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Set cmd1 = New ADODB.Command
With cmd1
.ActiveConnection = CurrentProject.Connection
.CommandText = "MySavedPQuery"
.CommandType = adCmdStoredProc
Set prm1 = .CreateParameter("Start Date", adDate, adParamInput, ,
Me!txtStartDate.Value)
Set prm2 = .CreateParameter("End Date", adDate, adParamInput, ,
Me!txtEndDate.Value)
.Parameters.Append prm1
.Parameters.Append prm2
End With

rs.Open cmd1, , adOpenForwardOnly, adLockReadOnly, adCmdStoredProc

 

[Danny J. Lesandrini]

Are you saying that, after the user supplies the parameters and the query
has run, you'd like to reuse those same parameters again in another process?

If that's the case, then maybe you could use some functions to save the values
into global variables.

Select Table1 .*,
SaveStartDate([Start Date]) As StartDate,
SaveEndDate([End Date]) As EndDate
from Table1 where TheDate between [Start Date] and [End Date]

Create two function-pairs to save and get the values. For example, one pair
would look like this ...

Private m_dteStartDate As Date

Public Function SaveStartDate(ByVal varValue As Variant) As Date
' If varValue isn't a date, use a default start date of your choice.
If Not IsDate(varValue) Then
' If missing, set StartDate to Today -(7 Days)
m_dteStartDate = DateAdd("d",-7,Date())
Else
m_dteStartDate = varValue
End If
SaveStartDate = m_dteStartDate
End Function

Private Function GetStartDate() As Date
' If the value hasn't been set yet, it will be zero (0). Use default value
If m_dteStartDate = 0 Then m_dteStartDate = DateAdd("d",-7,Date())
GetStartDate = m_dteStartDate
End Function

Now, after the query has run, the value entered by the user will be saved
in the Private variable, m_dteStartDate, and will be accessible through the
function GetStartDate() anywhere you wish to use it.

Cool, huh?

 

[David Horowitz]

Thanks everyone for your help. Really.

David

Yeah, I realize I forgot to mention that. You can treat these query
columns as a field. Set the control source of a text box to StartDate.

I see that someone posted an answer where you could set the expression,
[Start Date] as the control source. I wasn't aware that it would work
that way, but if so, that's even easier. Give them both a try.

--

Danny J. Lesandrini
(e-mail address removed)
http://amazecreations.com/datafast/

Wow, thanks Danny! That looks like it may help, I think I'm missing one
more thing:

How would I then get the values while inside, say, GroupHeader0_Format?

Thanks!

Dave

Rewrite the query like this ...
Select *, [Enter Value] As UserEnterValue
from Table1 where Field1=[Enter Value]

or more accurately ...

Select *,[Start Date] As StartDate, [End Date] As EndDate
from Table1 where TheDate between [Start Date] and [End Date]
--

Danny J. Lesandrini
(e-mail address removed)
http://amazecreations.com/datafast/



Hi. I have a query named Query1. Let's say it looks like this:

Select * from Table1 where Field1=[Enter Value]

So whenever you run the query, it prompts for "Enter Value".

So now, I have Report1, whose RecordSource is Query1.

So when I run Report1, Access prompts "Enter Value". Everything's good
so far.

Well, the thing is, I need to do some code inside the report for some
purpose which I won't go into, and I need to know what value the user
entered for [Enter Value]. Is there some way for me to get that value?

I know it would seem like I could just query maybe any Detail record
and see what value Field1 has, and that would be it, but my case is
actually a little more complicated, because my query is actually more
like:

Select * from Table1 where TheDate between [Start Date] and [End Date]

and I want to know what the Start Date and the End Date are inside the
VBA code because I need to do something with it.

TIA!

David