AboutScottGem Expertise I have been an IT professional for over 16 years. During that time I have accumulated a large store of knowledge and experience. This allows me to assist with a large variety of technology issues. My specialty is Database design primarily with MS Access. However, I can answer questions on many different IT related topics.
Experience I have worked in IT support for over 16 years. I have also helped people in cyberspace for a good portion of that time. I have been a volunteer staffer on Prodigy and AOL. I was the #2 ranked expert at AskMe.com (#1 in Technology) and am currently ranked at or near the top at Answerway.com and PointAsk.com.
Expert: ScottGem Date: 11/19/2007 Subject: Copying Data from a Query to a Form
Question Hi,
I would like to know how to automatically populate a form field with a value retrieved from a query. On the form a combo box is used to populate the employee ID. However, the user will not know the employee ID and will have to look up the ID by using the employee's name. There are over 15,000 employees in the list, so it will take a long time to navigate to it in the combo box. Alternatively, I created a query, activated by clicking a command button, to find the ID by prompting the user for the employee name and displaying all records associated with the name. How can I then click on the Employee ID in the query dynaset and have that value populated in the Employee ID field on the form without manually copying and pasting or typing it?
Answer Have you tried using a RowSource for the combo like:
SELECT EmployeeID, LastName & ", " & Firstname AS FullName FROM tblEmployees
ORDER BY Lastname, Firstname;
Then set the AutoExpand property to yes. Using this the employee can starttyping the employee's last name. the AutoExpand will bring the combo to the first maching record, the user can then continue to type the name or just scroll through the matching names.
If you want you can speed things up by using an initial filter. Add an unbound combo where the user can select a single letter. Then change the Rowsource to:
SELECT EmployeeID, LastName & ", " & Firstname AS FullName FROM tblEmployees
WHERE Lastname Like [cboInitial] * "*"
ORDER BY Lastname, Firstname;
So the user can select N from the initial combo and then the employee comsbo will only show last names starting with N.
Hope this helps,
Scott<>
Microsoft Access MVP 2007
