I have just started to program in Access 2003 and want to use some of the VB Tools.

I have a field where the user enters a number, say 1234567-8.
I want the database to check that, the number has not been previously entered.

If it has, I want the program to return a message to the user saying ”Duplicate Voucher – Please check”.

I want to code the On Exit command of the field property. Can you suggest the correct code to use?

The field name is receipt.

Thanks

You could use DCount() to check if there's already at least one record with that number in whatever the table is called. If so, clear it and tell the user e.g.

If DCount("fieldname", "tablename", "fieldname = '" & Me!Receipt & "'") > 0 Then
Msgbox "Receipt number already exists"
Me!Receipts = ""
End If

Many thanks for the quick reply ALC. I will try it out.

Good luck.

I forgot to add that you migth want to set the focus back to the field, if it gets cleared (to avoid users leaving it blank).


If DCount("fieldname", "tablename", "fieldname = '" & Me!Receipt & "'") > 0 Then
Msgbox "Receipt number already exists"
Me!Receipts = ""
Me!Receipts.SetFocus
End If

Hi Alc,

I have tried the code you suggested.

Private Sub Receipt_BeforeUpdate(Cancel As Integer)
If DCount("Receipt", "Receipts", "Receipt = '" & Me!Receipt & "'") > 0 Then
MsgBox "Receipt number already exists"
Me!Receipt = ""
Me!Receipt.SetFocus
End If
End Sub

However, I keep getting a Run Time Error 3464

Data type mismatch in Criteria Expression.

Any suggestions on how to get around this? Am I using the correct property. I also tried this code in Exit but no luck!

Thanks

Is the receipt number a number?
What I gave you assumed it was a string (as it had a '-' in the middle).

Try replacing this line

If DCount("Receipt", "Receipts", "Receipt = '" & Me!Receipt & "'") > 0 Then

with

If DCount("Receipt", "Receipts", "Receipt = " & Me!Receipt) > 0 Then

The number entered is in this format. 1234567-8. I set an input mask to ensure that users enter only in this format.

!9999999"-"9

And Indexed yes no duplicates.

I have tried the code. It now returns the message. However, the message appears even when there is no duplicate. It is also not allowing me to move to the next record.

Also, can you advise which event I should place the code? Thanks.

I could be wrong but let me give you this and see if it works. This would go either in the OnClick event for a button or on the after update event for a text control if you just want them to type in the number or scan the barcode and have it hit enter.

Dim rs as DAO.Recordset
Dim Sqry As String

Sqry = "SELECT field FROM Table WHERE Field=[Forms]![FrmName]![TextBox];"
Set rs.recordsource(sqry)

If rs.recordcount > 0 Then
MsgBox "Duplicate Value Exists"
Else
Record Saving Code
End If



Like I said I'm not 100% on that but I used it or something similiar to check all my stuff for duplicates.

Thanks Alc
I was having the same problem, but was able to fix it with the code you posted.:D

Is the receipt number a number?
What I gave you assumed it was a string (as it had a '-' in the middle).

Try replacing this line

If DCount("Receipt", "Receipts", "Receipt = '" & Me!Receipt & "'") > 0 Then
with

If DCount("Receipt", "Receipts", "Receipt = " & Me!Receipt) > 0 Then

Thanks Alc
I was having the same problem, but was able to fix it with the code you posted.:D
Every now and then, you get to help someone by accident. :D

I am trying to implement a feature that checks for duplicates as well.
I have a combo box to select the employee (strCsrFullname)
and I want the form to check if a record has already been created for that employee on the same day. Here is what I have so far, but I get an error on the Set line. "Expected: ="

Private Sub strCsr_AfterUpdate()
Dim rs As DAO.Recordset
Dim Sqry As String
Sqry = "SELECT strCsrFullName FROM TblAssistCalls WHERE strCsrFullName=[Forms]![tblCallIns]![strCsr] AND dtmCallDate=[Forms]![tblCallIns]![dtmCallDate];"
Set rs.recordsource(Sqry)
If rs.RecordCount > 0 Then
MsgBox "Duplicate Value Exists"
End If
End Sub

I have also tried using "Set rs.recordsource = Sqry" but I get the compile error "Method or data member not found" and it highlights "rs.recordsource"
Am I missing a reference or something?

I got it working by using this:

If DCount("[strCsrFullName]", "tblCallIns", "[strCsrFullName] ='" & Me.[strCsr] & "' AND [dtmCallDate] = #" & Me.[dtmCallDate] & "#") > 0 Then
'Message box warning of duplication
If MsgBox("A record has already been created for that employee today." _
& vbCr & vbCr & "Click 'OK' to create another record or" _
& vbCr & vbCr & "'CANCEL' to avoid a duplicate.", vbOKCancel _
, "Duplicate Warning") = vbCancel Then
Me.Undo
Else
Me.strSupervisor.RowSource = "SELECT strSupervisor FROM tblEmployees WHERE strCsrFullName = strCsr"
Me.strSupervisor = Me.strSupervisor.ItemData(0)
End If
End If