Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-29-2009, 08:39 AM   #1
skwilliams
Newly Registered User
 
Join Date: Jan 2002
Location: Indiana
Posts: 516
Thanks: 3
Thanked 0 Times in 0 Posts
skwilliams
Message box if duplicate value

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

skwilliams is offline   Reply With Quote
Old 09-29-2009, 08:41 AM   #2
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,822 Times in 1,577 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: Message box if duplicate value

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

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 09-29-2009, 08:46 AM   #3
skwilliams
Newly Registered User
 
Join Date: Jan 2002
Location: Indiana
Posts: 516
Thanks: 3
Thanked 0 Times in 0 Posts
skwilliams
Re: Message box if duplicate value

I added Me.Undo and Cancel = True.

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

skwilliams is offline   Reply With Quote
Old 09-29-2009, 08:47 AM   #4
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,822 Times in 1,577 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: Message box if duplicate value

Quote:
Originally Posted by skwilliams View Post
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?
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 09-29-2009, 08:49 AM   #5
Scooterbug
Registered User
 
Join Date: Mar 2009
Posts: 853
Thanks: 0
Thanked 2 Times in 2 Posts
Scooterbug will become famous soon enough Scooterbug will become famous soon enough
Re: Message box if duplicate value

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.
Scooterbug is offline   Reply With Quote
Old 09-29-2009, 08:52 AM   #6
skwilliams
Newly Registered User
 
Join Date: Jan 2002
Location: Indiana
Posts: 516
Thanks: 3
Thanked 0 Times in 0 Posts
skwilliams
Re: Message box if duplicate value

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.
skwilliams is offline   Reply With Quote
Old 09-29-2009, 08:54 AM   #7
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,822 Times in 1,577 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: Message box if duplicate value

Quote:
Originally Posted by skwilliams View Post
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).

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 09-29-2009, 09:13 AM   #8
skwilliams
Newly Registered User
 
Join Date: Jan 2002
Location: Indiana
Posts: 516
Thanks: 3
Thanked 0 Times in 0 Posts
skwilliams
Re: Message box if duplicate value

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.
skwilliams is offline   Reply With Quote
Old 09-29-2009, 09:17 AM   #9
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,822 Times in 1,577 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: Message box if duplicate value

Quote:
Originally Posted by skwilliams View Post
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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 09-29-2009, 09:28 AM   #10
skwilliams
Newly Registered User
 
Join Date: Jan 2002
Location: Indiana
Posts: 516
Thanks: 3
Thanked 0 Times in 0 Posts
skwilliams
Re: Message box if duplicate value

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
skwilliams is offline   Reply With Quote
Old 09-29-2009, 09:34 AM   #11
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,822 Times in 1,577 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: Message box if duplicate value

Quote:
Originally Posted by skwilliams View Post
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
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 09-29-2009, 09:43 AM   #12
skwilliams
Newly Registered User
 
Join Date: Jan 2002
Location: Indiana
Posts: 516
Thanks: 3
Thanked 0 Times in 0 Posts
skwilliams
Re: Message box if duplicate value

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?
skwilliams is offline   Reply With Quote
Old 09-29-2009, 09:48 AM   #13
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,822 Times in 1,577 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: Message box if duplicate value

Quote:
Originally Posted by skwilliams View Post
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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 09-29-2009, 09:55 AM   #14
skwilliams
Newly Registered User
 
Join Date: Jan 2002
Location: Indiana
Posts: 516
Thanks: 3
Thanked 0 Times in 0 Posts
skwilliams
Re: Message box if duplicate value

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
skwilliams is offline   Reply With Quote
Old 09-29-2009, 10:00 AM   #15
skwilliams
Newly Registered User
 
Join Date: Jan 2002
Location: Indiana
Posts: 516
Thanks: 3
Thanked 0 Times in 0 Posts
skwilliams
Re: Message box if duplicate value

I would send the file but it exceeds the allowed size limit. Even the zip file.

skwilliams is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Message Box Sinfathisar Modules & VBA 2 02-19-2009 12:57 PM
Check Box and Combo Box Blank ErikRP Forms 9 03-25-2008 11:09 AM
Duplicate record error message pd06498 Forms 1 03-08-2004 04:51 AM
[SOLVED] How do I issue items from a multi-level inventory? ltl Forms 5 01-27-2004 12:32 PM




All times are GMT -8. The time now is 05:11 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World