Filling query parameters from different forms without VB

  • Thread starter Thread starter Daniel Zazula
  • Start date Start date
D

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
Click to expand...

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.
 
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

Daniel said:
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
Click to expand...

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.
Click to expand...
 
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
 
Better idea.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Thanks Guys.
That form workaround will do until we switch to Access 2010 or, even
better, drop this app for good.

Daniel Zazula
 
Back
Top