Duplicate Inputs

[csdrex87]

So I'm trying to put a check on this table to prevent one user for putting more than one input per date. The problem is i cant do this with a primary key cause multiple users use the same table instead of creating the same table for hundreds of users.

I attempted to look up some sort of Dlookup function to check if the date and the employee ID # were the same as any of the other results but it doesn't seem to be working. If anyone has any suggestions thatd be greatly appreciated.

As of right now here is my code:
Private Sub Command11_Click()

Dim iEmpId As Long

iEmpId = Nz(DLookup("Emp_ID", "tblEmployeeDaily", "Emp_ID = '" & Me.Emp_ID & "' And " & "DailyDate=#'" & Me.DailyDate & "'"), 0)

If iEmpId <> 0 Then
MsgBox "An entry for this date already exists. " & vbCrLf & _
vbCrLf & "Please enter a different date.", _
vbCritical, "Duplicate Employee"
Cancel = True

Else:
DoCmd.Save acForm, "EmployeeDaily"
End If

End Sub

 

[pbaldy]

You've got an extra and wrong characters in there, plus too many concatenations. Try

Nz(DLookup("Emp_ID", "tblEmployeeDaily", "Emp_ID = '" & Me.Emp_ID & "' And DailyDate=#" & Me.DailyDate & "#"), 0)

 

[jzwp22]

If the Emp_ID field is numeric, you do not need the single quotes in the DLookup. If DailyDate is a date/time datatype, it must be bounded by # signs and not single quotes.

Here is the corrected expression:

iEmpId = Nz(DLookup("Emp_ID", "tblEmployeeDaily", "Emp_ID = " & Me.Emp_ID & " And DailyDate=#" & Me.DailyDate & "#"), 0)

Since you are not really using the employee ID obtained from the Dlookup, you can actually go with the DCount() function and then you would not need the NZ() function.

iEmpID= DCount("Emp_ID", "tblEmployeeDaily", "Emp_ID = " & Me.Emp_ID & " And DailyDate=#" & Me.DailyDate & "#")

 

[csdrex87]

Hmm so i tried to put the code in and im not sure if its not necessarily comparing it correctly or not but it seems that every time i put in any sort of entry whether it is a duplicate or not it automatically rolls over to the msgbox i put in. It might be because of the fact that im not totally sure exactly what iEmpID should be if there was nothing that Dcount found.

This is what my code looks like now:

iEmpID = DCount("Emp_ID", "tblEmployeeDaily", "Emp_ID = " & Me.Emp_ID & " And DailyDate=#" & Me.DailyDate & "#")

If IsNull(iEmpID) = True Then
DoCmd.Save acForm, "EmployeeDaily"

Else:
If MsgBox("Data is already in the database for this date!", vbOKOnly, "Unable to Save") = vbOK Then
Cancel = True

End If

End If

 

[jzwp22]

For each record that matches the criteria, DCount() counts it (DCount()>0); if no record is found then DCount()=0

Dim iEmpId As Long

iEmpID= DCount("Emp_ID", "tblEmployeeDaily", "Emp_ID = " & Me.Emp_ID & " And DailyDate=#" & Me.DailyDate & "#") 


If iEmpId <> 0 Then
   MsgBox "An entry for this date already exists. " & vbCrLf & _
   vbCrLf & "Please enter a different date.", _
   vbCritical, "Duplicate Employee"
   Cancel = True
End If

End Sub

The next question is when you are executing this command. From your first post, it appears that you are using a command button. If the form is bound to the table then you could probably execute the code in the before insert event.

 

[csdrex87]

I was originally creating it on a command button, but after thinking it through i decided to throw it in the before update for that exact reason that it is a bound form.

Edit: Although I can't do it on the before insert portion because it compares 2 fields on the form to 2 fields in the table. I might be able to figure some way around this but for now i just want to get the check working.

 

[jzwp22]

Sorry, the before update event of the form would be the correct event, not the before insert event.

 

[csdrex87]

So it appears as though my code still isnt working for this duplicate check. What I am getting is "Runtime error 2467: The expression you entered refers to an object that is closed or does not exist."

My table is called tblEmployeeDaily with fields...
DailyDate (date)
Emp_ID (number)
and 5 more data fields all with a number that i never refer to in the code.

My code for this is currently:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim iEmpId As Long

iEmpId = DCount("Emp_ID", "tblEmployeeDaily", "Emp_ID = " & Me.Emp_ID & " And DailyDate=#" & Me.DailyDate & "#")

If iEmpId <> 0 Then
MsgBox "An entry for this date already exists. " & vbCrLf & _
vbCrLf & "Please enter a different date.", _
vbCritical, "Duplicate Employee"
Cancel = True
End If

End Sub

 

[jzwp22]

Unfortunately, the error codes don't tell you much. Is it possible to post your database with any sensitive data removed?

 

[csdrex87]

Actually that problem i fixed. I didnt realise that i still had a small piece of code in the beforeInsert event. So we're all good

 

[jzwp22]

Glad you got it worked out. Good luck with your project.