Hi Uschi,
in the header of almost every main form, I create one or
more combos to Find a record. The display part of the combo
does not have to be more than a few characters -- however
many you think they might need to see when they use the
TypeAhead to find an item in the list.
If you have filter controls, rather than filtering the form,
filter the Find combos
Make one or more unbound (no ControlSource) combos on your
form (like in the header). Let the first column be
invisible and be the primary key ID of the RecordSource of
your form and then, on its AfterUpdate event, put this in
the [Event Procedure] code:
'~~~~~~~~~~~~~~~
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
With Me
'find the first value that matches
.RecordsetClone.FindFirst "SomeID = " & mRecordID
'if a matching record was found, then move to it
If Not .RecordsetClone.NoMatch Then
.Bookmark = .RecordsetClone.Bookmark
End If
End With
End Function
'~~~~~~~~~~~~~~~~~~~~
where
- SomeID is the Name of the primary key field, which is in
the RecordSource of the form -- assuming your primary key is
a Long Integer data type (autonumbers are long integers)
Remember that the Rowsource for a combo can come from
anywhere -- it can pull from multiple tables or only use one
... just make sure that the first column is the primary key
ID of the table you want to search (and that field is part
of the RecordSource for the form you are searching).
For instance, if your main form is People, you could have a
combo to find a person using their phone number by linking
to the Phones table (assuming your data is normalized) and
putting the PeopleID in the first column (hidden) and the
Phone in the column that displays. In this case, I use a
procedure to strip non-numeric characters so the user does
not have to type parentheses or dashes since I store phone
numbers with symbols. The width of this column is set tiny
so it is greater than zero, but is small enough so the user
does not see it -- and this is the second column. The user
actually sees the third column -- the one with the mask symbols.
If you are searching the recordset on another form, change
the FindRecord name to be specific (like FindRecord_Order)
and, substitute
With Me --> With forms!formname
If the record you are looking for is on a subform, change
the FindRecord name to be specific (like
FindRecord_Whatever) and, substitute:
With Me --> with Me.subform_controlname.form
Caveat: if you show users how to filter forms, I do have a
version of this function that checks for a filter if the
record is not found, removes the filter, and finds the record
Warm Regards,
Crystal
remote programming and training
http://MSAccessGurus.com
free video tutorials
http://www.YouTube.com/user/LearnAccessByCrystal
Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page book that covers essentials in Access
*
have an awesome day
*
I would like to create a combo box based on two fields on my form that will
list each [CertificateNo] that is marked [Registered (Yes)]. Registered
[quoted text clipped - 7 lines]
