exit form button and BeforeUpdate form event

I have an exit form button where I just want to confirm with the user if they
want to save or not, if they don’t it closes the form, but if they do it
saves the record before closing.  Things have worked great in other forms
with a simple BeforeUpdate event programmed on the form:  

Private Sub Form_BeforeUpdate(Cancel As Integer)
   
Dim intresponse As Integer
   
intresponse = msgbox("Save changes to this record?", vbYesNo, "Confirm Save")
   If intresponse = vbNo Then
       Cancel = True
   End If
    
End Sub



But now I have a particular form where I want to do some error checking
before the record is saved so I added an if statement to the BeforeUpdate
event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
   
Dim intresponse As Integer
   
intresponse = msgbox("Save changes to this record?", vbYesNo, "Confirm Save")
   If intresponse = vbNo Then
       Cancel = True
   Else
   	'Check the data for errors before saving
        	If (IsNull(Forms![ProductSKU].Active)) Then
            ' Warns the user of missing data.
            Beep
            msgbox "You need to assign an active or inactive status before
saving", vbExclamation, ""
            Cancel = True
        	End If
  End If
    
End Sub

It still works like a charm in that it doesn’t save if the user hasn’t filled
in the active status, but it also closes the form because, of course,  they
clicked the exit form button.  What I would like to do is give the user an
opportunity to fill in the active status before the form closes on them.
After all, the user did click that they wanted to save the record they just
failed the error check.  

I thought about putting the error checking in the exit button but then that
would be annoying for the users who didn’t want to save the record as they
would have to fill in the active status just so they could not save.  

Is there a way to get it to abort the code for the exit form button in the
BeforeUpdate code? Or is there a better way to accomplish what I need?

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200801/1

0
lambertsm
1/21/2008 8:58:42 AM
access.formscoding 7493 articles. 0 followers. Follow

4 Replies
744 Views

Similar Articles

[PageSpeed] 20

If you want the exit to continue after you cancel Form_BeforeUpate, you need 
to undo the form:
    Me.Undo

By "exit button", I assume you mean a command button on the form. If you use 
the Close action (in a macro) or method (in VBA code), Access silently loses 
the record if there is some reason it cannot be saved:
    http://allenbrowne.com/bug-01.html

The crucial thing would be to make your exit button force the save before 
you try to close the form. You need to use an approach that generates an 
error if the save fails, so you can trap it with an error handler, and not 
execute the Close. This kind of thing:

Private cmdExit_Click()
On Error Goto Err_Handler
    'Force the save
    If Me.Dirty Then Me.Dirty = False
    DoCmd.Close acForm, Me.Name

Exit_Handler:
    Exit Sub

Err_Handler:
    Select Case Err.Number
    Case3314, 2101, 2115   'can't save errors
        strMsg = "Record cannot be saved." & vbCrLf & _
            "Fix the entry, or press Esc to undo."
        MsgBox strMsg, vbExclamation, "Cannot exit"
    Case Else
        strMsg = "Error " & Err.Number & ": " & Err.Description
        MsgBox strMsg, vbExclamation, "Cannot exit
    End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim strMsg As String

    If IsNull(Me.ProductSKU) Then
        Cancel = True
        strMsg = "Status missing." & vbCrLf & _
            "Fix the entry, or press Esc to undo."
        MsgBox strMsg, vbExclamation, "Invalid data"
    Else
        strMsg = "Save?"
        If MsgBox(strMsg, vbOkCancel, "Confirm") <> vbOk Then
            Cancel = True
        End If
    End If
