Avatar of yoducati
yoducati
 asked on

Custom Control Button on Access Form

I have a simple data entry form in my access database.  It it part of a "built in" navigation form using the tabs across the top and sub menus down the left side.  On this particular form for adding vendors I want to work around the access default saving of records.  I have created a button for the user to "Add Record" and I want the button to insert the record only if pressed.  I haven't figured out how to create a message that explains to the user that their entry has not been saved and will be lost if they don't click the button.  I used to accomplish this by having the "Add Record" and "Main Menu" buttons be the only ones on the form. I would put the code for the message behind the main menu button to prompt them if they hadnt saved the record.  If they continued I would simply undo the record through code before the form closed.  Since Access uses this fancy new navigation form I dont have a need for the Main Menu button and want to accomplish the same "undo" action if the user leaves the current tab on the navigation form without having pushed the "Add Record" button.
Microsoft AccessDatabases

Avatar of undefined
Last Comment
yoducati

8/22/2022 - Mon
PatHartman

Attaching this type of code to button events is fruitless and almost always results in the failure to actually trap errors.

Final validation code (you might put individual field edits in the BeforeUpdate event of controls) belongs in the FORM's BeforeUpdate event or in some cases the BeforeInsert event.  They are the only place you can in ALL cases truly stop the record from being updated.  You do that by cancelling the update.  Think of it as putting your finger at the bottom of a funnel to cut off the flow.  The BeforeUpdate event is the absolute LAST event to run before a record is saved.  The BeforeInsert event runs immediately after the first character is typed.

Cancel = True
Me.Undo 'but only if you really want to back out all changes made by user and not give him the chance to correct them.

Open in new window


To do what you are asking, Create a TempVar or form level public variable to use as a flag.  Then set/unset it in various events.

In the Form's Current Event:
bAdd = False

In the click event of your Add button:
bAdd = True

In the BeforeInsert event of the Form:
If bAdd = False Then
    Msgbox "blah blah",vbOKOnly
    Cancel = True
    Me.Undo
    Exit Sub
End If

In the AfterUpdate event of the Form:
bAdd = False
yoducati

ASKER
Ok I think I follow you on how it works, but I'm only a novice programmer so how do I go about creating the variable?
yoducati

ASKER
P.S. - When you talk about the form events, is that the individual form on the navigation form, or the navigation forms events you are talking about?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
PatHartman

The individual form.

At the top of the Form's code module, after - Option Explicit and before any procedure header.
Option Compare Database
Option Explicit

Dim bAdd AS Boolean

Open in new window

yoducati

ASKER
Done.  But now I get the "blah blah" message as soon as I try to type anywhere on the form which it doesnt allow me to do.
yoducati

ASKER
Should it be under the event for AfterInsert instead?  If its in the BeforeInsert event what is being undone?  Also I think I will need a vbOkCancel button won't I? That way the user can continue without saving or hit cancel to go back and add the record.  I tried it in the AfterInsert event but I get an error saying variable not defined with cancel highlighted.  Ill mess with it some more and see if I can figure it out.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
yoducati

ASKER
Im sure I am missing something but this definitely isn't working as is.  I've tried several different things to get it to work but have been unsuccessful.  As is I get the msgbox as soon as I try to type in the form and then get stuck in a loop of trying to type and then clicking ok on the msgbox.
PatHartman

You said you wanted them to click the button to indicate they want to add.  So that click event sets the bAdd to True.  Then when they actually start typing in the new record, the BeforeInsert event fires and checks the bAdd variable.  If it is true - meaning they clicked the button first, it allows the insert to proceed.  If it is false, they didn't click the button so the insert is stopped immediately.

The "After" events are not EVER used to stop an add/change action.  They don't fire until AFTER the record is saved so it is too late to cancel the event and stop the save.
yoducati

ASKER
Ok I think something is getting lost in translation.  When the user opens the form it is already opened in data entry mode for them to type in the fields.  When they click "Add Record" that is the "Save" function.  At the beforeinsert event I can't type anything in any field without getting stuck in the messagebox loop I referenced above.  I'm not sure if thats the issue or not but I added the following code to the BeforeUpdate event of the form and it seems to behave exactly like I want.

If bAdd = False Then
If MsgBox("You have not saved the current record.  If you continue your changes will be lost.  Click OK to continue, or Cancel to return.", vbOKCancel) = vbOK Then
    Cancel = True
    Me.Undo
    Exit Sub
Else
     Cancel = True
    End If
End If
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
yoducati

ASKER
Ok I take that back.  Its almost working the way I want.  The msgbox part is working correctly but the save record button is not.  Any suggestions?
ASKER CERTIFIED SOLUTION
PatHartman

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
yoducati

ASKER
Ok I think Im very close.  I made one small change to accomodate what I am trying to do.  Imagine the user is typing on the form and then realizes he needs to verify something in another part of the database before saving the record.  So half way through he clicks on the tab he wants to verify and gets my msgbox above.  The change I made is to have it undo if he clicks ok instead of cancel.  For the user the "cancel" is to cancel leaving form, not cancel saving the record.  If he hits ok he continues leaving and the record is not saved.  

The part I can't seem to get working is to have the form allow him to continue to the tab he already clicked if he says "ok".  Right now it behaves correctly in not saving the record but the user then has to click the tab he was trying to get to again.  Ideally I want the behavior to be that the message box shows up if he hasnt hit the button to save the record and tries to do anything else.  If he hits cancel on the msgbox it should do nothing but keep him where he is and close the msgbox.  If he hits ok it will continue with taking him to whatever other button he clicked on.
PatHartman

When Access is interrupted on the way to some other place, I don't know of a clean way to get it back on track.  You would have to somehow capture where the user clicked and then after the OK button is pressed, re-click what the user previously clicked.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
yoducati

ASKER
Ok thanks.  It works as is but I was trying to mimick something that happened to me when I clicked on one of the database tools links.  The system interrupted me with a msgbox like the one I was trying to replicate and when I clicked ok to continue it took me where I was going anyway.  I have no idea how to isolate and replicate that behavior though.  This is close enough for what I'm doing.  Thanks for the help.