Form does not show error on close

[lizray]

I have a form based on a table,with a primary index, which is the "EntryNumber" field for that form. If I enter a duplicate of an existing "EntryNumber"( ie the Index), and then close the form using the DoCmd.close statement, I expect to get an error message, but do not. At other parts of the program, I can change the "EntryNumber" so that it is a duplicate "EntryNumber" and it shows as an error, which I can trap and display. The problem only shows (ie No error) when the record I am entering via the form is a New Record (ie I do net get an error message for a duplicate "EntryNumber") whereas If I change an existing "EntryNumber" so that it duplicates another existing "EntryNumber", then I get the expected error when I close the form. Can anyone help me so that I can get the error when I close the form for a New Entry ?

 

[PHV]

Is the duplicate entry created when you close the form ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[billmeye]

Is EntryNumber a Primary Key? If not, set it to be your Primary Key and it will not allow duplicates.

 

[lizray]

The EntryNumber is the Primary Key and when i close the form after entering a duplicate value, it does not save the new record, but exits without an error message, and the normal form events (beforeupdate etc) do not occur. I want to catch the error and popup a message box displaying the error, but I dont know how to do this. If the record is an existing record, and I duplicate the entrynumber, access reports the error as expected.

 

[billmeye]

Initially, my instincs say you should not be able to enter a value in your primary key as that should almost always be an autonumber field that is hidden from view. With that said, you should be able to set EntryNumber index to Yes (No Duplicates) and Access will not allow duplicates. But, since you are having this problem, one suggestion would be to place a validation in the EntryNumber Before Update event to test for duplicates and cancel if it finds one:

Sub EntryNumber_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
    If Dcount("*","tblYourTable","[EntryNumber]=" & Me.EntryNumber) > 0 Then 
    Msgbox "That already exists"
    Cancel = 1
    End If
ElseIf Me.EntryNumber.OldValue <> Me.EntryNumber Then
    If Dcount("*","tblYourTable","[EntryNumber]=" & Me.EntryNumber) > 0 Then 
    Msgbox "That already exists"
    Cancel = 1
    Me.EntryNumber.undo
    End If
End If
End Sub

 

[lizray]

Thanks billmeye. This code looks great. It will do exactly what I want. You are right, the systems will not allow duplicate values, but if the user tries to change a entrynumber, then I want to display the message

 

[billmeye]

That's good to hear. Good Luck.

 

[missinglinq]

If I enter a duplicate of an existing "EntryNumber"( ie the Index), and then close the form using the DoCmd.close statement

It has long been recommended that the code

DoCmd.RunCommand acCmdSaveRecord

or

If Me.Dirty Then Me.Dirty = False

be inserted before using

DoCmd.Close

to close a Form because of a quirk in Access. When DoCmd.Close is used, Access closes the Form regardless of whether or not a PK field or other Required Field has been left blank or validation rules have been violated!

If one of these things occur, Access will simply dump the Record, Close the Form, and not tell the user that the Record has been dumped!

The code If Me.Dirty Then Me.Dirty = False or DoCmd.RunCommand acCmdSaveRecord
forces Access to attempt to Save the Record, and if a violation has occurred, will throw up a warning message, allowing correction to be made before Closing the Form.

You need to change your

DoCmd.Close

to

If Me.Dirty Then Me.Dirty = False

DoCmd.Close

or

DoCmd.RunCommand acCmdSaveRecord

DoCmd.Close

Linq ;0)>


The Missinglinq

Richmond, Virginia

The Devil's in the Details!

 

[lizray]

Excellent response Missinglinq. It explains the strange behavior and I will now use this to solve problem. Many Thanks