End Sub

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"lambertsm via AccessMonster.com" <u39653@uwe> wrote in message
news:7e88606f39785@uwe...
>I have an exit form button where I just want to confirm with the user if 
>they
> want to save or not, if they don’t it closes the form, but if they do it
> saves the record before closing.  Things have worked great in other forms
> with a simple BeforeUpdate event programmed on the form:
>
> Private Sub Form_BeforeUpdate(Cancel As Integer)
>   Dim intresponse As Integer
>   intresponse = msgbox("Save changes to this record?", vbYesNo, _
>        "Confirm Save")
>   If intresponse = vbNo Then
>       Cancel = True
>   End If
> End Sub
>
>
> But now I have a particular form where I want to do some error checking
> before the record is saved so I added an if statement to the BeforeUpdate
> event:
>
> Private Sub Form_BeforeUpdate(Cancel As Integer)
>
> Dim intresponse As Integer
>
> intresponse = msgbox("Save changes to this record?", vbYesNo, "Confirm 
> Save")
>   If intresponse = vbNo Then
>       Cancel = True
>   Else
>   'Check the data for errors before saving
>        If (IsNull(Forms![ProductSKU].Active)) Then
>            ' Warns the user of missing data.
>            Beep
>            msgbox "You need to assign an active or inactive status before
> saving", vbExclamation, ""
>            Cancel = True
>        End If
>  End If
>
> End Sub
>
> It still works like a charm in that it doesn’t save if the user hasn’t 
> filled
> in the active status, but it also closes the form because, of course, 
> they
> clicked the exit form button.  What I would like to do is give the user an
> opportunity to fill in the active status before the form closes on them.
> After all, the user did click that they wanted to save the record they 
> just
> failed the error check.
>
> I thought about putting the error checking in the exit button but then 
> that
> would be annoying for the users who didn’t want to save the record as they
> would have to fill in the active status just so they could not save.
>
> Is there a way to get it to abort the code for the exit form button in the
> BeforeUpdate code? Or is there a better way to accomplish what I need? 

0
Allen
1/21/2008 9:28:10 AM
Well, now it won’t let you exit unless the status is filled in, fine if you
want to save but annoying if you don’t want to save as now you have to fill
in the status just so you can not save the record and leave.    ??????  I
want to give the users the choice when they close the screen if they want to
save the record or not.  But if they do want to save I need it to run error
checking that stops both the record save and the form from closing.  


Allen Browne wrote:
>If you want the exit to continue after you cancel Form_BeforeUpate, you need 
>to undo the form:
>    Me.Undo
>
>By "exit button", I assume you mean a command button on the form. If you use 
>the Close action (in a macro) or method (in VBA code), Access silently loses 
>the record if there is some reason it cannot be saved:
>    http://allenbrowne.com/bug-01.html
>
>The crucial thing would be to make your exit button force the save before 
>you try to close the form. You need to use an approach that generates an 
>error if the save fails, so you can trap it with an error handler, and not 
>execute the Close. This kind of thing:
>
>Private cmdExit_Click()
>On Error Goto Err_Handler
>    'Force the save
>    If Me.Dirty Then Me.Dirty = False
>    DoCmd.Close acForm, Me.Name
>
>Exit_Handler:
>    Exit Sub
>
>Err_Handler:
>    Select Case Err.Number
>    Case3314, 2101, 2115   'can't save errors
>        strMsg = "Record cannot be saved." & vbCrLf & _
>            "Fix the entry, or press Esc to undo."
>        MsgBox strMsg, vbExclamation, "Cannot exit"
>    Case Else
>        strMsg = "Error " & Err.Number & ": " & Err.Description
>        MsgBox strMsg, vbExclamation, "Cannot exit
>    End If
>End Sub
>
>Private Sub Form_BeforeUpdate(Cancel As Integer)
>    Dim strMsg As String
>
>    If IsNull(Me.ProductSKU) Then
>        Cancel = True
>        strMsg = "Status missing." & vbCrLf & _
>            "Fix the entry, or press Esc to undo."
>        MsgBox strMsg, vbExclamation, "Invalid data"
>    Else
>        strMsg = "Save?"
>        If MsgBox(strMsg, vbOkCancel, "Confirm") <> vbOk Then
>            Cancel = True
>        End If
>    End If
>End Sub
>
>>I have an exit form button where I just want to confirm with the user if 
>>they
>[quoted text clipped - 53 lines]
>> Is there a way to get it to abort the code for the exit form button in the
>> BeforeUpdate code? Or is there a better way to accomplish what I need?

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200801/1

0
lambertsm
1/22/2008 1:13:08 AM
OK - I think I have made this way to hard.  I just accomplished what I wanted
by getting rid of the BeforeUpdate event and programming everything on the
exit command.

lambertsm wrote:
>Well, now it won’t let you exit unless the status is filled in, fine if you
>want to save but annoying if you don’t want to save as now you have to fill
>in the status just so you can not save the record and leave.    ??????  I
>want to give the users the choice when they close the screen if they want to
>save the record or not.  But if they do want to save I need it to run error
>checking that stops both the record save and the form from closing.  
>
>>If you want the exit to continue after you cancel Form_BeforeUpate, you need 
>>to undo the form:
>[quoted text clipped - 52 lines]
>>> Is there a way to get it to abort the code for the exit form button in the
>>> BeforeUpdate code? Or is there a better way to accomplish what I need?

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200801/1

0
lambertsm
1/22/2008 1:54:30 AM
"lambertsm via AccessMonster.com" <u39653@uwe> wrote in message
news:7e913efc7994c@uwe...
> OK - I think I have made this way to hard.  I just accomplished what I 
> wanted
> by getting rid of the BeforeUpdate event and programming everything on the
> exit command.

Not sure that's a good idea.

The test will not execute if the record gets saved by any other means than 
your buttons (e.g. pressing Shift +Enter, tabbing past the last control on 
the form, saving through the menu, closing Access, applying a filter, 
changing the sort order, etc, etc.)

The MsgBox text I posted in the previous reply informs the use to press Esc 
if they wish to discard the record without filling it out.

Alternativley, you can use a MsgBox with vbYesNoCancel, and handle the 3 
states. In my view, that's less clear, but you can do it.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

0
Allen
1/22/2008 8:05:06 AM
Reply:

Similar Artilces:

News button is missing from customize toolbars
I tried to add the News button to allow the newsgroup reader function. It was there at first. I followed the instruction that said it was hidden, and I had to move it from the Customize Toolbar menu. I went under the Go section, and found News. I moved it to the Go section of my file menu, and it was there. I opened the newsgroup reader, and started to create a usenet account. I was missing some info for my account, so I canceled out and closed the newsgroup reader and Outlook. Now when I open Outlook, the News button that I put under the Go File menu is gone, and it isn't...

Max Limit for Values in List Boxes and Combo Boxes in Outlook forms
Folks, a user is setting up a form. She wants to have several combo boxes and list boxes with between 75 and 300 (yes, 300 -- that's not a typo!) possible values in the combo box or list box. Before she starts work on this, she would like to know if there is a limit on the number of possible values for a combo box or a multi-select list box. Is there a limit? Is it defined by the number of characters in the values box, or the number of actual values? What is the limit? Any help here would be appreciated. Thanks. David consulttech2004@hotmail.com ...

Stop advancing in form with the "next" arrow at end of "list"
I have an editable form for users where they can query by date, etc. However, the next entry arrow allows them to advance beyond the result. For example, if their query resulted in 1 of 4, they can still click to 5, 6, and so on which only adds blank lines to the database table. How do I set this to stop at the last entry of their original query? You can cancel adding new records like this: Private Sub Form_BeforeInsert(Cancel As Integer) Cancel = True End Sub Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Duchess" <Duchess@discuss...

Resize Forms in Access 2007
I thought I read somewhere that Access 2007 had a new function, or something, enabling you to have forms automatically resize to fit the screen resolution of the user. Is this correct, or am I wrong? If I am correct, how do you use this feature? Thanks, BobV On Aug 9, 8:41 am, "BobV" <rk...@earthlink.net> wrote: > I thought I read somewhere that Access 2007 had a new function, or > something, enabling you to have forms automatically resize to fit the screen > resolution of the user. Is this correct, or am I wrong? If I am correct, how > do you use this feature?...

Form Based Auth.
Hi All, I have checked the option to use form based authentication but I don't get the web page login - still the pop up login box. I have restarted IIS. This is all internal at the moment till i get the forms working. Any ideas. Regards Joe. Do you have "require SSL" checked on the Exchange (and Public) virtual directories in IIS? Have you tried going to the https:// site manually? Forms-based auth only works with SSL. If you haven't required SSL and you go to the http page, it will still work just fine and will pop up the login box. -- Ben Winzenz Exchange MVP ...

Check if Form is Open
I need to check to see if a form is open in the Onclose of another form. And if it is to requery it, if not do nothing. I did find a Function on The Access Web web site but I'm not sure how to utilize the function. Function fIsLoaded(ByVal strFormName As String) As Integer 'Returns a 0 if form is not open or a -1 if Open If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> 0 Then If Forms(strFormName).CurrentView <> 0 Then fIsLoaded = True End If End If End Function I need to run this in the OnClose of a form to see if frmHome is o...

Multi-field search form
Hi there, I am rather new to this place, so I would like to thank you all up front for any and all assistance you may provide. :) It has been a while since I worked with MS Access last. What I am trying to do is create a multi-field search form, where any of the fields can be filled which will display a result set. Preferably if no selection in the fields is made, the entire resultset should be shown - this is not a requirement, though. I seem to be a bit at a loss on how to procede with this task or to find any resources to help me get along. Again, thanks for any and ...

How the heck do I find tables, views, forms etc... in Access 2007
Can Access 2007 navigation be any more convoluted? I've been spending close to an hour trying to find a single table, query form, etc... now that the navigation has been changed. Is there a way to get the old style back? It does take some time - more than an hour :-) - to become familiar with the new interface in A2007 (NavPane, ribbon, ...) Suggestions: - The title bar at the top of the Nav Pane includes a tick box for: All Access Objects - Set Category to: Object Type - The categories (Tables, Queries, ...) collapse. - Show and use the Search Bar. It filters objects as...

aspx ascx and events
Hi, is the following a reasonable way to do things, or what would be better? The set up is as follows: I have an aspx, with many ascx's on it. The ascx's are not aware of each other, but they do depend on data from each other. Eg. (1) "search ascx" which accepts user input, and issues a request to a url and receives the returned xml (a list of shop data). (2) "raw display ascx" which displays the xml as-is. (3) "list display ascx" which processes the xml and displays only the returned "shop names" from the xml. The way I do it,...

Toolbox wizard to create Command Button
Access 2003 Windows XP Professional I used the Control Wizard on the Toolbox to create a Command Button to close the form. When I try to use it it will not run and returns this error message - The expression On Click you entered as the event property setting produced the following error: Expected: list separator or ). * The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure]. * There may have been an error evaluating the function, event, or macro. Any ideas why? I'm at a loss. Check to see if you haven't changed the name ...

