duplicate values

[pascal]

I have the following problem. I have a form with several fields including a date field called "Date". Now this field is unique so duplicate values are not allowed. So when I have added a new record but filled in a date that already exists and I close the form with the cross in the upper-right corner I first get my own message that I filled in a date value that already exists, then I get a message that the record can't be saved. Now, how can I disable this last message and in which event do I have to place it.

Thank you in advance.

 

[Jack Cowley]

First thing to do is change the name of your date field from Date to something else as Date is a reserved word in Access and can give you problems. Your code to validate your 'date' field can be either in the 'date' fields Before Update event for in the Forms Before Update event. If the code resides there you can cancel the update and then make your changes. There is a code example in Help on how to use the Before Update event.

hth,
Jack

 

[pascal]

Thank you very much Jack for the quick reply. Unfortunately I still couldn't solve the puzzle. I'm going to explain my problem a bit better first. You should try it out yourself perhaps to understand my problem. So here it is. Let's say I've got a form with only one field called "CustomerNumber" which can't have duplicate values.
Now fill in a customernumber that already exists. Now when you go to another record with the navigationbuttons you simply get the message that the entered customernumber already exists on which you reply with yes to get back to the form. OK so far. But you should try this. Instead of going to another record with the navigation bars you close the form with the cross in the upper-right corner. Now you get two messages such as 1. the customernumber already exists and then followed by another one 2. You can't save this record at this time. Now, how do I have to solve this so that the second message not appears and I return back to the form instead of being closed.

Thanks again.

 

[Jack Cowley]

You need to force your user to exit via your Close button on the form. Remove the Close box (the X) in the form and then you can control if the form closes or not. This topic has been discussed here on a number of occasions and has been addressed by Pat Hartman. I suggest you seach the archives by subject and by Pat's name and you should find a number of threads....

Jack

 

[Pat Hartman]

I covered this in some detail just yesterday.

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=42867&highlight=gPendingError

Searching the archives can produce an amazing store of information. You just need to be creative and persistant.

 

[Jack Cowley]

I know that you have covered this subject in the past but I did not see the thread from yesterday. Thanks Pat....

Jack

 

[pascal]

Thank you guys for your quick replies. I'm going to check all these things out. I hope it will work this time.

 

[pascal]

Still not working. Now, when I close the form with the X in the upper-right corner I don't get any messages and the form simply closes. What am I doing wrong here? This is my code so far.

Option Compare Database
Dim gPendingError

Private Sub Form_BeforeUpdate(Cancel As Integer)

gPendingError = False

If DataErr = 2169 Then
MsgBox "This record already exists", vbOKOnly
Cancel = True
gPendingError = True
End If

End Sub

Private Sub Form_Current()

gPendingError = False

End Sub

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 3022 Then
Response = acDataErrContinue
End If
If DataErr = 2169 Then
Response = acDataErrContinue
End If
End Sub

Private Sub Form_Unload(Cancel As Integer)

If gPendingError = True Then
MsgBox "You must fix the problem before closing the form", vbOKOnly
Cancel = True
End If

End Sub

 

[Pat Hartman]

The errors are being trapped in the Error event so that's where you need to set the gPendingError flag and put your msgbox.

 

[pascal]

Pat, is it possible for you to change my code and show it to me because I still can't figure it out. Thanks a lot.

 

[Pat Hartman]

No guarentees.

Option Compare Database 
Dim gPendingError 

Private Sub Form_BeforeUpdate(Cancel As Integer) 

If gPendingError = Then
    MsgBox "This record already exists", vbOKOnly 
    Cancel = True 
End If 

End Sub 

Private Sub Form_Current() 

gPendingError = False 

End Sub 

Private Sub Form_Error(DataErr As Integer, Response As Integer) 
If DataErr = 3022 Then 
    Response = acDataErrContinue 
End If 
If DataErr = 2169 Then
    MsgBox "This record already exists", vbOKOnly
    gPendingError = True  
    Response = acDataErrContinue 
End If 
End Sub 

Private Sub Form_Unload(Cancel As Integer) 

If gPendingError = True Then 
    MsgBox "You must fix the problem before closing the form", vbOKOnly 
    Cancel = True 
End If 

End Sub

 

[pascal]

Finally, it works. But I still had to made some changes. When I made a record that already exists and I close the form I get my message and my form remains opened.
Thanks again, Pat. Thanks to you I could finally solve my problem.

So, here is the code:

Option Compare Database
Dim gPendingError

Private Sub Form_BeforeUpdate(Cancel As Integer)

If gPendingError = True Then
Cancel = True
gPendingError = False
End If

End Sub

Private Sub Form_Current()

gPendingError = False

End Sub

Private Sub Form_Error(DataErr As Integer, Response As Integer)

If DataErr = 3022 Then
MsgBox "This record already exists !", vbOKOnly
gPendingError = True
Response = acDataErrContinue
End If

If DataErr = 2169 Then
gPendingError = True
Response = acDataErrContinue
End If

End Sub

Private Sub Form_Unload(Cancel As Integer)

If gPendingError = True Then
Cancel = True
gPendingError = False
End If

End Sub