How and where to capture the "duplicate values" error message?

[Guest]

MS Access 2K, Windows XP
---------------------------
Hi,

I have a table with names of people (Fields: PersonID, FName, LName), with
PersonID as PK, and an index (unique) defined on the combination of (FName
and LName).

I also have a form to enter the names of people. When I try and enter a
first and last name that's already in the table and click on the Save button
(placed using wizard) on the form, I get the message: "changes requested to
the table were not successfull....will create duplicate values in index,
primary key or relationship".

I would like to display my own message instead of the message displayed by
Access. I'm not sure where and how to capture the MS Access message and
display my own. There is no error number displayed with the message.

My guess is BeforeUpdate event of the form, but I'm not sure.

Any help/pointers will be appreciated.

Thanks.

-Amit

 

[fredg]

MS Access 2K, Windows XP
---------------------------
Hi,

I have a table with names of people (Fields: PersonID, FName, LName), with
PersonID as PK, and an index (unique) defined on the combination of (FName
and LName).

I also have a form to enter the names of people. When I try and enter a
first and last name that's already in the table and click on the Save button
(placed using wizard) on the form, I get the message: "changes requested to
the table were not successfull....will create duplicate values in index,
primary key or relationship".

I would like to display my own message instead of the message displayed by
Access. I'm not sure where and how to capture the MS Access message and
display my own. There is no error number displayed with the message.

My guess is BeforeUpdate event of the form, but I'm not sure.

Any help/pointers will be appreciated.

Thanks.

-Amit

Here's how you can find the correct error and show your own message
for any of the form level errors.

First code the Form's Error event:

MsgBox "Error#: " & DataErr ' Display the error number
Response = acDataErrDisplay ' Display Default message

Then open the form and intentionally make that error.

The message box will display the error number and the default error
message.

Next, go back to the error event and change that code to:

If DataErr = XXXX Then
Response = acDataErrContinue ' Don't display the default message
MsgBox "This is your custom message."
Else
MsgBox "Error#: " & DataErr
Response = acDataErrDisplay ' Display Default message
End If

where XXXX is the error number.

 

[Graham Mandeno]

Hi Amit

Fred has shown you how to intercept the error message after it has been
raised, but it's sometimes nicer to detect the condition before the error
occurs.

For this, your guess is correct - use Form_BeforeUpdate. For example:

Dim lExisting as Long
lExisting = Nz( DLookup( "PersonID", "TableName", _
"FName=""" & Me!FName & """ and LName=""" & Me.LName & """" ), _
Me!PersonID)
If lExisting <> Me!PersonID Then
cancel = True
'Message here...
End If

 

[Guest]

<snip>

Hi Fred, Graham,

Thanks for your response. I'll try them out right away.
Graham, thanks for an alternative solution to my problem.
You guys are the best

-Amit