show pictures in a form
i am trying to make a database and i have a record that has a hyperlink to a picture i have two question 1) is the hyperlink the best choice to use for the picture 2) how do i make the picture visble on a form thank you for your help sorry i sent this to the wrong newsgroup how every any advice would still be appreciated "William Storey II" <billstorey@sbcglobal.net> wrote in message news:zguub.30991$TL5.30867@newssvr25.news.prodigy.com... > i am trying to make a database and i have a record that has a hyperlink to a > picture > > i have two question > &g...

Dynamically Setting a forms Input Parameter Property
Hi , I'm working with a ADP project, I have a form where I set the recordset property at design time to a SQLServer stored procedure that has one parameter. If I set the Input Parameter property of the form at design time all is well. However I'd like to be a little more flexible in that I have the calling object that Opens the form using Docmd.Openform pass OpenArgs to the form. On the Form_Open event I set the InputParameter property of form using the value in OpenArgs. The problem is I'm still prompted with the parameter popup..if I press Ok everything continue on and ...

Exchange 2003 pop3 connector events 1036, 1023 and 1019
Since we installed SBS 2003 in November, 2003, we have had some problems with email not being able to be sent or received. If we check the event log, we see errors for every pop3 email account every 15 minutes when the Exchange server goes to check for new messages. We can fix this by running the Connect to Internet Wizard (not making any changes to the settings, just running the wizard) and restarting the server. When the server boots, everyone will receive the all the email they missed when the server was down. Here are examples from the event log: Event ID 1036 An error occurred during ...

