Solved

Custom Control Button on Access Form

Posted on 2014-11-24
14
362 Views
Last Modified: 2014-11-25
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.
0
Comment
Question by:yoducati
  • 9
  • 5
14 Comments
 
LVL 34

Expert Comment

by:PatHartman
ID: 40462255
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
 

Author Comment

by:yoducati
ID: 40462273
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
 

Author Comment

by:yoducati
ID: 40462284
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
 
LVL 34

Expert Comment

by:PatHartman
ID: 40462484
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
 

Author Comment

by:yoducati
ID: 40462523
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
 

Author Comment

by:yoducati
ID: 40462768
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
 

Author Comment

by:yoducati
ID: 40462817
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 34

Expert Comment

by:PatHartman
ID: 40462862
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
 

Author Comment

by:yoducati
ID: 40462879
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
 

Author Comment

by:yoducati
ID: 40462924
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
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40462928
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
 

Author Comment

by:yoducati
ID: 40463022
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
 
LVL 34

Expert Comment

by:PatHartman
ID: 40464765
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
 

Author Comment

by:yoducati
ID: 40465043
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

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Read about achieving the basic levels of HRIS security in the workplace.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now