Message box if duplicate value (1 Viewer)

skwilliams

Registered User.
Local time
Today, 13:03
Joined
Jan 18, 2002
Messages
516
I have a tabular form "frmReprintInput" used for inputting data. This form stores data in the table "tblReprints". I would like to display a message box if a duplicate order number "Order" already exists, but allow the input of this duplicate order number. The field and control for Order is a Long Integer. I've tried this code, but it's not working. Is there a better way to accomplish this?

Code:
Dim Od As Long
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

Od = Me.Order.Value
stLinkCriteria = "[Order]=" & "'" & Od & "'"

'Check tblReprints table for duplicate Order Number
If DCount("Order", "tblReprints", stLinkCriteria) > 0 Then Me.Order=Od
MsgBox "Duplicate Order Number"
'Go to Original Order Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark

Set rsc = Nothing
 

boblarson

Smeghead
Local time
Today, 10:03
Joined
Jan 12, 2001
Messages
32,059
First of all, once you've started entering the number then you need to back out of the record (Me.Undo and Cancel = True in the before update event) if you are going to move to the existing record. You can't just go there as you have started a new record. You would need to find out if they want to go to that existing record before abandoning the input values.
 

skwilliams

Registered User.
Local time
Today, 13:03
Joined
Jan 18, 2002
Messages
516
I added Me.Undo and Cancel = True.

I'm getting an error message. Data type mismatch in criteria expression.
 

boblarson

Smeghead
Local time
Today, 10:03
Joined
Jan 12, 2001
Messages
32,059
I added Me.Undo and Cancel = True.

I'm getting an error message. Data type mismatch in criteria expression.
What is being highlighted on the error?

Also, what event is this code on?
 

Scooterbug

Registered User.
Local time
Today, 13:03
Joined
Mar 27, 2009
Messages
853
stLinkCriteria = "[Order]=" & "'" & Od & "'"

Od is a Long...so you dont need the "'" in there. When you put a value in quotes, it tells access that it's a string. Quotes are not needed when the value is a number.
 

skwilliams

Registered User.
Local time
Today, 13:03
Joined
Jan 18, 2002
Messages
516
This line is highlighted.

If DCount("Order", "tblReprints", stLinkCriteria) > 0 Then Me.Order.Undo

The event is being triggered by the After_Update on the "Order" control.
 

boblarson

Smeghead
Local time
Today, 10:03
Joined
Jan 12, 2001
Messages
32,059
This line is highlighted.

If DCount("Order", "tblReprints", stLinkCriteria) > 0 Then Me.Order.Undo

The event is being triggered by the After_Update on the "Order" control.

A couple of things.

Scooterbug is right on the numeric vs. text.

But, you also can't undo in an After Update event. It has already updated. It has to be the BEFORE update event and it would be the FORM's BEFORE UPDATE event, not the control. You would undo the RECORD (Me.Undo) not the control (Me.Order.Undo).
 

skwilliams

Registered User.
Local time
Today, 13:03
Joined
Jan 18, 2002
Messages
516
I removed the quotes and it works and displays the message box. The only problem now is after clicking ok on the message box, I get another error because the Employee field hasn't been completed yet.

Run-time error '3201':
You cannot add or change a record because a related record is required in table 'tblEmp'. I can click end and continue. But this wouldn't be an acceptable window to pop up for an end user.

The order control must be listed before the employee control.
 

boblarson

Smeghead
Local time
Today, 10:03
Joined
Jan 12, 2001
Messages
32,059
I removed the quotes and it works and displays the message box. The only problem now is after clicking ok on the message box, I get another error because the Employee field hasn't been completed yet.

Run-time error '3201':
You cannot add or change a record because a related record is required in table 'tblEmp'. I can click end and continue. But this wouldn't be an acceptable window to pop up for an end user.

The order control must be listed before the employee control.

Again, this code needs to be in the form's before update event, not the control's. If you issue Cancel = True and Me.Undo (in that order) then you should not have any problem navigating to the other record (unless I'm not understanding your setup). You may need to post it here so we can see if this can't be figured out.
 

skwilliams

Registered User.
Local time
Today, 13:03
Joined
Jan 18, 2002
Messages
516
Put the event in the form's before_update procedure.

Now I'm getting this error.
The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing McPhersons Production Database from saving the data in the field.

Here's the highlighted line
Me.Bookmark = rsc.Bookmark
 

boblarson

Smeghead
Local time
Today, 10:03
Joined
Jan 12, 2001
Messages
32,059
Put the event in the form's before_update procedure.

Now I'm getting this error.
The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing McPhersons Production Database from saving the data in the field.

Here's the highlighted line
Me.Bookmark = rsc.Bookmark
Did you cancel and then undo the form first?

Cancel = True
Me.Undo
 

skwilliams

Registered User.
Local time
Today, 13:03
Joined
Jan 18, 2002
Messages
516
I just spoke with the data entry person and she said the name can go before the order, so sound like that isn't an issue. One last thing though, after click OK on the message box it goes to the first record instead of the duplicate order number already in the list. Can I fix that?
 

boblarson

Smeghead
Local time
Today, 10:03
Joined
Jan 12, 2001
Messages
32,059
I just spoke with the data entry person and she said the name can go before the order, so sound like that isn't an issue. One last thing though, after click OK on the message box it goes to the first record instead of the duplicate order number already in the list. Can I fix that?
FindFirst will go to the first instance of that order number. Isn't is doing that? Do you mean that there is another instance of that order number in the table? If so, you need some way to have it find the original. Not sure what you could use, except perhaps the earliest date as well.
 

skwilliams

Registered User.
Local time
Today, 13:03
Joined
Jan 18, 2002
Messages
516
Yes. The order number may already be in another record but duplicate order numbers are allowed. We just want the message box so we now that one is already in there.

I also realized that the message box pops up even if no duplicate order exists. This is starting to seem like a lost cause. lol:eek:
 

skwilliams

Registered User.
Local time
Today, 13:03
Joined
Jan 18, 2002
Messages
516
I would send the file but it exceeds the allowed size limit. Even the zip file.
 

boblarson

Smeghead
Local time
Today, 10:03
Joined
Jan 12, 2001
Messages
32,059
Yes. The order number may already be in another record but duplicate order numbers are allowed. We just want the message box so we now that one is already in there.

I also realized that the message box pops up even if no duplicate order exists. This is starting to seem like a lost cause. lol:eek:

It does seem to be a bit convoluted. Remember you are working with a relational database here. Each record should have a way to be uniquely identified (normally a surrogate primary key works well - autonumber) and then you pull data based on it. If you have a primary key like that you can identify any order by its PK and also by its order number if you supply an additional number.
 

skwilliams

Registered User.
Local time
Today, 13:03
Joined
Jan 18, 2002
Messages
516
I stripped out other objects not relevant to the problem. Hope this is everything.
 

Attachments

  • db1.zip
    41 KB · Views: 68

Users who are viewing this thread

Top Bottom