preempt SQL query's prompt for a variable

[Joe Clark]

In my evolving project, I now have a form with a fairly complicated
SQL statement which includes a WHERE clause like this : "WHERE
classID=[x]". When running the query, naturally, Access prompts the
user to supply a value for [x]. Is there any way my form can use VBA
to preempt the user prompt and fill in the value automatically (using,
for example, OpenArgs)?

 

[Marshall Barton]

In my evolving project, I now have a form with a fairly complicated
SQL statement which includes a WHERE clause like this : "WHERE
classID=[x]". When running the query, naturally, Access prompts the
user to supply a value for [x]. Is there any way my form can use VBA
to preempt the user prompt and fill in the value automatically (using,
for example, OpenArgs)?

If your form is already open, OpenArgs won't help.

How about stuffing the value of x into a hidden text box on
the form. Then the query can use Forms!yourform.thetextbox
instead of [x]

If the form is not open and another form is calculating the
value of x, then maybe you can put thetextbox on that form.

 

[Joe Clark]

In my evolving project, I now have a form with a fairly complicated
SQL statement which includes a WHERE clause like this : "WHERE
classID=[x]". When running the query, naturally, Access prompts the
user to supply a value for [x]. Is there any way my form can use VBA
to preempt the user prompt and fill in the value automatically (using,
for example, OpenArgs)?

If your form is already open, OpenArgs won't help.

How about stuffing the value of x into a hidden text box on
the form. Then the query can use Forms!yourform.thetextbox
instead of [x]

If the form is not open and another form is calculating the
value of x, then maybe you can put thetextbox on that form.

Yeah, but my question is how to put the [x] into the SQL query. Are
you saying I could put WHERE id=[Forms!yourform.thetextbox] right into
the SQL and it would go find that variable from the VBA module?

 

[Marshall Barton]

In my evolving project, I now have a form with a fairly complicated
SQL statement which includes a WHERE clause like this : "WHERE
classID=[x]". When running the query, naturally, Access prompts the
user to supply a value for [x]. Is there any way my form can use VBA
to preempt the user prompt and fill in the value automatically (using,
for example, OpenArgs)?

If your form is already open, OpenArgs won't help.

How about stuffing the value of x into a hidden text box on
the form. Then the query can use Forms!yourform.thetextbox
instead of [x]

If the form is not open and another form is calculating the
value of x, then maybe you can put thetextbox on that form.

Yeah, but my question is how to put the [x] into the SQL query. Are
you saying I could put WHERE id=[Forms!yourform.thetextbox] right into
the SQL and it would go find that variable from the VBA module?

Yeah, but
WHERE id=Forms![yourform].[thetextbox]
(note the brackets) does not look in the VBA module. A VBA
variable is only known in the VBA envirenment, so you can
not refer to a VBA variable anywhere outside of a VBA
module. OTOH, an open form is known in both VBA modules and
in Access queries (and some form/report property settings).

A more roundabout and less efficient way that can get to a
VBA variable is to use a public function in the (standard)
VBA module.

Dim x
Public Function GetX()
GetX = x
End Function

This works because standard module public functions are
known throughout Access.

I think it's possible that your confusion arises from a view
that Access is a monolithic entity when in fact it is just a
developement environment for creating a user interface
{forms/reports) to a data store that may be accessed through
data access libraries (DAO or ADO). The data store can
managed by a wide range of database engines such as
Jet(default), MSDE, SQL Server, Oracle, etc. Each database
engine has its own dialect of SQL with its own set of
features. The VBA environment and the ability to utilize it
throught public functions is an incredibly powerful feature
of the Access/Jet combination.

 

[onedaywhen]

my question is how to put the [x] into the SQL query. Are
you saying I could put WHERE id=[Forms!yourform.thetextbox] right into
the SQL and it would go find that variable from the VBA module?

Yeah, but
WHERE id=Forms![yourform].[thetextbox]
(note the brackets) does not look in the VBA module. A VBA
variable is only known in the VBA envirenment, so you can
not refer to a VBA variable anywhere outside of a VBA
module. OTOH, an open form is known in both VBA modules and
in Access queries (and some form/report property settings).

A more roundabout and less efficient way that can get to a
VBA variable is to use a public function in the (standard)
VBA module.

Dim x
Public Function GetX()
GetX = x
End Function

This works because standard module public functions are
known throughout Access.

