Form-based parameter query

[Guest]

I have 9 form-based parameter queries set up ... one for a header form and 8
for related subforms. Is there an easy way to tell them (one at a time ...
or all at once) to use a different form as a criteria for a specific run?
(The database COULD be used by multiple people at the same time.) There's
only one field that utilizes a criteria ... the same field in all nine
queries.

I'm hoping I don't have to set up 9 additional queries to accomplish the
same thing??

Thanks!

 

[Marshall Barton]

I have 9 form-based parameter queries set up ... one for a header form and 8
for related subforms. Is there an easy way to tell them (one at a time ...
or all at once) to use a different form as a criteria for a specific run?
(The database COULD be used by multiple people at the same time.) There's
only one field that utilizes a criteria ... the same field in all nine
queries.

Are you saying that the query's WHERE clause looks like
thisfield = Forms!someform.atextbox


If so, I suggest that you create a standard module with a
Public variable where any form can park the parameter's
value using a simple assignment statement:
MyParam = 12345

Then add a function to the module to return the variable:

Public MyParam As Long 'or String
Public Function MyFunc()
MyFunc = MyParam
End Function

With that in place, the query's WHERE clause can be:
thisfield = MyFunc()

 

[Guest]

Are you saying that the query's WHERE clause looks like
thisfield = Forms!someform.atextbox

Is that the same as "[8D ID] = [Forms]![8D Header Form]![8D ID]" ?
P.S. [8D ID] is an AutoNumber field....

If so, I suggest that you create a standard module with a
Public variable where any form can park the parameter's
value using a simple assignment statement:
MyParam = 12345

Is "12345" merely representative? Or do I actually use those numbers?

When I added a new module, "Option Compare Database" was automatically
added. Do I just type the "MyParam = 12345" on the line immediately after?
Or do I need more?

Then add a function to the module to return the variable:

Public MyParam As Long 'or String

Is this just another single line in the module?

Public Function MyFunc()
MyFunc = MyParam
End Function

This one makes sense to me, but I won't know until I get the others to work!

With that in place, the query's WHERE clause can be:
thisfield = MyFunc()

When am I actually assigning my [8D ID] value to "MyFunc"?

 

[Marshall Barton]

Are you saying that the query's WHERE clause looks like
thisfield = Forms!someform.atextbox

Is that the same as "[8D ID] = [Forms]![8D Header Form]![8D ID]" ?
P.S. [8D ID] is an AutoNumber field....
Yes

If so, I suggest that you create a standard module with a
Public variable where any form can park the parameter's
value using a simple assignment statement:
MyParam = 12345

Is "12345" merely representative? Or do I actually use those numbers?

It is representative. You probably do not actually want to
use a number. Most likely, you want to set it to the value
in the current record on the form:
MyParam = Me.[8D ID]

When I added a new module, "Option Compare Database" was automatically
added.

As a good practice, you should add a line immediately after
that:
OPTION EXPLICIT
to tell Access that you want it to tell you about use of
undeclared variables.

Do I just type the "MyParam = 12345" on the line immediately after?
Or do I need more?

No. You add the other code I posted to the module, but
setting MyParam should be done in each procedure that opens
the form.

Is this just another single line in the module?

Yes. It goes before the function so that code in any VBA
procedure can access it.

Public Function MyFunc()
MyFunc = MyParam
End Function

This one makes sense to me, but I won't know until I get the others to work!

With that in place, the query's WHERE clause can be:
thisfield = MyFunc()

When am I actually assigning my [8D ID] value to "MyFunc"?

As I said above, you do this by using:
MyParam = Me.[8D ID]
in each procedure that open the form.

 

[Guest]

It was working great sometimes and others not ... but I think it was a
problem with me not always assigning a value to the parameter. I think I've
caught them all now and it seems to be working all of the time. Thanks so
much for your help, Marshall.
Cindy

ckrogers wrote:

I have 9 form-based parameter queries set up ... one for a header form and 8
for related subforms. Is there an easy way to tell them (one at a time ...
or all at once) to use a different form as a criteria for a specific run?
(The database COULD be used by multiple people at the same time.) There's
only one field that utilizes a criteria ... the same field in all nine
queries.


Are you saying that the query's WHERE clause looks like
thisfield = Forms!someform.atextbox

Is that the same as "[8D ID] = [Forms]![8D Header Form]![8D ID]" ?
P.S. [8D ID] is an AutoNumber field....
Yes

If so, I suggest that you create a standard module with a
Public variable where any form can park the parameter's
value using a simple assignment statement:
MyParam = 12345

Is "12345" merely representative? Or do I actually use those numbers?

It is representative. You probably do not actually want to
use a number. Most likely, you want to set it to the value
in the current record on the form:
MyParam = Me.[8D ID]

When I added a new module, "Option Compare Database" was automatically
added.

As a good practice, you should add a line immediately after
that:
OPTION EXPLICIT
to tell Access that you want it to tell you about use of
undeclared variables.

Do I just type the "MyParam = 12345" on the line immediately after?
Or do I need more?

No. You add the other code I posted to the module, but
setting MyParam should be done in each procedure that opens
the form.

Is this just another single line in the module?

Yes. It goes before the function so that code in any VBA
procedure can access it.

Public Function MyFunc()
MyFunc = MyParam
End Function

This one makes sense to me, but I won't know until I get the others to work!

With that in place, the query's WHERE clause can be:
thisfield = MyFunc()

When am I actually assigning my [8D ID] value to "MyFunc"?

As I said above, you do this by using:
MyParam = Me.[8D ID]
in each procedure that open the form.