Prevent duplicate records from a form (1 Viewer)

[rdowney79]

I tried finding previous posts regarding this issue, but I couldn't find something that worked for me. Im trying to prevent duplicate records based on a PO # (RA_PO_Nbr), entered from a form. I'm trying to throw a 3022 error when the user attempts to enter a record that already exists, but I dont necessarily have to do it this way. Any help would be greatly appreciated.

Private Sub cmdSubmit_Click()
On Error GoTo cmdSubmit_Click_Error
Dim db As Database
Dim rst As DAO.Recordset
Dim strSQL As String
 
strSQL = "CompanyPOTable"
Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)
 
Me.txtPOAmt.SetFocus
If Me.txtPOAmt.Text = "" Then
    MsgBox "You must enter a PO amount!", vbInformation, "Invalid PO Amount"
Exit Sub
End If
 
With rst
    .AddNew
        ![RA_PO_Nbr] = [Forms]![frmNewPO]![txtPONbr]
        ![RA_Surcharge] = [Forms]![frmNewPO]![txtCompany_Surcharge]
        ![RA_Net_Merch] = [Forms]![frmNewPO]![txtCompany_Net_Merch]
        ![RA_PO_Amt] = [Forms]![frmNewPO]![txtPOAmt]
        ![OriginalEntryDate] = [Forms]![frmNewPO]![txtOrigDate]
        ![PO_Comments] = [Forms]![frmNewPO]![txtPOComments]
    .Update
 
End With
[Forms]![frmNewPO]![txtPONbr] = ""
[Forms]![frmNewPO]![txtCompany_Surcharge] = ""
[Forms]![frmNewPO]![txtCompany_Net_Merch] = ""
[Forms]![frmNewPO]![txtPOAmt] = ""
[Forms]![frmNewPO]![txtPOComments] = ""
[Forms]![frmNewPO]![txtPONbr].SetFocus
 
cmdSubmit_Click_Error:
    If Err.Number = 3022 Then
        MsgBox "You have entered a PO Number that already exists in the table!"
    Else
        If Err.Number = 3058 Then
            MsgBox "You must enter a PO number!"
        End If
    End If
End Sub

Thanks,
Ray

 

[pr2-eugin]

rdowney79, Why have you not used something like DCount/DLookup to check for existence?

Private Sub cmdSubmit_Click()
On Error GoTo cmdSubmit_Click_Error
    Dim db As Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
 
    If Len(Me.txtPOAmt & vbNullString) = 0 Then
        MsgBox "You must enter a PO amount!", vbInformation, "Invalid PO Amount"
        Exit Sub
    [COLOR=Blue]Else
        [/COLOR][COLOR=Blue]If DCount("*", "[/COLOR][COLOR=Red][B]yorTableNameHERE[/B][/COLOR][COLOR=Blue]", "[RA_PO_Nbr] = " & [Forms]![frmNewPO]![txtPONbr]) <> 0[/COLOR][COLOR=Blue] Then[/COLOR]
            [COLOR=Green]'Change it to the following if RA_PO_Nbr is [B]String [/B]type
            ' [/COLOR][COLOR=Green]If DCount("*", "[B]yorTableNameHERE[/B]", "[RA_PO_Nbr] = '" & [Forms]![frmNewPO]![txtPONbr] & "'") <> 0 Then[/COLOR]
            [COLOR=Blue]MsgBox "You have entered a PO Number that already exists in the table!"
            Exit Sub
        End If[/COLOR]
    End If
 
    strSQL = "CompanyPOTable"
    Set db = CurrentDb()
    Set rst = db.OpenRecordset(strSQL)
    
    With rst
        .AddNew
        ![RA_PO_Nbr] = [Forms]![frmNewPO]![txtPONbr]
        ![RA_Surcharge] = [Forms]![frmNewPO]![txtCompany_Surcharge]
        ![RA_Net_Merch] = [Forms]![frmNewPO]![txtCompany_Net_Merch]
        ![RA_PO_Amt] = [Forms]![frmNewPO]![txtPOAmt]
        ![OriginalEntryDate] = [Forms]![frmNewPO]![txtOrigDate]
        ![PO_Comments] = [Forms]![frmNewPO]![txtPOComments]
        .Update
    End With
    
    [Forms]![frmNewPO]![txtPONbr] = ""
    [Forms]![frmNewPO]![txtCompany_Surcharge] = ""
    [Forms]![frmNewPO]![txtCompany_Net_Merch] = ""
    [Forms]![frmNewPO]![txtPOAmt] = ""
    [Forms]![frmNewPO]![txtPOComments] = ""
    [Forms]![frmNewPO]![txtPONbr].SetFocus

[COLOR=Blue]    Set db = Nothing
    Set rst = Nothing[/COLOR]

cmdSubmit_Click_Exit:
    Exit Sub
