Prevent Duplicate

[Leventcos21]

Hi,

Upon entry of a new record I want to prevent duplicates. I relize that I can set an Keys on the table, however that would change the DB structure. Iam trying to prevent duplicates in the VBA but having some issues.

I am getting an error 91 on the Set rst. Will this work below? Thanks

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim rst As DAO.Recordset
    Dim db As DAO.Database
If Me.NewRecord Then
    Set rst = db.OpenRecordset("SELECT SOP, EffectiveDate " & _
                "FROM tblSop " & _
                "WHERE SOP = " & Me.txtNumber & " AND EffectiveDate = me.txtEffectiveDate")
                
    If rst.RecordCount = True Then
        MsgBox " duplicates", , "dups"
    
        rst.MoveNext
        Do Until rst.EOF
            rst.MoveNext
        Loop
    End If
    rst.Close
    Set rst = Nothing
End If

 

[PHV]

You must set db before rst:
Set db = CurrentDb

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JoeAtWork]

I see a few additional problems. Here's how I would change it:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim rst As DAO.Recordset
    Dim db As DAO.Database
If Me.NewRecord Then
    [COLOR=red]
    Set db = CurrentDB
    Set rst = db.OpenRecordset("SELECT SOP, EffectiveDate " & _
                "FROM tblSop " & _
                "WHERE SOP = " & Me.txtNumber & " AND EffectiveDate = #" & Format(me.txtEffectiveDate, "mm/dd/yyyy") & "#")
[/color]
    [COLOR=green]'I find RecordCount to often be inaccurate, I use BOF and EOF properties to be certain if no records were returned[/color]
    [COLOR=red]
    If Not(rst.BOF And rst.EOF) Then[/color]
        MsgBox " duplicates", , "dups"
    
        [COLOR=green]'I removed the loop, since it wasn't doing anything[/color]
    End If
    rst.Close
    Set rst = Nothing
End If