I have probably a very simple query but cannot find the answer on google anywhere. Does the before update of a form fire before or after the OnClick event of a button? It makes sense to me that it would as it should update the record before navigation away which the button is being used for?
22 6281
The only time that I could think of that both of these events would be called by a single user action would be by clicking on a button placed on a Continuous Form where the Record has been edited and the Button that is clicked is located on another Record. I believe in this case, the BeforeUpdate for the Form will be called before the OnClick for the Button because the Focus would need to be set to the Button, which is on a different Record, so the current changes would need to be saved before moving the other Record.
This is different than standard Windows Bubbling Event programming where the button click event would fire on click, if it's not handled then the Form would receive the click event, then Access would get it, then Windows. (Tunneling Events are the exact opposite where Windows gets the Event first and the Button last)
If you have a different scenario in mind, please let us know.
I have a data entry form which uses the before update to ensure the data entered into the record will be valid. On the form, I also have some navigation buttons, a return to main switchboard button and an add record button.
Essentially, I don't want the data to be autosaved into the table unless the add record button is pressed or if a navigation button is pressed when the user is prompted that the record has not yet been added.
So I'm trying to work out a way where the validation rules will be implemented before navigating away from the record so I was hoping the before update event would take place then the on click event.
Yep, that is the best use of the form BeforeUpdate event. When you are in the BeforeUpdate event and the data fails validation, set Cancel to True.
Another way to validate your data is on the Table level. Table Validation isn't as flexible and can be quite frustrating at times.
A third option is to create an Unbound Form and insert records after they are validated. This would allow you to tightly control what gets entered, with the caveat of working best only with Inserts.
I previously had table validation but it didn't work as I needed to allow 1 of 2 fields to be populated for a record to be saved.
Currently the navigation away from an "incomplete" record works fine but the problem is when the user creates a partial record then clicks on my main menu button. I need the message box prompt to come up before navigating away from the form. My code for the Before Update event is below, where validForm is a function I'm using to check whether the validation rules have been fulfilled: -
Private Sub Form_BeforeUpdate(Cancel As Integer)
-
Dim strMsg As String
-
Dim strMsg2 As String
-
Dim intResponse As Integer
-
Dim intResponse2 As Integer
-
-
strMsg = "You have not saved this Record. Do you wish to Save this Record " & _
-
"with those changes?" & vbCrLf & vbCrLf & "Click Yes to Save changes, No to Discard" & _
-
" or Cancel to Return to the Record?"
-
strMsg2 = "Record is incomplete, continue without saving?"
-
-
intResponse = MsgBox(strMsg, vbQuestion + vbYesNoCancel, "Prompt to Save Record")
-
-
If intResponse = vbYes Then
-
validForm
-
If validForm = True Then
-
Exit Sub
-
ElseIf validForm = False Then
-
intResponse2 = MsgBox(strMsg2, vbYesNo, "Incomplete Record")
-
If intResponse2 = vbYes Then
-
DoCmd.RunCommand acCmdUndo
-
Exit Sub
-
ElseIf intResponse2 = vbNo Then
-
Cancel = True
-
End If
-
End If
-
ElseIf intResponse = vbNo Then
-
DoCmd.RunCommand acCmdUndo
-
ElseIf intResponse = vbCancel Then
-
Cancel = True
-
End If
-
End Sub
-
And the Main Menu button is: -
Private Sub cmdReturnToMainEdit_Click()
-
DoCmd.OpenForm "frmSwitchboardAdmin"
-
DoCmd.Close acForm, "frmAdd"
-
End Sub
-
So currently when the button is clicked, the switchboard will open, the form will close and then the message box pops up which isn't useful as it won't have any effect.
I think I see what you are trying to do.
You'll not want to ask your user if they want to save their record in the BeforUpdate Event. This Event is fired by Access when it determines that it's time to save the record. This could be by record navigation, or someone clicking the save button. So at this point, the record is already half way on it's way to being saved. So I would move the Save Prompt into a function.
The Form_BeforeUpdate Event and the cmdReturnToMainEdit_Click Event are mutually exclusive; one won't fire the other, unless you code it to.
Before you open your Switchboard and close the Form, is when you'll want to prompt to save. That way when the Save is executed, the validation in the BeforeUpdate event will be triggered. Here is a generic function to Prompt the user if they would like to save their changes. You can spruce it up with some of the code you have already written (there maybe a better way of doing this, it is just something I had laying around): - Private Function promptSave() As Boolean
-
On Error Resume Next
-
If Me.Dirty Then
-
If MsgBox("Would you like to save?", vbQuestion + vbYesNo) = vbYes Then
-
DoCmd.RunCommand acCmdSaveRecord
-
Else
-
DoCmd.RunCommand acCmdUndo
-
End If
-
End If
-
promptSave = (Not Me.Dirty)
-
End Function
Having this function, I would modify cmdReturnToMainEdit like this: - Private Sub cmdReturnToMainEdit_Click()
-
If promptSave() Then
-
DoCmd.OpenForm "frmSwitchboardAdmin"
-
DoCmd.Close acForm, "frmAdd"
-
End If
-
End Sub
You could then put promptSave() in the OnClose event of the Form just for fun. - Private Sub Form_Close()
-
Call promptSave
-
End Sub
This works perfectly thank you. The only problem now is how do I get my navigation buttons to run the validation rules that I put in place as I have taken them out of the Form Before Update Event. I have tried saving the code I had in the Before Update event into a public function called validateForm then using this code for one of the buttons: -
Private Sub Command34_Click()
-
On Error GoTo Err_Command34_Click
-
-
validateForm
-
DoCmd.GoToRecord , , acNext
-
-
Exit_Command34_Click:
-
Exit Sub
-
-
Err_Command34_Click:
-
Resume Exit_Command34_Click
-
-
End Sub
-
it returns an error saying "Compile error: Argument not optional".
When calling a Function you'll need to either put the return value into a variable, use it in an Expression, or use the Call Statement: - Dim bReturn As Boolean
-
bReturn = validateForm()
-
-
if validateForm() then
-
endif
-
-
Call validateForm
Also, I would move the validation call into BeforeUpdate Event and if the validation fails, set Cancel=True. That way no matter how the record is being saved, it gets validated.
Then you can change your navigation buttons to: - If promptSave() Then
-
DoCmd.GoToRecord , , acNext
-
End If
This way, the Save prompt will be displayed and the validation will be run before the record pointer even tries to move.
Ok, I'm not sure if I follow so can I spell out what I understand and then you correct me where I'm wrong?
So the user clicks a navigation button. This calls promptSave which checks the state of the form. If the form is dirty, then it opens a message box asking whether to save or not. Clicking yes will “save” the data and then navigate as intended. Clicking no removes the data and then navigates as intended.
I say “save” the data, because this should trigger the before update event of the form to check whether the data is valid. If it is valid then saving and navigation occurs as normal. If invalid, another message box triggers asking whether the user would like to navigate without saving or return to the form to 'complete' the record. Clicking yes will again remove the data then navigate. Clicking no will activate the cancel = True event of the Before Update and will return to the form.
You've got it.
The validation is in a place that it will be called on the Record update no matter how the save is being called. Also, calling promptSave() when you can foresee that the current record may need to be saved will manage your users expectations and minimize their surprises.
So I have put my validation code in the Before Update, and updated the navigation controls to for example: -
Private Sub Command34_Click()
-
On Error GoTo Err_Command34_Click
-
-
If promptSave() Then
-
DoCmd.GoToRecord , , acNext
-
End If
-
-
Exit_Command34_Click:
-
Exit Sub
-
-
Err_Command34_Click:
-
Resume Exit_Command34_Click
-
-
End Sub
-
But now on my form whenever I do anything I get the error of "The expression On Dirty you entered as the event property setting produced the following error: Procedure declaration does not match description of event or procedure having the same name"
That error comes from having a standard method or procedure declaration with the wrong signature. For example Access expects: - Private Sub Form_BeforeUpdate(Cancel As Integer)
for the BeforeUpdate event, but it's easy enough when copy and pasting to remove the arguments and end up with: - Private Sub Form_BeforeUpdate()
The signature isn't what Access (Visual Basic) is expecting, it is expecting the Cancel argurment, so you get that error.
To find out what line is giving you trouble, go into the Visual Basic Code Editor and from the Debug Menu, select Compile. This should take you to the offending line. Once you know which procedure is giving you trouble, you can rename it and then use Access to recreate it the way you would normally create it to find out what it should look like so you can fix it.
Ah that solved it! I had put cancel instead of Cancel...
This has now solved my issues so thanks for your help jforbes!
Sorry to revive an old thread but this question is directly related to all of this stuff!
When a user opens the form, if they click on the return to main menu button before typing anything the promptSave function automatically fires even though they haven't changed anything on the form. Does this mean the form is automatically dirty upon open because I can't work out why else the function should fire!
This also only happens before the first record is added. E.g. it works fine if the user adds a record then clicks the button to return to the menu as the message box doesn't appear.
Rereading the post, it looks like we explicitly put the PromptSave() call in the Form's Exit function, so it should be called on Exit no matter how the Form is opened. But since the function is written to only stir up trouble when the Form is Dirty, it should pass through it unless some data has changed.
But I wouldn't expect the Form to be Dirty initially on open. So you users should be able to Open your Form and then turn right around and Close it even though the SavePrompt function fires. If this can't be done, I would look in to reasons why the Form is Dirty.
This is what I thought also. The users open the form, click the return to menu button, the function fires but the message box appears. This also happens if the user were to press the previous record button but not the first, next or last record buttons.
How can the form be dirty if nothing has been changed on it? Does clicking the button make it dirty (which makes no sense as no record has been changed!)?
I've just had another look at it and my last post is wrong. When the form is opened, when a user tries to press any of the navigation buttons or the return to main menu button the prompt message box appears.
What reasons could there be for the form to be dirty?
I wouldn't expect it to be Dirty unless a value in one of your bound controls is changed. This shouldn't happen just by navigating records natively. I remember that you have some custom navigation buttons, possibly there is something in the code there that changes a value. I would also check the OnCurrent event as this is a common place to put code.
Things you can do: - Put some Breakpoints through out your code and use ?Me.Dirty in the Immediate Window or add a watch to Me.Dirty to see its value.
- You can try adding a watch for Me.Dirty with the "break when value changes" option, but it probably wont work. I don't know why, it's just not reliable.
- If you want to post your code, we can take a look and see if we can spot it.
I'll post it here and see what you can find. - Option Compare Database
-
-
Private Sub AddNewRecordButton_Click()
-
Dim strPrompt As String
-
strPrompt = ""
-
-
'Check if there is a forename
-
If IsNull(Forms!frmAdd!txtForename) Then
-
strPrompt = "- Forename"
-
End If
-
-
'Check if there is a surname
-
If IsNull(Forms!frmAdd!txtSurname) Then
-
If strPrompt = "" Then
-
strPrompt = "- Surname"
-
Else
-
strPrompt = strPrompt & vbCrLf & "- Surname"
-
End If
-
End If
-
-
'The record must contain at least a DoB or a NHS
-
'Case 1 - when there is no DoB or NHS
-
If IsNull(Forms!frmAdd!txtDoB) And IsNull(Forms!frmAdd!txtNHS) Then
-
If strPrompt = "" Then
-
strPrompt = "- Either Date of Birth or NHS Number"
-
Else
-
strPrompt = strPrompt & vbCrLf & _
-
"- Either Date of Birth or NHS Number"
-
End If
-
End If
-
-
'Case 2 - is a NHS but no DoB
-
If IsNull(Forms!frmAdd!txtDoB) = True And IsNull(Forms!frmAdd!txtNHS) = False Then
-
If Len(Forms!frmAdd!txtNHS) <> 10 Then
-
If strPrompt = "" Then
-
strPrompt = "- A valid NHS Number"
-
Else
-
strPrompt = strPrompt & vbCrLf & _
-
"- A valid NHS Number"
-
End If
-
End If
-
End If
-
-
'Case 3 - is a DoB but no NHS
-
If IsNull(Forms!frmAdd!txtDoB) = False And IsNull(Forms!frmAdd!txtNHS) = True Then
-
If Forms!frmAdd!txtDoB >= Date Then
-
If strPrompt = "" Then
-
strPrompt = "- A valid Date of Birth"
-
Else
-
strPrompt = strPrompt & vbCrLf & _
-
"- A valid Date of Birth"
-
End If
-
End If
-
End If
-
-
'Case 4 - when there is both a DoB and a NHS - check for valid
-
If IsNull(Forms!frmAdd!txtDoB) = False And IsNull(Forms!frmAdd!txtNHS) = False Then
-
If Forms!frmAdd!txtDoB >= Date Then
-
If strPrompt = "" Then
-
strPrompt = "- A valid Date of Birth"
-
Else
-
strPrompt = strPrompt & vbCrLf & _
-
"- A valid Date of Birth"
-
End If
-
End If
-
-
If Len(Forms!frmAdd!txtNHS) <> 10 Then
-
If strPrompt = "" Then
-
strPrompt = "- A valid NHS Number"
-
Else
-
strPrompt = strPrompt & vbCrLf & _
-
"- A valid NHS Number"
-
End If
-
End If
-
End If
-
-
'Check if there is a KMN number
-
If IsNull(Forms!frmAdd!txtKMN) Then
-
If strPrompt = "" Then
-
strPrompt = "- KMN Number"
-
Else
-
strPrompt = strPrompt & vbCrLf & _
-
"- KMN Number"
-
End If
-
End If
-
-
'Compare variable string and determine if record is complete
-
If strPrompt = "" Then
-
DoCmd.GoToRecord , , acNewRec
-
Else
-
MsgBox "This record is incomplete." & vbCrLf & "You have not included:" & vbCrLf & strPrompt, vbExclamation + vbOKOnly, "Adding Record: Incomplete"
-
End If
-
End Sub
-
-
Private Sub cmdReturnToMainEdit_Click()
-
If promptSave() Then
-
DoCmd.OpenForm "frmSwitchboardAdmin"
-
DoCmd.Close acForm, "frmAdd"
-
End If
-
End Sub
-
-
Private Sub Command31_Click()
-
On Error GoTo Err_Command31_Click
-
-
If promptSave() Then
-
DoCmd.GoToRecord , , acFirst
-
End If
-
-
Exit_Command31_Click:
-
Exit Sub
-
-
Err_Command31_Click:
-
Resume Exit_Command31_Click
-
-
End Sub
-
Private Sub Command32_Click()
-
On Error GoTo Err_Command32_Click
-
-
If promptSave() Then
-
DoCmd.GoToRecord , , acLast
-
End If
-
-
Exit_Command32_Click:
-
Exit Sub
-
-
Err_Command32_Click:
-
Resume Exit_Command32_Click
-
-
End Sub
-
Private Sub Command33_Click()
-
On Error GoTo Err_Command33_Click
-
-
If promptSave() Then
-
DoCmd.GoToRecord , , acPrevious
-
End If
-
-
Exit_Command33_Click:
-
Exit Sub
-
-
Err_Command33_Click:
-
Resume Exit_Command33_Click
-
-
End Sub
-
Private Sub Command34_Click()
-
On Error GoTo Err_Command34_Click
-
-
If promptSave() Then
-
DoCmd.GoToRecord , , acNext
-
End If
-
-
Exit_Command34_Click:
-
Exit Sub
-
-
Err_Command34_Click:
-
Resume Exit_Command34_Click
-
-
End Sub
-
-
Private Function promptSave() As Boolean
-
On Error Resume Next
-
-
Dim strMsgPrompt As String
-
Dim intMsgResponse As Integer
-
-
strMsgPrompt = "You are about to navigate away from this page but the record has not been saved." & vbCrLf & "Would you like to save the record?"
-
-
If Me.Dirty Then
-
intMsgResponse = MsgBox(strMsgPrompt, vbQuestion + vbYesNoCancel, "Navigation: Unsaved Record")
-
If intMsgResponse = vbYes Then
-
DoCmd.RunCommand acCmdSaveRecord
-
ElseIf intMsgResponse = vbNo Then
-
DoCmd.RunCommand acCmdUndo
-
ElseIf intMsgResponse = vbCancel Then
-
DoCmd.CancelEvent
-
End If
-
End If
-
promptSave = (Not Me.Dirty)
-
End Function
-
-
Private Sub Form_BeforeUpdate(Cancel As Integer)
-
Dim strMsg As String
-
Dim strMsg2 As String
-
Dim intResponse As Integer
-
Dim intResponse2 As Integer
-
-
If validForm = True Then
-
Exit Sub
-
ElseIf validForm = False Then
-
strMsg2 = "This record is incomplete." & vbCrLf & "You have not included:" & vbCrLf & strInvalid & vbCrLf & "Continue navigating without saving?"
-
intResponse2 = MsgBox(strMsg2, vbQuestion + vbYesNo, "Navigation: Incomplete Record")
-
If intResponse2 = vbYes Then
-
DoCmd.RunCommand acCmdUndo
-
Exit Sub
-
ElseIf intResponse2 = vbNo Then
-
Cancel = True
-
End If
-
End If
-
End Sub
-
-
Private Sub Form_Current()
-
If Me.NewRecord Then
-
Me.AddNewRecordButton.Caption = "Add Record"
-
Else
-
Me.AddNewRecordButton.Caption = "Save Record"
-
End If
-
End Sub
-
-
Private Sub txtForename_LostFocus()
-
Me.txtForename = UCase(Left(Me.txtForename, 1)) & Mid(Me.txtForename, 2)
-
End Sub
-
-
Private Sub txtSurname_LostFocus()
-
Me.txtSurname = UCase(Left(Me.txtSurname, 1)) & Mid(Me.txtSurname, 2)
-
End Sub
-
First thing I would try is to change the LostFocus Events to AfterUpdate Events, that way data isn't being changed just by the fields getting focus. I would also change them to test to see if they need to modify data. This is the long way around and it isn't such a big deal, but it is a good practice in a case like this and it might make a difference here: - Private Sub txtForename_AfterUpdate()
-
If Me.txtForename <> UCase(Left(Me.txtForename, 1)) Then Me.txtForename = UCase(Left(Me.txtForename, 1))
-
End Sub
-
-
Private Sub txtSurname_AfterUpdate()
-
If Me.txtSurname <> UCase(Left(Me.txtSurname, 1)) & Mid(Me.txtSurname, 2) Then Me.txtSurname = UCase(Left(Me.txtSurname, 1)) & Mid(Me.txtSurname, 2)
-
End Sub
-
This has solved the message box problem... but the Upper case stuff didn't work XD
So instead I moved it into the validation procedure so that if the strPrompt was "", then it would update them to upper case then return the function as true and it seems to work ok! Thanks for all the help again!
String comparisons in code are not case sensitive by default. You have to use Option Compare Binary to do that or the StrComp() function with the binary flag set.
Nice one Rabbit! I didn't really pay attention to what was being compared. Shame on me.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Lauren Quantrell |
last post by:
The Access form's control box has available an "X" to close and the
min/max buttons.
Is it possible to customize the control box to include other options
and to isnert my own icons/command...
|
by: Mullin Yu |
last post by:
i have created a windows application, but i didn't use any form and control
indeed (like console application).
by default, there's a Form1 at the project, can i remove it or hide it so
that it...
|
by: Not Available |
last post by:
I have windows form user control which I load into the browser via this ascx web user control and it works. The process is explained at this link:...
|
by: Richard |
last post by:
Hello
MyMainWebPage.aspx contains MyUserControl.ascx
MyUserControl.ascx has a DataTable Property MyDataTable
|
by: Don |
last post by:
I have a custom control with a custom designer. This designer allows
me to open a dialog to define custom properties the way 'Property
Builder' works for the datagrid. After I set the properties...
|
by: Trint Smith |
last post by:
I know that this may belong in the asp group, but I want to
programatically control the web form image control.
For example, I want to let the program choose the image url at load time
and the...
|
by: Lisa Jones |
last post by:
Hi
Can someone tell me How do you get selected date form MonthCalendar control
Thanks so muc
Lisa
|
by: Rajesh Kumar Choudhary |
last post by:
Hi,
I want to use the system.windows.form.datagrid control present in .net 2005
or 2003. Is it possible to use this? Please let me know if it is not
supported.
I have seen and used the classes...
|
by: arunbalait |
last post by:
Hi all
Another childhood doubt
Is VB form a control?
Can we use form as object?
If so how form acts as object?
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |