Recordset.findfirst says there's a match...but it lies :)

[jwill]

Hey everyone,



I have a sub procedure that when the user updates the field on the form then the procedure looks for a matching record based on 3 fields (code_id, credited_dsu_id, and ap_date). If there is a match then edit a 4th column qty_of_prod. If there is no match add a new row.

When it finds the match it does a good job of updating the row, but the problem is its saying that its finding a match when I'm positive there is not match on all 3 criterias (there's only one row in the table). Here is the code:

Private Sub qemc_AfterUpdate()
On Error GoTo Err_qemc_afterupdate

Dim db As dao.Database
Dim rs As dao.Recordset
Dim rs_clone As dao.Recordset
Dim var_dsu
Dim var_ap_date
Dim var_first_search As String

Set db = CurrentDb
Set rs = db.OpenRecordset("assoc_prod_qry")
Set rs_clone = rs.Clone
var_dsu = Me.cred_dsu_txt
var_ap_date = Me.ap_date_txt
var_first_search = "code_id = '" & Me.ActiveControl.Name & "' and credited_dsu_id = '" & var_dsu & "' and ap_date = #" & Me.ap_date_txt & "#"


With rs_clone
        
    
    .FindFirst var_first_search
   
    
    If (.BOF And .EOF) Then
        'If criteria not add new record
        rs.AddNew
        If IsNull(Me.cred_dsu_txt) Then 'this can be removed after the default dsu is set on the form
            MsgBox "Please enter a DSU.", vbExclamation
            rs.Close
            db.Close
            Set rs = Nothing
            Exit Sub
        Else
            rs!credited_dsu_id = Me.cred_dsu_txt.Value
        End If
        rs!code_id = Me.ActiveControl.Name
        rs!ap_date = Me.ap_date_txt
        rs!qty_of_prod = Me.qemc.Value
        rs!assoc_entered = "jaison"
        rs!entry_timestamp = Now()
        rs.Update
    Else
        'If found, update the qty of the unit of production
        rs.Edit
        rs!qty_of_prod = Me.qemc.Value
        rs.Update
      End If

End With
    
    

rs_clone.Close
rs.Close
db.Close
Set rs = Nothing
Set rs_clone = Nothing


Exit_qemc_afterupdate:
    Exit Sub

Err_qemc_afterupdate:
     MsgBox "Error number " & Err.Number & ": " & Err.Description
    Resume Exit_qemc_afterupdate
End Sub

Can someone tell me why its saying that its a match, when there is not?

[RuralGuy]

If (.BOF And .EOF) Then is true only if there are NO records in the Recordset. I tend to use .NoMatch but maybe you want: If Not .EOF Then

[ItsMe]

I am with Rural Guy. I use .Nomatch. I do not see how BOF or EOF is of any benefit in this circumstance.

[jwill]

still new to recordsets. .nomatch worked perfectly, thanks guys.

[RuralGuy]

You might also be advised that RecordsetClone is a DAO property and Recordset.Clone is an ADO method.