Filling query parameters from different forms without VB

[Daniel Zazula]

Suppose that I have an Access 2007 application and a query
(ExampleQuery) that deletes one or more records using as criteria a
field (ExampleField) present in a form (ExampleForm), like this:

Delete * From ExampleTable Where ExampleTableColumn > [Forms]!
[ExampleForm]![ExampleField]

Now suppose that I need to change that query so it can be called from
various forms, and not only from 'ExampleForm', so I turn it into a
parameterized query:

Delete * From ExampleTable Where ExampleTableColumn

:ExampleParameter

How can I fill this parameter from 4 different forms? (no prompting
the user for parameters)

In the real case I need to change lots of queries and lots of forms
and neither I nor anybody in the company knows VB, so I can't use
temporary variables or VB script, unless someone knows a very generic
VB function like OpenQuery(QueryName, ArrayOfParameters,
ArrayOfParametersValues). Macros are Ok through.

I heard that this is possible in Access 2010, through I don't know
how, I was wondering if its possible in Access 2007 also.

 

[John Spencer]

If I were using VBA I would build a simple function to return the value from
the various form controls. Assumption is that only one form is open at a time
or if multiple forms can be open there is a precedence on which form to use.

In the query you would have
DELETE * FROM ExampleTable WHERE ExampleTableColumn > fGetValue()

This untested sample code is not the best but it should work. I would
normally use an additional function to test if a specific form was open
instead of relying on the clunky error trapping.

Public Function fGetValue()
Dim vReturn as Variant
vReturn = Null

On Error GoTo ProcError
vReturn = Forms!NameOfFormA!NameOfControlOnFormA

If IsNull(vReturn)Then
vReturn = Forms!NameOfFormB!NameOfControlOnFormB
End If

If IsNull(vReturn)Then
vReturn = Forms!NameOfFormC!NameOfControlOnFormC
End If

If IsNull(vReturn)Then
vReturn = Forms!NameOfFormD!NameOfControlOnFormD
End If

fGetValue = vReturn

Exit Function

ProcError:
vReturn = Null
Resume Next

End Function

That said, it would probably be simpler to execute a query from a button on
each form using some VBA to construct and execute a query string.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Suppose that I have an Access 2007 application and a query
(ExampleQuery) that deletes one or more records using as criteria a
field (ExampleField) present in a form (ExampleForm), like this:

Delete * From ExampleTable Where ExampleTableColumn > [Forms]!
[ExampleForm]![ExampleField]

Now suppose that I need to change that query so it can be called from
various forms, and not only from 'ExampleForm', so I turn it into a
parameterized query:

Delete * From ExampleTable Where ExampleTableColumn

:ExampleParameter

How can I fill this parameter from 4 different forms? (no prompting
the user for parameters)

In the real case I need to change lots of queries and lots of forms
and neither I nor anybody in the company knows VB, so I can't use
temporary variables or VB script, unless someone knows a very generic
VB function like OpenQuery(QueryName, ArrayOfParameters,
ArrayOfParametersValues). Macros are Ok through.

I heard that this is possible in Access 2010, through I don't know
how, I was wondering if its possible in Access 2007 also.

 

[Ron2006]

What I have done for this is create a hidden form that can contain
various and sundry fields for these types of queries.

1) I created a form and have the very first form that opens in the
application open this form as hidden.
2) Any time I have a query that may end up being called from multiple
places I update the value in a corresponding/appropriate field on that
hidden form and then let the query run.

That way any and/or all queries can use the same single field as
criteria.

Ron

 

[John Spencer]

Better idea.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Daniel Zazula]

Thanks Guys.
That form workaround will do until we switch to Access 2010 or, even
better, drop this app for good.

Daniel Zazula