Duplicate Values

[kbreiss]

I have a table that does not allow duplicate Drivers license numbers.

If a user enters a duplicate DL number into the the Form that I created, Access throws back the whole long duplicate record error. This scares the users.

I would like to do some validation check were it checks the DL number field in the form and then checks the table to see if there is a duplicate DL number. If there is I would like a simple msgbox to display DL number is already on file. My only problem is I have no idea where to start. Any help would be greatly appreciated.

Thanks,

Kacy
________
T transmission

 

[Hayley Baxter]

What about using the find duplicates wizard? You can maybe put in some VBA code where if a duplicate is found then msgbox "Your Message"

Someone else might know a better way but I think the wizard would probably be the easiest method.


HTH
Hay

 

[Pat Hartman]

Use DLookup() to determine if the license is already in the db.

Haley, a find duplicates query would only work if the duplicate license were already added to the db.

 

[kbreiss]

I was hoping that it would be possible by using a msgbox and it's condition function since I know nothing about VB. Do you guys think that this may be possible or are you pretty sure that it can't be done by this using the msgbox function?

Something like this.....

Condition

[Forms]![APPOINTMENT]![DL_NUM]=[TABLES]![APPOINTMENT]![DL_NUM]

Action

MsgBox
________
AVANDIA PREGNANCY

 

[Hayley Baxter]

Ok Pat Thanks for bringing this to my attention.

Hay

 

[kbreiss]

I'm not exactly for sure how or where to use the DLookUp() function. Any suggestions? Thanks.

Kacy
________
Suzuki gs series specifications

 

[kbreiss]

OK...I've got this far. I have a macro to run on the "exit" of this field.

This is the macro..
1.) Run Command -

=DLookUp("[DL_NUM]","[APPOINTMENT]","[DL_NUM] = [Forms]![Appointment]![DL_NUM]")

2.) MsgBox -
DL is already on file.

Here is my problem.
1. ) When I enter a DL that is not a DUP it returns the MsgBox. If the DL is not a DUP I would like for it to skip the MsgBox.

2.) When I enter a DUP number (R111-1111-1111) I get the following error;

"The object doesn't contain the Automation object 'R111.' "

"You tried to run a VB procedure to set a property or method for an object. However the component doesn't make the property or method available for Automation operations.

I would like for it to skip the MsgBox if the number is not a DUP

Also, if it is a DUP I would like for the MsGBox to display.

Any suggestions on making this happen would be greatly appreciated.

Thanks,

Kacy
________
Marijuana Hemp