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.
yoducatiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
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
0
yoducatiAuthor Commented:
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?
0
yoducatiAuthor Commented:
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?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PatHartmanCommented:
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

0
yoducatiAuthor Commented:
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.
0
yoducatiAuthor Commented:
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.
0
yoducatiAuthor Commented:
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.
0
PatHartmanCommented:
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.
0
yoducatiAuthor Commented:
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
0
yoducatiAuthor Commented:
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?
0
PatHartmanCommented:
OK, Instead of using the BeforeInsert event, use the BeforeUpdate event of the form.  But, you need to check to see if this is a new record.  Also, change the If that checks the button response.
In the BeforeUpdate event of the Form:
If Me.NewRecord Then
     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) = vbCancel Then
             Cancel = True
             '''Me.Undo  'You probably don't want to do this since in this event, it could remove a lot of typing.
             Exit Sub
        End If
     End If
End If

Open in new window


I would also rename the variable.  So instead of bAdd, I would call it bSave
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
yoducatiAuthor Commented:
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.
0
PatHartmanCommented:
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.
0
yoducatiAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.