A query question (using the value of a control in a sql statement possible?)

[Gary]

I have a query which finds the records entered in a table which match a
certain field (salesman) and then displays the last entry entered using
the value of the primary key to determine which the last entry was as
follows...

SELECT *
FROM [Initial Contact]
WHERE [id] = (SELECT Max([id]) FROM [Initial Contact] AS X
WHERE X.[Salesman] LIKE [Enter salesman's name:] & "*");

i have an on open macro on the form which simply runs this query.
everytime the form is opened it asks for the salesman's name and then
displays the last entry for that salesman, which is perfect.

Question:
i want a button on my form which refreshes the form, so that if in the
meantime whilst the form has been open an entry has been made to the
table for the salesman in question - he can refresh the screen and get
the latest entry.

At the moment my query prompts everytime for the salesman name, given
the fact that i'm running this second query from a form which allready
has that information, in a control - how do i modify the query so that
instead of asking for the salesman's name - it picks it up from the
control called 'cntrlsalesman' on my form?

thanks for your valuable time!

Gary

 

[Arvin Meyer [MVP]]

Instead of: [Enter salesman's name:]

use: [Forms]![YourFormName]![cntrlsalesman]
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access