UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Find Record    
Find Record


Comboboxes to Find Records

(or Filter other combos)

In the header of main forms, create one or more unbound (no ControlSource) Unbound_Control combos to Find a record. Let the first column (be invisible and) be the primary key ID of the RecordSource of your form. The display part of the combo does not have to be more than a few characters. You can choose to show columns from more than one table. (or Filter other combos)

code to find record in code behind form

On the AfterUpdate event, put this in the [Event Procedure] code:


this code goes behind the form to process all the calls to FindRecord:

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 nRecordID As Long

  'set value to look up by what is selected
  nRecordID = Me.ActiveControl

  'clear the choice to find
  Me.ActiveControl = Null

  With Me
     'find the first value that matches
     .RecordsetClone.FindFirst "RecordID = " & nRecordID

    '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 - RecordID 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 (AutoNumber is Long Integers)

If RecordID is text or date, its value must be delimited with quotes (text) or hash marks (date).

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 based on a table called Contacts, you might have a combo to find a person using their phone number by linking to a Phones table and putting the ContactsID in the first column (hidden) and the Phone in the column that displays. In this case, you may wish to use a procedure to strip non-numeric characters so the user does not have to type parentheses or dashes since phone numbers may be stored with the symbols. The width of this column can be very small so it is greater than zero, but is tiny enough that 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.

code to StripPhoneNonNumeric characters

Function StripPhoneNonNumeric(pPhone As String) As String
  Dim sPhone As String _
     , i As Integer _
     , sChar As String * 1
  'loop through number and only keep numeric characters
  For i = 1 To Len(pPhone)
     sChar = Mid(pPhone, i, 1)
     If IsNumeric(sChar) Then
        sPhone = sPhone & sChar
     End If
  Next i
  StripPhoneNonNumeric = sPhone
End Function

sample SQL using function to strip non-numeric values from Phone number for easy finding

for instance, the SQL might be:

SELECT Contacts.ContactID
, StripPhoneNonNumeric(pho.Phone) as Phone_
, Phone
, Contacts.NameLast & (', '+Contacts.NameFirst) & (' ' & Contacts.NamMid) AS [ContactName]
FROM (tbl_Contacts AS Contacts " _
INNER JOIN t_Phone AS pho ON Contacts.ContactID=pho.ContactID) " _
ORDER BY pho.Phone, Contacts.NameLast, Contacts.NameFirst;

searching on another form

If you are searching the recordset on another form, change the FindRecord name to be specific (for instance, FindRecord_Invoice) and, substitute

With Me --> With forms!formname

searching on a suborm

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 users will filter forms, if the record is not found: check for a filter, remove the filter, and find the record -- or specify a WHERE clause in the SQL of the RowSource for each combo so nothing can be chosen that is not showing.

Instead of filtering the form, you may want to simply filter the combos to Find Records.

Creating a procedure to Find Record in a general module

Since Finding records is a common feature in a database, it makes sense to generalize the FindRecord function so that it can be used from any form. You may want to add N to the end of the procedure name to distinguish it and also to specify that the assumption is the value of the key is Numeric -- and specifically, a Long Integer

' usually used by Find combos on forms
' find record given a numeric fieldname and value

Function FindRecordN(pF As Form _
  , pKeyFieldname As String _
  , Optional pCtrlName_SetFocus As String = "" _
  , Optional pRecordID = 0 _
  , Optional pbClear As Boolean = True _
  , Optional pbChkIsLoaded As Boolean = False _
  ) As Boolean

  'Crystal (strive4peace)
 'pF --> form reference
 'pKeyFieldname = name of numeric key field
 'pCtrlName_SetFocus -- name of control to set focus to
 'pRecordID = numeric value of key field to look up
 'pbClear -- set control = Null after finding (default = true)
 'pbChkIsLoaded -- check if form is loaded (default = false)
  '  on the AfterUpdate [Event Procedure] of a control to find a record (ie: combo or listbox)
  '   FindRecordN Me, "SoftwareID", "SoftwareName"
  ' if you are searching a subform:
  '   FindRecordN Me.subform_controlname.form, "SoftwareID", "SoftwareName"
  ' if you want to find a record, such as you got the ID in code:
  '   FindRecordN Me, "SoftwareID", "SoftwareName", lngSoftwareID
  '       by default, if you specify an ID to find,
  '       the procedure will not attempt to clear any controls
  ' if you are finding a record on a form that may not be open:
  '    FindRecordN Forms!People, "PeopleID", "LastName",,,True
  ' if you are want to send a value of the field to search:
  '   FindRecordN Me, "PID", "Lastname", lngPID
  '      note: if you are sending a value, the control will not be cleared
  'set up Error Handler
  On Error GoTo Proc_Err

  FindRecordN = False
  If pbChkIsLoaded Then
     If Not IsLoaded(pF.Name) Then
        Exit Function
     End If
  End If
  If pRecordID = 0 Then
     'if nothing is picked in the active control, exit
     If IsNull(pF.ActiveControl) Then Exit Function
     'set value to look up by what is selected
     pRecordID = pF.ActiveControl
     'clear the choice to find
     If pbClear Then pF.ActiveControl = Null
  End If

  'make sure form is open
  'save current record if changes were made
  If pF.Dirty Then pF.Dirty = False

  'find the first value that matches
  pF.RecordsetClone.FindFirst pKeyFieldname _
     & "= " _
     & pRecordID

  'if a matching record was found, then move to it
  If Not pF.RecordsetClone.NoMatch Then
     pF.Bookmark = pF.RecordsetClone.Bookmark
     Goto Proc_Exit
  End If

  If pCtrlName_SetFocus <> "" Then
     'this fails if controlname is not correctly specified
  End If
  FindRecordN = True
  On Error Resume Next
  Exit Function

  MsgBox Err.Description, , _
       "ERROR " & Err.Number & "   FindRecordN"
  Resume Proc_Exit

  'if you want to single-step code to find error, CTRL-Break at MsgBox
  'then set this to be the next statement
End Function
Edit Discussion
Custom Search

Thank you for your support!
This page has been accessed 7,311 times.  This page was last modified 02:27, 25 August 2012 by Mark Davis. Contributions by Jack Leach and Strive4peace  Disclaimers