Can I run a query from a current record on a form?

C

Casandra

How can I run a query based on the current record on the form? I have a
field SSN that I need to run a query on from the form. I have the query
running with a command button, but the only way I thought of was to use a
promt? Is there a way to run the query from the current SSN that is showing
form?.

Thanks much
 
T

Tom Ellison

Dear Casandra:

Your query can reference the value on an open form. In place of the prompt,
put a reference to the form It would look like this:

[Forms]![FormName]![ControlName]

Replace FormName and ControlName with the actual name of your form and the
control on it that contains the desired value.

It is very important that the ControlName be unique. It could conflict with
the name of a column in the bound table, for example. Using a prefix for
control names is a good (not perfect) protection against this. You can find
a description of this in many Access Good Practices articles.

Tom Ellison
 
N

Nikos Yannacopoulos

Cassandra,

In your query design, add a reference to the form control in the first
criteria line under the SSN field; assuming the form is called MyForm
and the control is called txtSSN, the reference syntax would be:

Forms![MyForm]![txtSSN]

Just substitute the actual form and control names, and the trick is done.

HTH,
Nikos
 
C

casandra.iacuzzo

Nikos said:
Cassandra,

In your query design, add a reference to the form control in the first
criteria line under the SSN field; assuming the form is called MyForm
and the control is called txtSSN, the reference syntax would be:

Forms![MyForm]![txtSSN]

Just substitute the actual form and control names, and the trick is done.

HTH,
Nikos
Click to expand...
 
C

casandra.iacuzzo

Thanks, I did that, but.. I created a command button the run the query,
and I get the pop up box asking me for the SSN. I think I should have
mentioned that the query is based on a subform.. or does it not matter.


Thanks!!!
 
C

casandra.iacuzzo

Thanks, I did that, but.. I created a command button the run the query,
and I get the pop up box asking me for the SSN. I think I should have
mentioned that the query is based on a subform.. or does it not matter.


Thanks!!!
 
N

Nikos Yannacopoulos

I get the pop up box asking me for the SSN
Click to expand...
Any chance you had specifically declared SSN as a parameter in the
original query (through menu item Query > Parameters), and forgot to
remove it after you changed the criterion to look up the SSN control on
the form? If yes, remove the parameter reference.


mentioned that the query is based on a subform..
Click to expand...
does not make much sense. Forms (and subforms) can be based on queries -
meaning they can have queries as their recordsources, but not the other
way around!
Any chance you mean the SSN control that you wish to filter your query
on, is in a subform rather than in the main form? If yes, then the
syntax must be:

Forms![FormName]![SubformName]![txtSSN]


HTH,
Nikos
 
Top