Double-click notifications from a button?
How do you get double-click notifications from a button in an MFC dialog? I created an "ordinary" button using the resource editor. When instantiated, it has the BS_NOTIFY style, according to Spy++. If this button is double-clicked, it sends a BN_CLICKED message, but no BN_DOUBLECLICKED. This is Win XP Pro SP1 and VC++ 6.0 SP5. I would prefer to avoid solutions that require timing to see if single-clicks should be combined, and those which require me to wait after a single-click to see if another click is coming. sherNOwoodSPAM@computer.org (remove caps to get e-mail) I'...

This error in event log
Hi all I do have exch2003 and outlook 2003 I did get this error 4 times today in the event log. I cannot find concrete info on it on how to solve it. Should i ignore it or is it trying to tell me something important : Thanks for ll the help Error 0x6bb deleting unused restricted view from folder 1-D86610 on database "First Storage Group\Mailbox Store (<servername>)". Microsoft Exchange Information Store will try to delete the view again at the next maintenance interval. For more information, click http://www.microsoft.com/contentredirect.asp. http://support.microsof...

Activate Purchase Button in Asset General Information window
Hi there, Does anyone know how to make the purchase button from the Asset General Information window appear (Cards >> Fixed Assets >> General) What I'm trying to do is get the link enabled between the Transaction entry window and the Asset General information window. I've create an entry and posted it, by the purchase does not appear. Thanks for your help. Hi Ian You should re-visit your Fixed Assets Company setup window as that is where you tell the system that you want the link between the purchase transaction and the Fixed Assets module. Hope that helps Sheila ...

