VB Access Duplicate record message (1 Viewer)

Britcan · Jun 10, 2008

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 · Jun 10, 2008

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.

Code:

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

Britcan · Jun 10, 2008

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

Alc · Jun 10, 2008

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).

Code:

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 · Jun 12, 2008

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 · Jun 12, 2008

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

Code:

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

with

Code:

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

Britcan · Jun 12, 2008

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 · Jun 12, 2008

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 · Jun 24, 2008

Duplicate Record Message

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

Alc said:
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

Code:

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

with

Code:

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

Alc · Jun 24, 2008

poucedeleon said:
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 · Jul 16, 2008

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: ="

Code:

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 · Jul 16, 2008

I got it working by using this:

Code:

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 · Dec 4, 2008

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 · Dec 4, 2008

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 · Dec 4, 2008

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 · Dec 4, 2008

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

rick roberts · Dec 5, 2008

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 · Dec 5, 2008

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 · Dec 8, 2008

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?

VB Access Duplicate record message (1 Viewer)

New member

Registered User.

New member

Registered User.

New member

Registered User.

New member

Registered User.

Registered User.

Registered User.

Que?

Que?

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Similar threads

Users who are viewing this thread