How do I suppress the "You cannot save the record at this time" message
Hey Experts,
I have a strange problem. I have some code in the before update event of a form that checks for duplicate values. If it finds duplicates it warns the user with a message and sets cancel to true.
All this works great unless they try to x out of the form. When that happens, they get the warning message PLUS the "You cannot save this record at this time" message.
Here's the simple code:
If DCount("*", "tblTeacherAssistants", "LastName = """ & LastName & """ and FirstName = """ & FirstName & """ and SiteID = " & SiteID) > 0 Then
MsgBox "The combination of Last Name, First Name and Site has already been entered. To back out of this entry, please press Esc." & vbCrLf & vbCrLf & "You can use the Find and Filter features at the top of this form to find the entered Teacher Assistant.", vbCritical
Cancel = True
End If
It feels like I've done this a thousand times and never got this message.
I would like to allow the user to simply x out of the form and let the data be saved by default.
Can some one help me on how to do this.
Thanks,
EEA
I have a strange problem. I have some code in the before update event of a form that checks for duplicate values. If it finds duplicates it warns the user with a message and sets cancel to true.
All this works great unless they try to x out of the form. When that happens, they get the warning message PLUS the "You cannot save this record at this time" message.
Here's the simple code:
If DCount("*", "tblTeacherAssistants", "LastName = """ & LastName & """ and FirstName = """ & FirstName & """ and SiteID = " & SiteID) > 0 Then
MsgBox "The combination of Last Name, First Name and Site has already been entered. To back out of this entry, please press Esc." & vbCrLf & vbCrLf & "You can use the Find and Filter features at the top of this form to find the entered Teacher Assistant.", vbCritical
Cancel = True
End If
It feels like I've done this a thousand times and never got this message.
I would like to allow the user to simply x out of the form and let the data be saved by default.
Can some one help me on how to do this.
Thanks,
EEA
ASKER
No, I actually want to allow the user to SAVE by default. I'm just not sure why the message is coming up. I feel like I've done this a thousand times and never had this problem.
I don't want to do a Me.Undo, because I'd like to give the user a chance to make a change to the entered data.
Any ideas?
Thanks,
EEA
I don't want to do a Me.Undo, because I'd like to give the user a chance to make a change to the entered data.
Any ideas?
Thanks,
EEA
try adding exit sub
Cancel = True
exit sub
End If
Cancel = True
exit sub
End If
Is the User actually hitting ESC, and then the message is happening ?
Do you have a multi field unique index in the table that does not allow dups on these 3 fields?
That error message usually occurs when ... a user tries to close the Form ... and the dup condition exists ... and you have a unique index in the table to prevent dups
You night try this:
If Dcount( ........ Then
MsgBox ....
Cancel = True
Me.txtFirstName.SetFocus ' so user can make change(s)
End If
Do you have a multi field unique index in the table that does not allow dups on these 3 fields?
That error message usually occurs when ... a user tries to close the Form ... and the dup condition exists ... and you have a unique index in the table to prevent dups
You night try this:
If Dcount( ........ Then
MsgBox ....
Cancel = True
Me.txtFirstName.SetFocus ' so user can make change(s)
End If
Their getting the second message because your canceling the event and therefore the save.
I guess I don't understand....you warn them, then cancel the save, but when they hit the X, you want the record saved without warning or no?
Seems to me you want to warn them, but not cancel the save unless they elect to.
The second message by the way can be trapped in the forms OnError event and in that event, by setting the Response variable, you can control whether Access displays a message to the user or not.
Jim.
I guess I don't understand....you warn them, then cancel the save, but when they hit the X, you want the record saved without warning or no?
Seems to me you want to warn them, but not cancel the save unless they elect to.
The second message by the way can be trapped in the forms OnError event and in that event, by setting the Response variable, you can control whether Access displays a message to the user or not.
Jim.
ASKER
The Exit Sub and the .Setfocus didn't help. Urg!
I think I'm going to have to add an official save button. But I'd love to know if there is a work around for this.
Any other ideas?
Best,
EEA
I think I'm going to have to add an official save button. But I'd love to know if there is a work around for this.
Any other ideas?
Best,
EEA
Again ... when exactly is the error message occurring ... ie, what set of actions are happening ?
ASKER
Message Attached.
U understand the message, but what keystrokes (actions) is resulting in this message ? Again, it usually occurs per what I stated above ...
m
m
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you. This is the closest I'm going to come to a solution. I appreciate the help.
ASKER
Thanks every one for the help. I really appreciate it.
If so, you need to do:
On Error Resume next
Me.Undo
Me.Undo
in the OnUnload event, which undoes all the current edits so the current record is not saved.
Jim.