Check if exists

[D Willett]

Back again guys.

I'm putting together some code to check if a record exists, if it does then go yo that record, if not go to a new record. I'm having errors on the rs.FindFirst line which says: txtJobID is not recognised. The code is implemented in the After Update event of txtJobID:

Code: Select all

Private Sub CheckAndGoToRecord()
    Dim rs As DAO.Recordset
    Dim searchCriteria As String
    
    ' Get the search criteria (e.g., the value of the field you want to check)
    searchCriteria = Me.txtJobID.value
    
    ' Set the recordset to the form's underlying table or query
    Set rs = Me.RecordsetClone
    
    ' Search for the record
    rs.FindFirst "me.txtJobID.value = '" & searchCriteria & "'"
    
    ' Check if the record was found
    If Not rs.NoMatch Then
        ' If the record exists, go to that record
        Me.Bookmark = rs.Bookmark
    Else
        ' If the record does not exist, move to a new record
        Me.Recordset.AddNew
    End If
    
    ' Clean up the recordset object
    rs.Close
    Set rs = Nothing
End Sub


Private Sub txtJobID_AfterUpdate()
CheckAndGoToRecord
End Sub

[HansV]

What is the name of the field you're searching? If it is JobID, change

Code: Select all

    rs.FindFirst "me.txtJobID.value = '" & searchCriteria & "'"

to

Code: Select all

    rs.FindFirst "JobID = '" & searchCriteria & "'"

[D Willett]

Spot on Hans, all working fine now.

Many Thanks