No duplicates on subform (1 Viewer)

[jdcallejas]

My database is really something simple. I just need to scan personal that come through a buffet line. That's it.

This is for employees that come get lunch thought the buffet line, this is paid by the company. So I have everything working, is a three table database, tblEmployees, tblBuffetDetails, tblBuffet. Think of it like tblProducts as tblEmployees, tblinvoiceDetails as tblBuffetDetails and tblInvoices as tblBuffet.
The system is working great, it scans and adds new employee automatically if it's not on the tblEmployees. I have one issue... Duplicates...
I want to make it so that if an employee tries to go through the buffet line for a second time with same barcode, when scanned it will show as a duplicate and have a msg box show that this card already been scanned today.

How can I make that happened..

Thank you

 

[Ranman256]

Scan the code to an unbound text box,
Which runs a lookup to see if it exists,: dlookup(...)
If so, show msg,
If not ,add to the table w append query.

 

[arnelgp]

as suggested use Dlookup/DCount.
if you have date field on the tblBuffetDetails, then include it
in the condition of the DCount (i'll use this since it doesn't return a Null value), eg:

If DCount("1", "tblBuffetDetails", "EmpID = " & Me.ScannedIDTextbox & " And DateField = #" & Format(Date(), "mm/dd/yyyy") & "#") > 0 Then
' this employee already has his buffet
Else
'not yet taken the buffet
'add to tblBuffetDetails
End If

 

[jdraw]

Another option is to make the ScannedId and BuffetDate a composite unique index in your BuffetDetails table. When you attempt to save a duplicate record, Access will sense the issue and
report a trappable error 3022 - which you can process/give message.

 

[jdcallejas]

as suggested use Dlookup/DCount.
if you have date field on the tblBuffetDetails, then include it
in the condition of the DCount (i'll use this since it doesn't return a Null value), eg:

If DCount("1", "tblBuffetDetails", "EmpID = " & Me.ScannedIDTextbox & " And DateField = #" & Format(Date(), "mm/dd/yyyy") & "#") > 0 Then
' this employee already has his buffet
Else
'not yet taken the buffet
'add to tblBuffetDetails
End If

Hello, you have giving me this code to add EmpID on the Not in List event to add the EmpID if it was a new EmpID. This is working great.

Private Sub EmpID_NotInList(NewData As String, Response As Integer)
CurrentDb.Execute "insert into [tblEmployees] ([EmpBarcode]) select '" & NewData & "'"
Response = acDataErrAdded
End Sub


I don't have a datefield on the details table. So I guess I will take out the datefield out of the new code.

Where do I insert the Dcount code you just gave me?

 

[arnelgp]

which table are you adding the employee taking the buffet?
I think you need the date field in that table, since you cannot identify,
the employee if he has taken it Today.