Doing a query based on input from user

  • Thread starter Thread starter Taffy-Taff
  • Start date Start date
T

Taffy-Taff

This might not seems technical but I need some assistance with it.
have a form with a text box for user name. The user will enter a nam
and this information is stored in a variable called Fname.
I also have a table that contains certain information, with one fiel
name called "Collector"

I want to prepare some codes to get a query and a report for th
"collector' whose name is stored under Fname.

thanks much

Taff
 
In other words, you want the user's entry in the textbox on the form to be
used as a criterion in your query? If that's the case, then assuming that
the form is kept open while the query/report is run, you don't need any
code, and you don't even need the global variable. Assuming your form is
called Form1 and the textbox is called Txt1, all you need to do is go in the
query's design and add the following expression in the criterion line of the
field in question:

Forms!Form1!Txt1

You can make it more user-friendly and foolproof by using a listbox or combo
box instead of the textbox, so the user is only required to select their
name from a list rather than type it (and get the occasional misspelling
problem).

HTH,
Nikos
 
Thanks for the assistance. I obviously did not fully express what I
wanted to be done. I know how to get the query based on the user
input. This is the real problem:

I have a form that is populated with information based on a filtered
table. The query to filter the table is working fine. The form is now
populated with the first record in the filtered table. What I want to
do is to put a textbox on the form (called GetAnAccount). When the
person enters a specific account # and press ENTER, I want the form to
be populated with the data for that specific record. Can you assist
with how to have this done?

Thanks much.
Taffy
 
Taffy,

You can use an event like On Exit, On Lost Focus, After Update of the
GetAnAccount txtbox, so it will fire when the user hits enter. Not my
preferred solution, though, I would prefer a command button instead, so as
not to pass on focus to another control or even the next record. At any
rate, the code run by the event would be something like:

DoCmd.ApplyFilter , "AcctNo=" & Me.GetAnAccount

where I have assumed that the field name is AcctNo, change to the actual
field name.

HTH,
Nikos
 
Hi Taffy,

I suggest a command button (cmdGetAccount?) located, say, just to the right
of the textbox. I'd probably make its caption" &Get Account ". Using a
command button rather than counting on the ENTER key will remove
ambiguities.

Look in Access Help for GoToRecord. complete the where clause for the field
in your table matching the value of the text box with the account number.
Place that code in the Click event of cmdGetAccount.

HTH
 
Back
Top