Find Record
---
Hi Nancy,
Make one or more unbound combos on your form. Let the first column be
invisible and be the primary key ID of the recordsource of your form 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
'~~~~~~~~~~~~~~~~~~~~
so you would make 2 unbound combos. The first would SHOW the first
column, ID field.
the second would still have ID as the first column, but it would be
invisible.
The AfterUpdate event for both would be the same since the bound column
in each case is the ID field
Lets say you have a People table with a PID (PeopleID) autonumber field.
Then, you can make a combobox bound to the PID that displays names
create a combobox control
Name --> FindName
RowSource -->
SELECT
PID,
LastName & ", " & Firstname AS Fullname,
BirthDate
FROM People
ORDER BY LastName, Firstname
BoundColumn --> 1
ColumnCount --> 3
columnWidths --> 0;2;1
(etc for however many columns you have -- the ID column will be hidden)
ListWidth --> 3
(should add up to the sum of the column widths)
AfterUpdate --> =FindRecord()
~~~~
although you made the subject of your post "Filter", I assumed what you
really wanted to do was "Find"
If this is not what you want, please specify more details. Thanks <smile>
Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
