VB Access Duplicate record message

[Britcan]

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

 

[Alc]

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("[I]fieldname", "tablename", "fieldname = '" [/I]& Me!Receipt & [I]"'"[/I]) > 0 Then
     Msgbox "Receipt number already exists"
     Me!Receipts = ""
End If

 

[Britcan]

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

 

[Alc]

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("[I]fieldname", "tablename", "fieldname = '" [/I]& Me!Receipt & [I]"'"[/I]) > 0 Then
     Msgbox "Receipt number already exists"
     Me!Receipts = ""
     Me!Receipts.SetFocus
End If

 

[Britcan]

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

 

[Alc]

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

 

[Britcan]

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.

 

[ASherbuck]

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.

 

[poucedeleon]

Duplicate Record Message

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

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

 

[Alc]

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

Every now and then, you get to help someone by accident.

 

[RossWindows]

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];"
[COLOR=red]Set rs.recordsource(Sqry)[/COLOR]
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?

 

[RossWindows]

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

 

[rick roberts]

message to Alc -- im using your code for my program -- it holds information on inported emails- i.e. -- subject, to address, from address and body -all in seperate textboxes and i use a button to check if there are any duplicates --- this works fine for all textboxes except the body box -- it gives a runtime error 3075 - missing operator but i really cant find anything wrong in it - -since ive actually copied and pasted from the other working codes and simply inserted the correct textbox name - -could this error have something to do with the size of the box or the data contained in it?

 

[Alc]

Glad it's come in useful for someone else.

Could you post the code?
If I had a pound for every time I've used 'the exact code' from elsewhere, but accidentally deleted one or more characters, I wouldn't need to be sat here now.

 

[rick roberts]

Private Sub cmdFilterBody_Click()
If DCount("Body", "tblEmail", "Body = '" & Me!Body & "'") > 1 Then
MsgBox "Duplicated", vbOKOnly
End If
End Sub



ive had another lookat this -- seems to work ok if the message is short

 

[Alc]

Try declaring the datatype as Memo instead of String.
If the length is the issue, that should fix it.

 

[rick roberts]

im still stuck with this -- it seems to work for some records and not others and ive changed from text to memo in the table in case it was a field size problem - i probably should have said that the records are imported from an excel file and appended to an exisiting table

 

[rick roberts]

im still having trouble with this -it seems to work on some records and not others (have a feeling its to do with the content)- ive changed from text to memo in case it was a field size problem - i guess i should have said - -the table is imported from an excel file - also Why do i find that the format for all trextboxes is set to "@" in the table

 

[rick roberts]

do you mean declare it in the table --which i already did or in the VB code - i havent declared anything in there cos the code was so simple -- and cant find a memo declaration anyway?