Question Custom message when adding duplicate values

[ppataki]

Dear All,

I have a table with a compound key
I have a form with a button that adds new records each time pressed
Code:

DoCmd.GoToRecord , , acNewRec
Me!sfr_LineManID.Form!ParticipantID = Me.user
Me!sfr_LineManID.Form!StatusAlloc = "Pending approval"

If I want to add duplicate values data is added then when I want to move to the next record I get the standard Access error message about index, etc so I have to hit Esc to revert the new record
My question is: how can I set up the code so that if duplicate is added I get a custom msgbox and the new record is not added? (without pressing Esc)
Many thanks

 

[CBrighton]

There's likely a way to do it through error checking, but if we are talking a single field (i.e. some kind of reference number or account number) I would use a recordset in VBA to quickly check for existing records.

e.g.:

dim db as database
set db = currentdb
dim rst as recordset
set rst = db.openrecordset("SELECT FieldName FROM TableName WHERE FieldName = '" & NameOfFieldOnForm & "'")
 
If not rst.eof then
     MsgBox "Insert message text here"
     Exit Sub
End if

 

[ppataki]

Unfortunately I have 2 fields to check as the table has a compound key
Basically I dont want to check them as primary key is set so Access checks them for me, I just want to display an error message when entering duplicates with the "Accmd Newrec" method
Do you have any suggestions please?
Thank you

 

[CBrighton]

I'm largely self-taught in Access, for this kind of thing I favour recordsets over error handling purely because that's what I've always used in the past.

What version of Access are you using? Error handling should do what you want but the error number may vary depending on your version of Access.

 

[ppataki]

I use Access 2007

 

[CBrighton]

A quick google search for "Access 2007 error handling codes" produced this:
3022 - The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

Therefore try something like:

On Error GoTo Err_Trapping
 
**standard code**
 
 
Err_Trapping:
If Err.Number = 3022 Then 'Duplicate value in unique indexed field
    msgbox "Error message text"
    Exit Sub
End if

:edit: I don't know what you actually want to do after the messagebox is displayed, I have assumed it is just stopping the process so the user can correct the data but you could highlight the relevant field (change the background colour to red for example) and more the cursor focus to it so it's more user friendly, etc.

 

[ppataki]

Thank you very much it works fine!!

 

[JANR]

Instead of letting the error happen and trapp it, you could just check the table for dublicates using the Dcount function and cancel the event if you find a duplicate.

You'll need to use the forms Before_Update event, something like this.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim sWhere As String
sWhere = "[ParticipantID]=" & Me.User
sWhere = sWhere & " AND [StatusAlloc] = 'Pending approval'"
 
If DCount("*", [COLOR=red]tableName[/COLOR], sWhere) > 0 Then  ' It is a duplicate
    Cancel = True
    Me.Undo  '<----Remove this if you don't want to erase form input
End If
End Sub

Just change the marked in red to your real tablename.

JR

 

[Joe8915]

JANR, not experienced in VBA code, I have copied your code and pasted it in the BeforeUpdate Event. I am getting a Compile error:

Method or data member not found (shown in red)
Private Sub Invoice_Number_BeforeUpdate(Cancel As Integer)
Dim sWhere As String
sWhere = "[Invoice_Number]=" & Me.User
sWhere = sWhere & " AND [StatusAlloc] = 'Pending approval'"

If DCount("*", tblpayestimates, sWhere) > 0 Then ' It is a duplicate
Cancel = True
Me.Undo '<----Remove this if you don't want to erase form input
End If
End Sub

The field is called Invoice Number

The table is called tblPayestimates

So what did I do wrong?

 

[JANR]

The error indicate that you don't have a control on your form called "User", which by the way is a Access reserved word and that can become an issue.

Make sure that there is a control on your form, if it is then try and change it's name to txtUser.

JR

 

[Joe8915]

JANR, thanks for the quick reply. I tried the txtUser, and that did not work as well. Any other ideas?

 

[JANR]

What error do you get?

If txtUser is a textfield and not number you'll need to enclose Me.txtUser in single quotes,

sWhere = "[Invoice_Number]= '" & Me.User & "'

In your first post you stated:

Me!sfr_LineManID.Form!ParticipantID = Me.user

why is it now:

[invoice Number] = Me.txtUser

Final note:

In yor table tblpayestimate does the fieldname Invoice_Number really have an underscore or just a space??

if it's a space then:
sWhere = "[Invoice Number]=" & Me.User

or

sWhere = "[Invoice Number]= '" & Me.User & "'

If you still can't get it to work please post a stripped down version of your db so I can take a look

JR

 

[Joe8915]

JANR, First of all thank you ever so much helping me.
I tried to work with it for a couple hours, just could not make it work.

See the attach

 

[Joe8915]

JNAR, I attach the wrong db, what a fricken idiot I am. So sorry

 

[JANR]

- [invoice number] in table do not have an underscore and is cast as String and not number.
- Me.user is NOT anywhere in your db
- [SatusAlloc] is NOT in db, but somthing called pau_ID??

so if you want to avoid a duplicate of Invoice AND Pau_ID, then:

Private Sub invoice_number_BeforeUpdate(Cancel As Integer)
Dim sWhere As String
sWhere = "[Invoice Number]=[COLOR=red]'"[/COLOR] & Me.invoice_number & [COLOR=red]"'"
[/COLOR]sWhere = sWhere & " AND [Pau_ID] =" & Me.pau_id
 
If DCount("*", "tblpayestimates", sWhere) > 0 Then ' It is a duplicate
Cancel = True
MsgBox " Duplicate"
End If
End Sub

Take note of the delimiters around Me.Invoice_number.

As a sidenote dont use spaces in objectnames since VBA do not tolerate it. use camelcase instead. ex. InvoiceNumber

if invoice number is a real number the don't cast is as text.

JR

 

[Joe8915]

JR, that work like a charm. Once again many thanks