Pasing a non-from specific paramter to a query

[mlkiser via AccessMonster.com]

Hello. FIrst time poster and relatively new Access user. I have only a basic
knowledge of Access and VB and no formal training on either.

I have several checklists I am building, all a slightly different variation.
On these checklists is a button to send an email to the individual whose
record is displayed. Though the checklists are different, the email format
will be the same so I only want to build it once. What I need to do though to
make it work is be able to build a table on the fly that contains all the
variables that need to go in to the email, all of which aren't on the form.
So what I want to do is, when the email button is pressed on any of these
checklists, I want to build a table for only that social. I want the same
query to build the table no matter which checklist the button is pressed on.
As such, I want to build a query that will accept the Social Security Number
(var name ssan) as the cirteria. In other words, I want one query to work for
all seven checklists.

I have tried to build the query using just the component [ssan] as the
criteria but that doesn't work. It will only work if I put the full form path.
Needless to say, this isn't what I want as I don't want to have seven queries,
I just want to have one. So, two things here. One, why won't the criteria
work as I have it? If all the forms have the variable ssan, why can't I use
that as the criteria and have it accept whatever value the form happens to
have at the time? Two, since i have to accept the fact it doesn't work, how
can I do this so the query will work no matter what the from is that passes
the SSAN?

 

[John Spencer]

The reason you cannot reference the SSAN control on seven different forms is
the control on each form is a unique object as far as the program is
concerned.

You might be able to solve your problem by using a global variable declared
in a module and a function that returns the variable value in your query.

'====== Simplified VBA Module ======
Option Explicit

Dim gsSSAN as String

Public Function fGetSSAN()
fGetSSAN = gsSSAN
End Function

'==== End Module ===

You would have to have each list set the value of the global variable,
perhaps with some simple VBA code.

Another alternative would be to use VBA to return the value from whichever
form was open (assumption that you have code to detect that a form is open)
and then return the value from the control on the first form you found that
was open.

I can think of at least two more solutions. (more code and objects needed)
-- Use only one form and use code to change the form to meet your need
-- Use an additional form (hidden) and set a control value on it from your
other forms

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hello. FIrst time poster and relatively new Access user. I have only a
basic
knowledge of Access and VB and no formal training on either.

I have several checklists I am building, all a slightly different
variation.
On these checklists is a button to send an email to the individual whose
record is displayed. Though the checklists are different, the email format
will be the same so I only want to build it once. What I need to do though
to
make it work is be able to build a table on the fly that contains all the
variables that need to go in to the email, all of which aren't on the
form.
So what I want to do is, when the email button is pressed on any of these
checklists, I want to build a table for only that social. I want the same
query to build the table no matter which checklist the button is pressed
on.
As such, I want to build a query that will accept the Social Security
Number
(var name ssan) as the cirteria. In other words, I want one query to work
for
all seven checklists.

I have tried to build the query using just the component [ssan] as the
criteria but that doesn't work. It will only work if I put the full form
path.
Needless to say, this isn't what I want as I don't want to have seven
queries,
I just want to have one. So, two things here. One, why won't the criteria
work as I have it? If all the forms have the variable ssan, why can't I
use
that as the criteria and have it accept whatever value the form happens to
have at the time? Two, since i have to accept the fact it doesn't work,
how
can I do this so the query will work no matter what the from is that
passes
the SSAN?

 

[mlkiser via AccessMonster.com]

Thanks. I actually followed your last suggestion. I just created a "ghost"
from containing only the social and get it from there. Works fine.

The reason you cannot reference the SSAN control on seven different forms is
the control on each form is a unique object as far as the program is
concerned.

You might be able to solve your problem by using a global variable declared
in a module and a function that returns the variable value in your query.

'====== Simplified VBA Module ======
Option Explicit

Dim gsSSAN as String

Public Function fGetSSAN()
fGetSSAN = gsSSAN
End Function

'==== End Module ===

You would have to have each list set the value of the global variable,
perhaps with some simple VBA code.

Another alternative would be to use VBA to return the value from whichever
form was open (assumption that you have code to detect that a form is open)
and then return the value from the control on the first form you found that
was open.

I can think of at least two more solutions. (more code and objects needed)
-- Use only one form and use code to change the form to meet your need
-- Use an additional form (hidden) and set a control value on it from your
other forms

Hello. FIrst time poster and relatively new Access user. I have only a
basic

[quoted text clipped - 33 lines]

passes
the SSAN?