Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-05-2004, 08:25 AM   #1
sloaner14
Registered User
 
Join Date: Apr 2002
Location: lawton, ok, us
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
sloaner14
Duplicate value message

I have a table with two primary keys, SSN and EntryDate. From a form, I want a custom message box that tells the user that an applicant has already been entered, and not the long default message that access uses. I am using Access 2002 with the 2000 file format. I have tried a lot of stuff, but can't seem to make any thing work. Any help would be great.

sloaner14 is offline   Reply With Quote
Old 03-05-2004, 08:29 AM   #2
Mile-O
Back once again...
 
Mile-O's Avatar
 
Join Date: Dec 2002
Location: Glasgow, UK
Posts: 11,312
Thanks: 4
Thanked 118 Times in 108 Posts
Mile-O will become famous soon enough
Arrow

First of all you don't have "two primary keys" in one table - you have a composite key - a primary key comprising two or more fields.

To make your own message you have to trap the Form's Error.

I don't know the number of this data error offhand but you can get it by putting the following line into the Code Builder's module of the Form's OnError event.

Code:
MsgBox DataErr
Run the code and cause the error. You'll get a message box displaying the number.

Now, replace the code with:

Code:
If DataErr = **** Then
    MsgBox "You already have this combination in the table.", vbInformation
    Response = acDataErrContinue
End If
Replace **** with the error number...
Mile-O is offline   Reply With Quote
Old 03-05-2004, 08:40 AM   #3
sloaner14
Registered User
 
Join Date: Apr 2002
Location: lawton, ok, us
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
sloaner14
MsgBox DataErr did not work still recieved the same message. Thanks for the info on the composite key. That makes perfect since.

sloaner14 is offline   Reply With Quote
Old 03-05-2004, 10:46 AM   #4
Mile-O
Back once again...
 
Mile-O's Avatar
 
Join Date: Dec 2002
Location: Glasgow, UK
Posts: 11,312
Thanks: 4
Thanked 118 Times in 108 Posts
Mile-O will become famous soon enough
Quote:
Originally Posted by sloaner14
MsgBox DataErr did not work still recieved the same message.
I never said it would fix your problem; it was only to get the error number as I couldn't tell you it offhand.

The second part solves that error.
Mile-O is offline   Reply With Quote
Old 03-05-2004, 10:58 AM   #5
sloaner14
Registered User
 
Join Date: Apr 2002
Location: lawton, ok, us
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
sloaner14
Sorry for not explaining better. I didn't get a message box with an error number. I continued to get "The changes you requested to the table were not succesful because they would create duplicates values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redifine the index to permit duplicate entries and try again." message box.
sloaner14 is offline   Reply With Quote
Old 03-11-2004, 07:01 PM   #6
Rich_Lovina
Registered User
 
Join Date: Feb 2002
Location: Perth, WA, Australia
Posts: 225
Thanks: 0
Thanked 0 Times in 0 Posts
Rich_Lovina
You state at the outset you are getting the Access command, which means you are trying to enter a new record in a related table, which needs to be added separately first.

I have some code from some of our good friends here (Pat Cowley, Fornation, Jack Cowley and Doug) which shows me :
"IF J Bloggs and deptcode already in system don't enter twice." This is:


Private Sub Combo143_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[Inits]", "GOVDOUG", "[Inits]= '" & Me![Inits] & "' And [Surname] = '" & Me![Surname] & "' And [DeptCode] = '" & Me![Deptcode] & "'")) Then
Beep
MsgBox "That full name and Deptcode already exists"
End If
Deptcode_Exit:
Exit Sub
Deptcode_Err:
MsgBox Error$
Resume Deptcode_Exit
End Sub

This is one check!
Any advanced users reading this who could help me add a function which will take me to the first instance of this record so the user can update the original??
Rich_Lovina is offline   Reply With Quote
Old 03-12-2004, 01:10 AM   #7
R6Flyer
Yamaha hooligan
 
Join Date: Nov 2003
Location: South Yorkshire, England
Posts: 95
Thanks: 0
Thanked 0 Times in 0 Posts
R6Flyer
The error number should be 3022

__________________
Cheers,

Flyer

Don't beam me up yet Scottie, I'm still having a shiiiiiiiiiiiii......
R6Flyer is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 10:23 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