In my evolving project, I now have a form with a fairly complicated
SQL statement which includes a WHERE clause like this : "WHERE
classID=[x]". When running the query, naturally, Access prompts the
user to supply a value for [x]. Is there any way my form can use VBA
to preempt the user prompt and fill in the value automatically

Such a procedure can be executed, passing the parameter value as part
of the call. This can be done in a variety of ways, including using an
ADO Command object with child Parameter object and using the EXECUTE
keyword in Jet SQL while in ANSI-92 query mode e.g. using the
ADODB.Execute method:

Set rs = CurrentProject.Connection.Execute("EXECUTE Query1 55;")

There may even be a way of doing similar with DAO. This may not fit
with the 'bound forms' approach.

Jamie.

--

 

[Joe Clark]

Yeah, but my question is how to put the [x] into the SQL query. Are
you saying I could put WHERE id=[Forms!yourform.thetextbox] right into
the SQL and it would go find that variable from the VBA module?

Yeah, but WHERE id=Forms![yourform].[thetextbox]
(note the brackets) does not look in the VBA module.

That's what I thought

OTOH, an open form is known in both VBA modules and
in Access queries (and some form/report property settings).

You mean, if there's a textbox called [x] on my form, I can use "WHERE
id=[x]" in the SQL?

A more roundabout and less efficient way that can get to a
VBA variable is to use a public function in the (standard)
VBA module.

Dim x
Public Function GetX()
GetX = x
End Function

In this case would my SQL statement say "WHERE id=[GetX()]"?

I guess there's a way one can assemble the SQL query (record source)
of a form on-the-fly as the form loads by concatenating a bunch of
string variables; I'm trying to avoid that and make my program a
little elegant and easy-to-change by placing just the right SQL query
in the record source Property of the form.

 

[Joe Clark]

Thanks for all the useful ideas. For posterity's sake, here is what I
did to get the desired result.
I created a new module ("UberModule") unattached to any forms and
added a public variable:
Public SSFRclassID As Integer

Nevermind the name... SSFR is just an abbreviation of the name of the
form that uses the variable. In the parent form that calls up my
student roster, I added this line to the "open linked form" action
code:
UberModule.SSFRclassID = [classID]

I found it was better to do this in the parent form than in the linked
form, because there doesn't seem to be any event that executes before
the first query (not even BeforeQuery). Next, in the UberModule, I
created a function that, when called, returns the variable:
Public Function SSFRkey()
SSFRkey = SSFRclassID
End Function

Finally I altered the SQL of my query to use "... WHERE
classID=SSFRkey() ...". I'm getting the right result every time.

 

[Marshall Barton]

Thanks for all the useful ideas. For posterity's sake, here is what I
did to get the desired result.
I created a new module ("UberModule") unattached to any forms and
added a public variable:
Public SSFRclassID As Integer

Nevermind the name... SSFR is just an abbreviation of the name of the
form that uses the variable. In the parent form that calls up my
student roster, I added this line to the "open linked form" action
code:
UberModule.SSFRclassID = [classID]

I found it was better to do this in the parent form than in the linked
form, because there doesn't seem to be any event that executes before
the first query (not even BeforeQuery). Next, in the UberModule, I
created a function that, when called, returns the variable:
Public Function SSFRkey()
SSFRkey = SSFRclassID
End Function

Finally I altered the SQL of my query to use "... WHERE
classID=SSFRkey() ...". I'm getting the right result every time.

That's logically correct (the slightly more roundabout way I
mentioned earlier). A subtle issue with that is that it can
make debugging more complex because any unhandled error in
any code will **reset** your code project (i.e. clear all
module level and static variables) so you need to be more
careful about initializing the global variables.

The way many people avoid that complication is to use a
hidden text box on an open form. Some people go so far as
to create a separate, always open, hidden form just to hold
the "global" values. I usually just use a hidden text box

You mean, if there's a textbox called [x] on my form,
I can use "WHERE id=[x]" in the SQL?

No, that is not quite correct. Because there might be a
text box named x on any number of open forms, you need to
specify the form that contains the x you want to use:
WHERE id=Forms!theform.x

I also note that you have missused the [ ] in a couple of
places. The brackets must be around a single name, not a
full reference, i.e. [Forms!theform.x] is wrong. Note that
the [ ] are not required when the name contains only
alphanumeric and underscore characters. OTOH, Access often
adds the brackets around every name, so you might see some
redundant brackets, e.g. [Forms]![theform].[x] even though
you did not put them there. The best advice I can give on
this issue is to only use names with alphanumeric
characters.