ComboboxEx Select and buttons disappear?
When I select an item from a comboboxex on my dialog all my buttons disappear, this is really strange and I'm not sure about how to go about fixing it..Anybody had this problem? any ideas? thanks OK I figured out comboboxex is calling wm_paint but wm_paint is not redrawing the controls, how can I force this? > Nevermind it was the buttons transparent property set to true that was causing it "David S." <big_dave@comcast.net> wrote in message news:TIKcnXpQBIZ0x2KjXTWJkw@comcast.com... > When I select an item from a comboboxex on my dialog all my buttons > disa...

Which 'event' to run pivot chart formatting code on file open?
Greetings I have code to change my pivot chart type to a mix of line and column also to refomat colours I need to run the code so that the user sees the changes when the file is opened Ive tried the 'workbook open' event and the chart activate event but noy joy so far. (Once the file is open, manualy triggering the code works perfectly) Any ideas? Thanks I don't know why workbook_open doesn't work. You could use Application.OnTime to run your code a short time (like 2 sec) after the workbook opens. - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://Peltier...

How do I assign a macro I have created to a Command Button in Exc.
I believe on previous versions of excel you right-clicked on the command button and then clicked on assign marco, but this does not seem to appear in the 2003 version of excel. Any help is appreciated. If you add a button from the Forms toolbar, you'll see this option. I think you added a commandbutton from the control toolbox toolbar. Andrew7675 wrote: > > I believe on previous versions of excel you right-clicked on the command > button and then clicked on assign marco, but this does not seem to appear in > the 2003 version of excel. Any help is appreciated. -- D...

printing to a form
how do i print addresses from excel to an address form page? is i possible? the page came out of an address book and i have to do lik 200 of them. thank -- Message posted from http://www.ExcelForum.com ...

Outlook 2003
I have been using Outlook 2003 with one user/email (pop3)/profile for a few years and have a great system of folders and saved messages. I recently added a new profile with its own email (pop3) and everything is fine. Upon opening Outlook it prompts for the profile to open and I can open both. However, when I select my 'original' profile (listed at 'Outlook' in the drop-down box presumeably because it was the original profile), all my original folders and saved messages appear, but the Send/Receive button is missing from the toolbar as well as from the Tools menu. The S...

Button apperance
I am using ActiveX option buttons in my spreadsheet and the apperance of the buttons is pixelated and distorted. Is there a way to make the buttons look cleaner or load new option button styles into excel? Hi there, You could try using Autoshapes as buttons.....much more presentable -- Thank you and Regards Garreth Lombard "ndobbo" wrote: > I am using ActiveX option buttons in my spreadsheet and the apperance of the > buttons is pixelated and distorted. Is there a way to make the buttons look > cleaner or load new option button styles into excel?...

Mutiple Table Entry into One form Wthout using subforms
Hello all I have created a form by adding information from several differnt forms and table as they areall getting input by several differnt people on the network. the problem i am having is that i can not make this new form add records and save them for later query or report from it? could someone please help -- Message posted via http://www.accessmonster.com Why do you not want to use subforms? Unless you can join the tables in such a way that the resultant query is updatable (usually a challenge), subforms are the easiest approach. -- Doug Steele, Microsoft Access MVP http://I.Am/D...

Grouping Form Fields on Continuous Form
Hi, I have a table which looks somewhat like this: --------------------------- | Type | Name | Value | --------------------------- | A | Ball | 1.00 | | A | Cup | 2.00 | | B | Fly | 0.50 | (...and so on) I want these displayed on a form, however, grouped by Type (similar to a report), for example Type: A Ball [Textbox] Cup [Textbox] Type: B Fly [Textbox] Is such a thing possible? The closest I can get is with multiple labels and conditional formatting. Code seems to be of little us...

Syncing with Blackberry: bug with private events in calendar?
When I create a "private" calendar event in the Blackberry, the corresponding entry that appears in Outlook does not have the private flag set. Our synchronizing is using the Blackberry Wireless Sync, and we use MS Exchange and a Blackberry Enterprise Server. Is this a bug/known limitation? Thats a good question. I too have experienced this. doesnt bother me much as i dont set many 'private' calendar events. Hope you find resolution! -- Dan Schlicht Microsoft Gold Partner / MBS Partner - Great Plains / Small Business Specialist Platinum Systems, Inc. Kenosha, ...