FindRecord
---
Hi Chloe,
I am assuming that you have an autonumberID in your Employees table to
uniquely identify each employee
I find it best to just put a search combo in the header of the Employees
form so the user can easily resposition the record pointer.
Let the first column be invisible and be the primary key ID of the
recordsource of your form (ie: EmployeeID) and then, on its AfterUpdate
event...
=FindRecord()
this code goes behind the form:
'~~~~~~~~~~~~~~~~~~~~
Private Function FindRecord()
'if nothing is picked in the active control, exit
If IsNull(Me.ActiveControl) Then Exit Function
'save current record if changes were made
If me.dirty then me.dirty = false
'declare a variable to hold the primary key value to look up
Dim mRecordID As Long
'set value to look up by what is selected
mRecordID = Me.ActiveControl
'clear the choice to find
Me.ActiveControl = Null
'find the first value that matches
Me.RecordsetClone.FindFirst "IDfield = " & mRecordID
'if a matching record was found, then move to it
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
End Function
'~~~~~~~~~~~~~~~~~~~~
where
IDfield would be changed to EmployeeID, or the name of your autonumber
employee ID
so, if you want to search by first & last name...
make a combo
RowSource -->
SELECT
EmployeeID
, Firstname & " " & Lastname as EmpName
FROM Employees
ORDER by Firstname, Lastname;
columnCount --> 2
ColumnWidths --> 0;2
ListWidth --> 2
~~~~
because this code is written somewhat generically, you can put multiple
combos in the header to find emplyees by different ways -- just make
sure the first column is always the EmployeeID
Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
