Apparently my brain has quit functioning. I have forgotten how to extract info from a table and have it automatically entered on a form. PROBLEM: I have a form that is used for data entry which contains many fields among them "employee ID" , "Last name", First name", "address" etc.. I would like to be able to have my form automatically fill in info such as that listed above from my employees table which contains this info by me entering any of the following; employee ID, or Last name and first name etc. If anyone can help me accomplish this via a simple means, please let me know. Many Thanks.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
extract data from table and fill in form
- Thread starter baxwink
- Start date
You are talking about a Search fucntion? I'm assuming you have bound the form to the table or query. The way that provides what I feel is the best balance between minimal coding and maximum flexibility is to have several Unbound textboxes and or comboboxes in a separate area, your 'search' area. You trigger the search either through a button or in the afterUpdate event of one of these boxes. (The button is my recommendation)<br>
Suppose one of the unbound search boxes is for Last Name. In the button's click event, do this:<br>
<br>
dim strLname as string,rst as recordset<br>
set rst = me.recordsetclone<br>
strLName = me!txtSearchLastName<br>
rst.findNext "lastname Like """ & strLName & "*"""<br>
If rst.nomatch then<br>
msgbox strLName & " not found"<br>
Else<br>
me.bookmark = rst.bookmark<br>
End If<br>
<br>
If there are multiple matches, the FindNext allows you to just keep pressing the search button and stepping to each match.<br>
<br>
Now expand the criteria part of the findfirst to include the other fields, with AND or OR,<br>
etc. A combobox has a built-in feature where, ie, if you loaded it with Lastname, it would jump to the first match of the characters you've typed into it. This is NOT a recommended solution, since the table containing the last name may have many thousands of records, and the box would take a long time to fill and to do it's own searching. The way I showed above is very useful and flexible, you can add as much as you want with the criteria, the Like clause, etc.<br>
--Jim
Suppose one of the unbound search boxes is for Last Name. In the button's click event, do this:<br>
<br>
dim strLname as string,rst as recordset<br>
set rst = me.recordsetclone<br>
strLName = me!txtSearchLastName<br>
rst.findNext "lastname Like """ & strLName & "*"""<br>
If rst.nomatch then<br>
msgbox strLName & " not found"<br>
Else<br>
me.bookmark = rst.bookmark<br>
End If<br>
<br>
If there are multiple matches, the FindNext allows you to just keep pressing the search button and stepping to each match.<br>
<br>
Now expand the criteria part of the findfirst to include the other fields, with AND or OR,<br>
etc. A combobox has a built-in feature where, ie, if you loaded it with Lastname, it would jump to the first match of the characters you've typed into it. This is NOT a recommended solution, since the table containing the last name may have many thousands of records, and the box would take a long time to fill and to do it's own searching. The way I showed above is very useful and flexible, you can add as much as you want with the criteria, the Like clause, etc.<br>
--Jim
- Status
Similar threads
- Locked
- Question
- Locked
- Question
