?
Solved

How do I suppress the "You cannot save the record at this time" message

Posted on 2014-09-09
12
Medium Priority
?
1,278 Views
Last Modified: 2014-09-09
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
0
Comment
Question by:eantar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 58
ID: 40312616
You mean not saved yet by default?

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.
0
 
LVL 5

Author Comment

by:eantar
ID: 40312662
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
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40312675
try adding exit sub

   Cancel = True

exit sub

End If
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 75
ID: 40312698
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
0
 
LVL 58
ID: 40312741
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.
0
 
LVL 5

Author Comment

by:eantar
ID: 40312772
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
0
 
LVL 75
ID: 40312781
Again ... when exactly is the error message occurring ... ie, what set of actions are happening ?
0
 
LVL 5

Author Comment

by:eantar
ID: 40312806
Message Attached.Message on x-ing out.
0
 
LVL 75
ID: 40312812
U understand the message, but what keystrokes (actions) is resulting in this message ? Again, it usually occurs per what I stated above ...


m
0
 
LVL 38

Accepted Solution

by:
PatHartman earned 2000 total points
ID: 40312876
This is a common problem.  The user makes a change and presses the x to close the form.  As long as the record is valid, the save is completed without error and the form closes.  The problem is when there is an error and the BeforeUpdate event is cancelled or if there is an RI error and Access itself prevents the record from being saved.

The simplest way to solve the problem is to create a form level variable.

Dim bAllowClose as Boolean

In the Form's Current event sent the variable to true.
bAllowClose = True

In the Form's BeforeUpdate event set the variable to false.
bAllowClose = False

In the Form's AfterUpdate event set the variable to true
bAllowClose = True

Then in the form's unload event, check the variable.  Here you can give the user the option of not closing the form and going back to correct the error or to exit without saving.

If bAllowClose = True Then
    Exit Sub
Else
    If Msgbox("An error was discovered.  Press Yes to return to fix it.  Press No to exit without saving the change.",vbYesNo) = vbYes Then
        Cancel = True
        Exit Sub
    Else
        Me.Undo
        Exit Sub
    End If
End If

Open in new window

0
 
LVL 5

Author Closing Comment

by:eantar
ID: 40312901
Thank you. This is the closest I'm going to come to a solution. I appreciate the help.
0
 
LVL 5

Author Comment

by:eantar
ID: 40312911
Thanks every one for the help. I really appreciate it.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question