cmdSubmit_Click_Error:
    MsgBox "Error (" & Err.Number & ") - " & Err.Description, vbCritical, "There is an Error."
    Resume cmdSubmit_Click_Exit
End Sub

 

[rdowney79]

rdowney79, Why have you not used something like DCount/DLookup to check for existence?

Private Sub cmdSubmit_Click()
On Error GoTo cmdSubmit_Click_Error
    Dim db As Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
 
    If Len(Me.txtPOAmt & vbNullString) = 0 Then
        MsgBox "You must enter a PO amount!", vbInformation, "Invalid PO Amount"
        Exit Sub
    [COLOR=blue]Else[/COLOR]
[COLOR=blue]   If DCount("*", "", "[RA_PO_Nbr] = '" & [Forms]![frmNewPO]![txtPONbr] & "'") <> 0 Then[/COLOR]
            [COLOR=green]'Change it to the following if RA_PO_Nbr is [B]Number[/B] type[/COLOR]
[COLOR=green]       'If DCount("*", "", "[RA_PO_Nbr] = " & [Forms]![frmNewPO]![txtPONbr]) <> 0 [/COLOR]Then
            [COLOR=blue]MsgBox "You have entered a PO Number that already exists in the table!"[/COLOR]
[COLOR=blue]       Exit Sub[/COLOR]
[COLOR=blue]   End If[/COLOR]
    End If
 
    strSQL = "CompanyPOTable"
    Set db = CurrentDb()
    Set rst = db.OpenRecordset(strSQL)
 
    With rst
        .AddNew
        ![RA_PO_Nbr] = [Forms]![frmNewPO]![txtPONbr]
        ![RA_Surcharge] = [Forms]![frmNewPO]![txtCompany_Surcharge]
        ![RA_Net_Merch] = [Forms]![frmNewPO]![txtCompany_Net_Merch]
        ![RA_PO_Amt] = [Forms]![frmNewPO]![txtPOAmt]
        ![OriginalEntryDate] = [Forms]![frmNewPO]![txtOrigDate]
        ![PO_Comments] = [Forms]![frmNewPO]![txtPOComments]
        .Update
    End With
 
    [Forms]![frmNewPO]![txtPONbr] = ""
    [Forms]![frmNewPO]![txtCompany_Surcharge] = ""
    [Forms]![frmNewPO]![txtCompany_Net_Merch] = ""
    [Forms]![frmNewPO]![txtPOAmt] = ""
    [Forms]![frmNewPO]![txtPOComments] = ""
    [Forms]![frmNewPO]![txtPONbr].SetFocus
 
[COLOR=blue]Set db = Nothing[/COLOR]
[COLOR=blue]Set rst = Nothing[/COLOR]
 
cmdSubmit_Click_Exit:
    Exit Sub
cmdSubmit_Click_Error:
    MsgBox "Error (" & Err.Number & ") - " & Err.Description, vbCritical, "There is an Error."
    Resume cmdSubmit_Click_Exit
End Sub

Pr, thank you for the quick response. Im very new at Access, so I'm learning on the fly. I tried your response, but got an 'Error (2428) - You entered an invalid argument in domain aggregate function.' which I clueless about. Also, the data type for RA_PO_Nbr is currency.

 

[pr2-eugin]

Okay, you should replace yorTableNameHERE with the actual name of your table.. Since it is currency you need to treat it as a Double value (Number) so use the Edited Code in Post#2..

 

[ButtonMoon]

Enforcing the key in the database is more important. Put a uniqueness constraint (AKA key constraint / "unique index") on RA_PO_Nbr if there isn't one already. Don't rely only on procedural code in your forms to keep out duplicates - it won't.

 

[rdowney79]

Okay, you should replace yorTableNameHERE with the actual name of your table.. Since it is currency you need to treat it as a Double value (Number) so use the Edited Code in Post#2..

Issue Solved. thank you Pr, you were a tremendous help.

Button - that seems to be much a much easier route than using vba to throw error. What are the drawbacks to simply electing a column to be 'Unique' and 'Required' over using VBA, as I have in this instance?

Thanks,
Ray

 

[David R]

The biggest drawback is that Access checks for uniqueness when you SAVE the record, so you could do a bunch of data entry before that and end up learning that you can't save... however in your case, since the solution is probably just to increment the PO#, you've lost very little.

You could also combine the solutions, checking the PO# at the beginning but using a uniqueness constraint to catch anything that falls through the cracks (99% of the time, DLookup will catch it unless you have a LOT of simultaneous users).

 

[pr2-eugin]

Glad to help. I agree with Button, specially when you are dealing with Double values.. As Rounding will make the function fall apart..

The only drawback AFAIK, if the column set to UNIQUE is the Error message is generic from Access and looks a bit messy.. Would not allow you to save the record unless you enter a proper valid value, if you fail to then the record will not be saved..