Find Record

Logic

Using With pF and End With enhances performance and makes code easier to read. Anything starting with a dot (.) between these two statements is a property or method of the passed form.

This is often called on the AfterUpdate event of an unbound combo box to pick a record to find. In these cases, the key ID to find will be read from the active control. If the active control is Null then the code exits because there is nothing to look for. Unless pbClear is set to False, the combo box (or whatever control is active) will be cleared after the value is read.

When pnKeyID is zero (0), that indicates that the active control has the key value, which is the default. So if you might want to actually find a record whose ID is 0, then modify the code to use a different number as an indicator.

If a value (other than 0) for pnKeyID is sent, then the active control does not matter. So this function can be called to find a record where the key ID to find is determined in code. It can also be used to find a record on a subform, or another open form.

In most cases the key ID will be a Long Integer, but it is declared as a variant. This will allow anything to 'work' (such as a string containing a numbers).

Once the value of the key ID to look for is determined, then if there are unsaved changes to the record on the form (Dirty), they are saved. It is a good idea to specificically save a dirty record before moving the record pointer.

A copy of the form recordset, the RecordsetClone, is searched for the key field being equal to the key value. If a record is found, RecordsetClone.NoMatch is False ... so Not False is True. That record is made active on the form by setting the Bookmark to be the same as the bookmark found in the RecordsetClone. You could also search the form recordset directly (instead of using RecordsetClone).

If a control name to set focus to is specified, that control is made active and the code exits.

Although this is defined to be a function that returns True or False, I rarely use the return value and normally treat this like a Sub (that doesn't return a value). By defining this to be a Function instead of a Sub, it can be called directly from the property sheet, which I rarely do, but its nice to have that option.

Parameters

pF is the passed Form reference
in code behind a form, this will usually be Me
psKeyFieldname is the name of the key field to look for in the recordset

Optional:

pCtrlName_SetFocus is the name of the control on the form to set focus to after a record is found.
pnKeyID is the value of the key field to find. If not passed, value in ActiveControl will be used.
pbClear: True (default) to clear value in ActiveControl after reading
pbChkIsLoaded: True to check if form is loaded. Default = False

Code

'*************** Code Start *****************************************************
' Purpose  : Find a Record on a form for a key field. Maybe call on unbound combo AfterUpdate.
' Author   : crystal (strive4peace)
' Return   : Boolean
' License  : below code
' Code List: www.MsAccessGurus.com/code.htm
'-------------------------------------------------------------------------------
'                              FindRecordN'-------------------------------------------------------------------------------
' 
Function FindRecordN(pF As Form _
   , psKeyFieldname As String _
   , Optional psCtrlName_SetFocus As String = "" _
   , Optional pnKeyID As Variant = 0 _
   , Optional pbClear As Boolean = True _
   , Optional pbChkIsLoaded As Boolean = False _
   ) As Boolean
'8-17-08 crystal (strive4peace) ... 160819, 181214

   'set up Error Handler
   On Error GoTo Proc_Err

   'initialize return value (that probably isn't used)
   FindRecordN = False
   
   With pF
      
      If pnKeyID = 0 Then
         'if nothing is picked in the active control, exit
         If IsNull(.ActiveControl) Then Exit Function
         'set value to look up by what is selected
         pnKeyID = .ActiveControl
         'clear the choice to find
         If pbClear Then .ActiveControl = Null
      End If
         
      'save current record if changes were made
      If .Dirty Then .Dirty = False
   
      'find the first value that matches
      .RecordsetClone.FindFirst psKeyFieldname _
         & "= " _
         & pnKeyID
   
      'if a matching record was found, then move to it
      If Not .RecordsetClone.NoMatch Then
         .Bookmark = .RecordsetClone.Bookmark
         DoEvents
      Else
         go to Proc_Exit
      End If
   
      If psCtrlName_SetFocus <> "" Then
         'this fails if controlname is not correctly specified
         .Controls(psCtrlName_SetFocus).SetFocus
      End If
   End With 'pF
   
   FindRecordN = True

Proc_Exit:
   On Error Resume Next
   Exit Function

Proc_Err:
   Resume Proc_Exit
End Function
'
' LICENSE
'   You may freely use and share this code 
'     provided this license notice and comment lines are not changed;
'     code may be modified provided you clearly note your changes.
'   You may not sell this code alone, or as part of a collection,
'     without my handwritten permission.
'   All ownership rights reserved. Use at your own risk.  
'   ~ crystal (strive4peace)  www.MsAccessGurus.com
'*************** Code End *******************************************************

VBA > Form > Find Record

Screen shot

Examples

Call FindRecordN (Me, "CID", "NoteCtc")

Call FindRecordN (Me, "CID", "NoteCtc", nCID)

Call FindRecordN (Me.subform_controlname.form, "OtherID")

Logic

Parameters

Optional:

Code

' FindRecordN

Share

Reference