Prevent duplicate records form being added to table

[jmmunoz2141]

So I have a form where users enter their ID number in a text box (Text32) and click a command button (Command138) to submit their record into a table (tbl_Report meals). To give the everyday use of this function, this database is used to track meals eaten by employees and I am trying to prevent an employee from eating the same meal in one day.



The fields in the table are:

• Meal ID
• User ID
• Fname
• Lname
• SSO
• Department
• Production
• Meal
• Record Added Date
• Record Added Time

The way this record is entered into the table is by an append query that is triggered to run in the code of the cmd button. What I need to accomplish is to prevent duplicates in the table based on three fields in the table: UserID, Meal, Record Added Date and for the form to acknowledge this and perform a specific set of commands. What I've done so far is to add an index to the table itself titled UniqueKey with the three fields mentioned before and set it to be unique.

With this in place, I've noticed that whenever I try to enter a duplicate value I get a message that "Microsoft Access can't append all the records in the append query due to 1 key violation" when the append query tries to run. Now when I click No on this prompt, my error handler takes over and makes my form do exactly what I need it to do. The issue is that I can't have that access prompt appear every time someone enters their duplicate value. Here is the code I currently have in place for my cmd button.

Private Sub Command138_Click()
On Error GoTo Error_Stopduplicates
Dim intUP As Integer
Dim booAdd As Boolean
intUP = Nz(DLookup("[Permission]", "tbl_user", "[User ID] = " & Forms![frm_cafe entry form]!Text32), 0)
Select Case Me.Text16
    Case "Breakfast"
        If intUP = 1 Or intUP = 4 Or intUP = 5 Or intUP = 7 Then booAdd = True
    Case "Lunch"
        If intUP = 2 Or intUP = 4 Or intUP = 6 Or intUP = 7 Then booAdd = True
    Case "Dinner"
        If intUP = 3 Or intUP = 5 Or intUP = 6 Or intUP = 7 Then booAdd = True
End Select
If booAdd = True Then
    'do this
    DoCmd.ApplyFilter "", "[User ID] Like ""*"" & [Forms]![frm_cafe entry form]![Text32] & ""*""", ""
    DoCmd.OpenQuery "qry_user add to report", acViewNormal, acEdit
    DoCmd.GoToControl "Text32"
    Me.Text32.Value = "1234"
    Me.txtmealaccess.Value = "Enjoy your meal!"
    Me.txtmealaccess.BackColor = RGB(85, 206, 2)
    Me.txtmealaccess.ForeColor = vbBlack
Else
    'do this
    DoCmd.ApplyFilter "", "[User ID] Like ""*"" & [Forms]![frm_cafe entry form]![Text32] & ""*""", ""
    DoCmd.Beep
    DoCmd.GoToControl "Text32"
    Me.Text32.Value = "1234"
    Me.txtmealaccess.Value = "You do not have rights to eat this meal."
    Me.txtmealaccess.BackColor = RGB(186, 20, 25)
    Me.txtmealaccess.ForeColor = vbWhite
End If
Exit_Command138_Click:
Exit Sub
Error_Stopduplicates:
    DoCmd.ApplyFilter "", "[User ID] Like ""*"" & [Forms]![frm_cafe entry form]![Text32] & ""*""", ""
    DoCmd.Beep
    DoCmd.GoToControl "Text32"
    Me.Text32.Value = "1234"
    Me.txtmealaccess.Value = "You have already eaten this meal today."
    Me.txtmealaccess.BackColor = RGB(186, 20, 25)
    Me.txtmealaccess.ForeColor = vbWhite
Resume Exit_Command138_Click:

Is there any way to automatically say No to the access prompt that appears and let the error handler to take care of the rest?

[June7]

Prevent the error. Do a DLookup with those 3 values as search parameters.

[jmmunoz2141]

Preventing the error sounds like a much better solution. Unfortunately I'm not sure how to write this DLookup to search for these 3 values. Is there any chance somebody can help me write this? My top post is rather obtuse so if any more information is needed I'd be more than happy to oblige.

[orange]

Here is a link to the proper syntax and some samples of DLookup.

You could also use a DCount() function. If Dcount(proper syntax and values) >0 Then you know it already exists.
DCount is also on techonthenet.
God luck.