Question Generating "Duplicate field exist" msg before update (1 Viewer)

LBP · May 17, 2011

I need to capture & display a msg that tell that I 've just entered duplicate data on the field of a record. The msg has to appear immediately after finish typing the duplicated field (before I get to the last field of the record).

I use "ProdID" as the reference field in PRODUCT table.

Also do I have to use form to do this, and how is the syntax for the code?

Thank you so much

boblarson · May 17, 2011

Yes, you would use a form and the code would go in the BEFORE UPDATE event of the control if you want it to occur immediately.

Do you type in ProdID or is it an autonumber?

To check to see if a ProdID would be duplicated it would be:

Code:

If DCount("*", "PRODUCT", "[ProdID] = " & Me.ProdID.Text) > 0 Then
   Msgbox "Product ID already exists", vbExclamation, "Duplicate Item"
   Cancel = True
   Me.ProdID.Undo
End If

That is assuming that ProdID is the value being entered and we use the .Text in this case because the control hasn't updated yet (it might be okay to use the .value but when on the control itself, and in the before update, I would use .Text).

If ProdID is text then we would need to add quotes to the code.

John Big Booty · May 17, 2011

Welcome to the forum;

You could use the DCount() function, in the Form's Before Update event. The code might look something like;

Code:

    If DCount("ProdID", "PRODUCT", "ProdID= " & Me.ProdID) > 0 Then
        MsgBox Me.State & " Already exists"
        Cancel = True
        Me.ProdID= ""
        Me.ProdID.SetFocus
    End If

This code assumes that ProdID is a numeric value if however it is a text field use;

Code:

    If DCount("ProdID", "PRODUCT", "ProdID= " & [COLOR="Red"]"'" &[/COLOR] Me.ProdID[COLOR="Red"]& "'"[/COLOR]) > 0 Then
        MsgBox Me.ProdID & " Already exists"
        Cancel = True
        Me.ProdID= ""
        Me.ProdID.SetFocus
    End If

Also consider implementing a naming protocol for your objects and controls, something like; TBL_TableName, FRM_FormName, QRY_QueryName etc. limit yourself to alpha and numeric charters and the underscore (_) , this will make it easier to write code and also make it more apparent what type of object it is that you are referring to.

LBP · May 17, 2011

Redirecting the focus (open) the duplicated record for editing

Thanks so much for your fast response guys. I 'll enthusiastically try it.

Meanwhile the next step I am trying to do (as the title says) would be to redirect the focus (or to open) the duplicated record so that I can make update if needed.

Cheers!

LBP · May 19, 2011

Hello John and Bob:

Unfortunately both your codes did not work.
The ProdID is actually also the primary key for the table, does this will affect it?

Is (are) there anything I need to declare at the beginning of the codes lines?
Thanks

boblarson · May 19, 2011

It might be easier if you uploaded a copy of your database (after running Compact and Repair and then zipping the file to a .zip file).

LBP · May 19, 2011

Thanks Bob,

Attached "sample.zip" is a sample with ProdID as SID. It is Access 2007 data.

Thanks again

todAnthony · May 31, 2011

Replying to 'subscribe' to thread.

Having save issue and none of you above code worked for me either. I did, of course, adjust the table and field names properly.

boblarson · May 31, 2011

todAnthony said:
Replying to 'subscribe' to thread.

Having save issue and none of you above code worked for me either. I did, of course, adjust the table and field names properly.

todAnthony:

1. You don't need to post in a thread to subscribe to it. Go up to the top of the post list of the thread and in THREAD TOOLS use the drop down and select SUBSCRIBE.

2. Post the code you actually tried to use which didn't work.

todAnthony · May 31, 2011

Opps, I didn't see thread tools and was looking for and obvious subscribe button. TY for the info.

Code where 'streetAddress' is the field in the 'SurveyData' table:

Private Sub streetAddress_BeforeUpdate()
If DCount("*", "SurveyData", "[streetAddress] = " & Me.streetAddress.Text) > 0 Then
MsgBox "Address already exists", vbExclamation, "Duplicate Item"
Cancel = True
Me.streetAddress.Undo
End If
End Sub

this creates a huge error containing: Procedure declaration does not match description of event or procedure having the same name.

Also: This error occurs when an event has failed to run because Microsoft Office Access cannot evaluate the location of the logic for the event. For example, if the OnOpen property of a form is set to =[Field], this error occurs because Access expects a macro or event name to run when the event is fired.

Very new to Access so please be gentle. TYVM

todAnthony · May 31, 2011

After researching for a solution it appears that this code work:

Private Sub streetAddress_BeforeUpdate(Cancel As Integer)
If IsNull(DLookup("[streetAddress]", _
"SurveyData", _
"[streetAddress] = """ & Me.streetAddress.Text & """")) = False Then
Cancel = True
MsgBox "Address already exists", vbOKOnly, "Warning"
Me![streetAddress].Undo
End If
End Sub

I would still like it to pull up into the form the duplicated record to do updates on it if necessary.

boblarson · May 31, 2011

todAnthony said:
I would still like it to pull up into the form the duplicated record to do updates on it if necessary.

I made some enhancements to your code you posted and then added the part to go to the record if there is an existing record.

Code:

Private Sub streetAddress_BeforeUpdate(Cancel As Integer)
Dim rst As DAO.Recordset
 
   If DCount("*", _
   "SurveyData", _
   "[streetAddress] = " & Chr(34) & Me.streetAddress & Chr(34)) > 0 Then
       Cancel = True
       MsgBox "Address already exists", vbOKOnly, "Warning"
       Me.Undo
       Set rst = Me.RecordsetClone
            rst.FindFirst  [streetAddress] = " & Chr(34) & Me.streetAddress & Chr(34)
            Me.Bookmark = rst.Bookmark
   End If     
 
End Sub

todAnthony · May 31, 2011

After click OK on the MsgBox I get:

Run-time error '94:
Invalid use of Null

Will try to tweak. Your thoughts?

boblarson · Jun 2, 2011

todAnthony said:
After click OK on the MsgBox I get:

Run-time error '94:
Invalid use of Null

Will try to tweak. Your thoughts?

Make sure any error handler is commented out temporarily so when this error appears you can click the DEBUG button which should take you to the offending line of code. Post that.

todAnthony · Jun 2, 2011

My issue has been resolved for that. I am actually abandoning that plan for pulling up the record (Long Story). The code for checking on dups is all I needed in the end.

Bob, I thank you a million times for your help. You are the man and I wish I knew half of what you have forgotten. I have started a thread with another question relating to your samples and tutorials if you wouldn't mind checking out.

Again Thank You Very Much.

boblarson · Jun 2, 2011

todAnthony said:
I have started a thread with another question relating to your samples and tutorials if you wouldn't mind checking out.

Looks like Paul has you on the right track there.

todAnthony · Jun 2, 2011

Yup. Thanks all ya'll for the knowledge.

Question Generating "Duplicate field exist" msg before update (1 Viewer)

LBP

New member

boblarson

Smeghead

John Big Booty

AWF VIP

LBP

New member

LBP

New member

boblarson

Smeghead

LBP

New member

Attachments

todAnthony

Wait... What?

boblarson

Smeghead

todAnthony

Wait... What?

todAnthony

Wait... What?

boblarson

Smeghead

todAnthony

Wait... What?

boblarson

Smeghead

todAnthony

Wait... What?

boblarson

Smeghead

todAnthony

Wait... What?

Similar threads

Users who are viewing this thread