In an Access database, I have a form that, after the user performs a search, displays the name of an employee, the parking tag #(s) that have been assigned to them and their phone extension. I'm using a search box (a combo box) on that form where the user can search using the Tag # to get the results mentioned above. A query showing all the employees and their related Parking Tag numbers is the data source used in the combo box. The combo box also has the following code in an After Update event procedure:
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[TagInventory#] = '" & Me![Combo3] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
This works well when the user searches by the Tag number.
I'm trying to create a similar search box on another form but in this case the user can search by name. The above code doesn't work where an employee has 2 Parking Tags assigned to them. How do I adapt the code or what other procedure can I use so that all the records are displayed when an employee has more than 1 Tag# assigned to them. What can be used in place of FindFirst or FindLast?
StarTrekker and weejas:
The TagInventory table consists of a TagID# (autonumber), TagColor, TagStatus, DateIssued and an EmployeeID. The form the user uses displays the above fields. The user finds a Tag# where the status is "unassigned" and enters the EmployeeID in the EmployeeID field on the form.
Ok, it helps a little... you haven't revealed the structure of the Employee table though, the underlying query for the form or the key properties of the combo box.
I'll make some assumptions, but you'll have to adapt it to your situation.
- the Employee table is included in the underlying SQL for the form.
- EmployeeName is a field visible somewhere on the form.
- the form's Record Source is "qryTagDetails".
- the combo box is called NameSearchCombo and has the text field EmployeeName as its bound column.
' Find the records that matches the control, based on EmployeeName.
Me.RecordSource = "SELECT * FROM qryTagDetails WHERE EmployeeName Like """ & Nz(Me.NameSearchCombo,"*") & """"
That should show only tags for the name selected in the combo box.