Setting up query parameter to UNION query by VBA

[AlexT]

Folks

I have an UNION query that "joins" to "sub-queries", each of them
having a parameter.

I'd like to set a value for the parameter thorough VBA. Should I set
it at the "top" query level of for each "sub-query" ?

To be honest I tried both an nothing work, ie.

Set db = CurrentDb
Set qdf = db.QueryDefs("myQuery")
qdf.Parameters("refDate").value = myValue
debug.print qdf.Parameters("refDate").value ' This is ok

then

DoCmd.OpenForm "myForm"

where myForm.RecordSource is myQuery, the union query, doesn't seem to
work as Access prompts me to enter a value for refDate...

Regards

--alexT

 

[Dale Fye]

Alex,

You cannot use a parameter query as the recordsource for a form, or as a
rowsource for a control, unless the parameter is:
1. a reference to a control on a form
2. a function that returns a value

A lot of people create a hidden control on form that is always open (but
normally hidden) in their application. If you use this technique, you would
just have to set the value of the control on that form, then define the
parameter in your query as the name of that control
(Form!frmSplash.txtRefDate). If you do this, you might also need to actually
declare the parameter as a date.

I use both of these techniques, but prefer the latter, because it doesn't
require that I have a particular form open when I am working with my queries.
An example of such a function follows:

Public Function fnRefDate(Optional SomeValue As Variant = Null) As Variant

'I use variants to allow the user to pass a NULL value to the function
'If you use a date datatype, you cannot set it to NULL, and the default
value
'is set to 12 AM, on 30 Dec 1899

'I use a static variable so that the value of myRefDate is retained
between
'calls to the function
Static myRefDate As Variant

If IsEmpty(myRefDate) And IsNull(SomeValue) Then
myRefDate = Null
ElseIf Not IsNull(SomeValue) Then
If IsDate(SomeValue) Then
myRefDate = SomeValue
Else
myRefDate = Null
End If
End If

fnRefDate = myRefDate

End Function

This function accepts an optional parameter which sets the value of the
function if it is provided and is a date data type. If you don't pass it a
parameter, it will return the most recent value passed to it, or a NULL.

In your example, all you would have to do is pass it a value, I would do
this in the AfterUpdate event of the control that contains your RefDate, and
in the Forms current event if RefDate is a bound control.

Private Sub txt_RefDate_AfterUpdate

fnRefDate me.txt_RefDate.Value

End Sub

Then, I would modify the SQL for "myQuery" to look something like:

SELECT * FROM yourTable
WHERE [SomeDate] = fnRefDate()

An advantage of this technique over using a reference to a forms control is
that you can set the value of fnRefDate from the immediate window, and you
can use it in forms or reports.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[AlexT]

Hello

An advantage of this technique over using a reference to a forms control is
that you can set the value of fnRefDate from the immediate window, and you
can use it in forms or reports.

That was EXACTLY was I was looking for - great stuff !

Thanks

--alexT