Use the FindFirst On Tables A With Primary Key of Multiple Fields
In the case where the primary key is composed of multiple columns (For example, Last Name, First Name). the FindFirst command can use a concatenated set of fields to perform the search. In the sample below, the primary key consists of the GLType and the Group.
Program Code
Option Compare Database
Option Explicit
Public Function FindFirstKey()
Dim db As DAO.Database
Dim recIn As DAO.Recordset
Dim strSearchKey As String
Set db = CurrentDb()
Set recIn = db.OpenRecordset("qryGLAccountNumbers")
If recIn.EOF Then
recIn.Close
Set recIn = Nothing
Set db = Nothing
Exit Function
End If
strSearchKey = "TSO09"
recIn.FindFirst "[GLType]&[Group]=" & """" & strSearchKey & """"
If recIn.NoMatch Then
MsgBox ("No Match")
End If
recIn.Close
Set recIn = Nothing
Set db = Nothing
Exit Function
End If
End Function
Option Compare Database
Option Explicit
Private Sub cmdProceed_Click()
' *****************************************************************
' This Demonstrates The FindFirst After A Record Had Been Deleted
' ******************************************************************
Dim db As DAO.Database
Dim recIn As DAO.Recordset
Dim varSelectedClientID As Variant
Dim lngClientIDToBePurged As Long
Dim strClientNameToPurge As String
Dim lngResponse As Long
Dim qd As DAO.QueryDef
Dim strSQL As String
Dim strSQLSelect As String
Set db = CurrentDb()
strSQLSelect = "Select * From tblEmployees"
' ******************************************************************
' Loop Through Delete Selections
' ******************************************************************
If Me.lstEmployees.ItemsSelected.Count = 0 Then
MsgBox ("You Must Select At Least 1 Employee To Be Purged")
Exit Sub
End If
With Me.lstEmployees
For Each varSelectedClientID In .ItemsSelected
If Not IsNull(varSelectedClientID) Then
lngClientIDToBePurged = .ItemData(varSelectedClientID)
lngResponse = MsgBox("Are You Sure You Want To Purge " & _
Me.lstEmployees.Column(0, varSelectedClientID) & "?", vbYesNo)
If lngResponse = vbYes Then
On Error GoTo ErrorHandler
strSQL = "DELETE tblEmployees.ID, tblEmployees.EmployeeName FROM tblEmployees WHERE tblEmployees.ID = " & _
lngClientIDToBePurged
db.Execute strSQL, dbFailOnError
' ******************************************************************
' Make Sure It Was Deleted
' ******************************************************************
Set recIn = db.OpenRecordset(strSQLSelect)
If Not recIn.EOF Then
recIn.FindFirst "[ID]=" & lngClientIDToBePurged
If recIn.NoMatch Then
MsgBox ("No Match")
End If
recIn.Close
Set recIn = Nothing
End If
End If
End If
Next
End With
Exit Sub
' *****************************************************************************
' Handle Errors
' *****************************************************************************
ErrorHandler:
HandleError Err.Number, Err.Description, "Purge Employers", Me.Name
Err.Clear
Resume Next
End Sub
Private Sub HandleError(intErr As Long, strErrorDescription As String, strFunction As String, strObject As String)
On Error Resume Next
MsgBox ("Error #------------------ " & intErr & vbCrLf & "Error Description-------- " & strErrorDescription & vbCrLf & _
"Processing Function---- " & strFunction & vbCrLf & "Error in Access Object-- " & strObject)
End Sub