Access Questions for a Newbie - Duplicates using the Next Record button

[adamcm]

Hi all,

I'm jsut trying to set up a simple Access DB for a co-worker, but I wanted to add a little bit of extra features. One thing that I am really having a tough time with is error checking and not allowing duplicates in the DB.

Each of my forms are set up in the following format:
Form Fields (Name, etc)
Navigation buttons (First, Previous, Next, and Last)
Record buttons (Add, Update, and Delete)

I figured out how to display an error message to the user when entering a duplicate and clicking on the Add button by Exporting the Macros to VB, and by adding a DLookup. However, the Next button will allow for the record to be added as well (which I didn't know). Is there anyway to prevent the Next button from adding a duplicate record within the table? The only way I have found so far is by making the field indexed. This is fine except I can't display a nice error message to the user.

Any ideas?

Thanks!

[RuralGuy]

Actually you could replace the system error message in the OnError event of the form. You just need to know the error #.

[adamcm]

Actually you could replace the system error message in the OnError event of the form. You just need to know the error #.

Hey thanks for the help. But I don't think it's an actual error is it? I believe the Next Record functionality always will add the record won't it? I'm wondering if it is possible to do a duplicate check on using the Next Record button?

Thanks.

[RuralGuy]

Test for duplicates in the BeforeUpdate event of the form and Cancel the event if there is a duplicate. That will catch *any* method that tries to create a duplicate. Next, Previous, Save, Close, any method.

[adamcm]

Hey, that works great!

The only issue I have is after receiving the message that the "Role already exists in the DB", I receive another message:

"The value violates the validation rule for the field or record".

I don't see any other validations on my form and have no idea why this is appearing. Any ideas?

Thanks again!

[RuralGuy]

If you open the table in design mode, what do you have in the validation for that field?

[RuralGuy]

I tend to do all of my validation in a form and none at the table level so I am in complete control of the messages.

[adamcm]

There is nothing listed for RoleName in the Validation Text or Validation Rule. I have modified some of my fields including:

Required: Yes
Allow Zero Length: No
Indexed: Yes (No Duplicates)

The rest I believe are defaults. Do I need to remove these?

Thanks!

[RuralGuy]

Are you canceling the BeforeUpdate event when the user puts in a duplicate? That will stop the Update.

[adamcm]

Are you canceling the BeforeUpdate event when the user puts in a duplicate? That will stop the Update.

Here is the code...

If (Not IsNull(DLookup("[ResourceName]", _
"Resource", "[ResourceName] ='" _
& Me!ResourceName & "'"))) Then
MsgBox "Resource has already been entered in the Database."
Cancel = True
Me!ResourceName.Undo
End If

[RuralGuy]

Instead of the DLookup() function how about using a .FindFirst with the RecordSource RecordsetClone (DAO)? It should run faster than the Domain function as well.

[RuralGuy]

BTW, when you post event code it is useful if you start your copy with Private Sub... and end with End Sub.