Fred Webb
asked on
New Record command not firing
I have a save button on my access form that first validates that all required fields are filled in and if so to save the record and go to a new record, but the new record portion of my code doesn't seem to fire. I have attached my code
Private Sub btnSave_Click()
'If MsgBox("Do you want to SAVE the record (or the change)", vbYesNo, "Save Record...") = vbNo Then
' Me.Firstname.SetFocus
' Me.Company.SetFocus
' Me.Undo
' Cancel = True
' Validates that Control has Data for required feilds
If Len(Firstname) = 0 Or IsNull(Firstname) = True Then
Call MsgBox("You must enter First Name", vbExclamation, "Add Firstname")
Me.Company.SetFocus
Me.Firstname.SetFocus
Cancel = True
ElseIf Len(Lastname) = 0 Or IsNull(Lastname) = True Then
Call MsgBox("You must enter Last Name", vbExclamation, "Add Lastname")
Me.Company.SetFocus
Me.Lastname.SetFocus
Cancel = True
ElseIf Len(Address1) = 0 Or IsNull(Address1) = True Then
Call MsgBox("You must enter Address1", vbExclamation, "Add Address1")
Me.Company.SetFocus
Me.Address1.SetFocus
Cancel = True
ElseIf Len(City) = 0 Or IsNull(City) = True Then
Call MsgBox("You must enter City", vbExclamation, "Add City")
Me.Company.SetFocus
Me.City.SetFocus
Cancel = True
ElseIf Len(State) = 0 Or IsNull(State) = True Then
Call MsgBox("You must select a State", vbExclamation, "Add State")
Me.Company.SetFocus
Me.cboState.SetFocus
Cancel = True
ElseIf Len(Zip) = 0 Or IsNull(Zip) = True Then
Call MsgBox("You must enter a Zip", vbExclamation, "Add Zip")
Me.Company.SetFocus
Me.Zip.SetFocus
Cancel = True
ElseIf Len(Phone) = 0 Or IsNull(Phone) = True Then
Call MsgBox("You must enter a Phone No", vbExclamation, "Add Phone")
Me.Company.SetFocus
Me.Phone.SetFocus
Cancel = True
ElseIf Len(cboCSR) = 0 Or IsNull(cboCSR) = True Then
Call MsgBox("You must select a CSR", vbExclamation, "Add CSR")
Me.Company.SetFocus
Me.cboCSR.SetFocus
Cancel = True
ElseIf Len(cboMod) = 0 Or IsNull(cboMod) = True Then
Call MsgBox("You must select a Model", vbExclamation, "Add Model")
Me.Company.SetFocus
Me.cboMod.SetFocus
Cancel = True
ElseIf Len(Serial) = 0 Or IsNull(Serial) = True Then
Call MsgBox("You must select a Serial Number", vbExclamation, "Add Serial")
Me.Company.SetFocus
Me.Serial.SetFocus
ElseIf Len(ProblemDesc) = 0 Or IsNull(ProblemDesc) = True Then
Call MsgBox("You must select a Problem Description", vbExclamation, "Add ProblemDesc")
Me.Company.SetFocus
Me.ProblemDesc.SetFocus
'Save the record.
RunCommand acCmdSaveRecord
'Load a new record after the save.
DoCmd.GoToRecord , , acNewRec
End If
'End If
End Sub
there is a more elegant way of coding this.
first, in the design view of your form, select all the controls that are required to be fill in
open the Property sheet and select the Other tab
in the Tag property place REQ or Required
then see this threads
https://www.experts-exchange.com/questions/22653980/MS-Access-Forms-Cycle-through-each-field-on-close-to-check-for-nulls.html?anchorAnswerId=ccepted-solution#accepted-solution
https://www.experts-exchange.com/questions/28201725/Looping-Through-Controls.html?anchorAnswerId=ccepted-solution#accepted-solution
first, in the design view of your form, select all the controls that are required to be fill in
open the Property sheet and select the Other tab
in the Tag property place REQ or Required
then see this threads
https://www.experts-exchange.com/questions/22653980/MS-Access-Forms-Cycle-through-each-field-on-close-to-check-for-nulls.html?anchorAnswerId=ccepted-solution#accepted-solution
https://www.experts-exchange.com/questions/28201725/Looping-Through-Controls.html?anchorAnswerId=ccepted-solution#accepted-solution
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You're referring to a Cancel variable, but there's not one available. You can use the If ElseIf structure if you declare that variable, and then move the "save record" stuff out of the If - ElseIf - End If structure:
Private Sub btnSave_Click()
'/ you must declare a Cancel variable
Dim cancel as Boolean
'If MsgBox("Do you want to SAVE the record (or the change)", vbYesNo, "Save Record...") = vbNo Then
' Me.Firstname.SetFocus
' Me.Company.SetFocus
' Me.Undo
' Cancel = True
' Validates that Control has Data for required feilds
If Len(Firstname) = 0 Or IsNull(Firstname) = True Then
Call MsgBox("You must enter First Name", vbExclamation, "Add Firstname")
Me.Company.SetFocus
Me.Firstname.SetFocus
Cancel = True
ElseIf Len(Lastname) = 0 Or IsNull(Lastname) = True Then
Call MsgBox("You must enter Last Name", vbExclamation, "Add Lastname")
Me.Company.SetFocus
Me.Lastname.SetFocus
Cancel = True
ElseIf Len(Address1) = 0 Or IsNull(Address1) = True Then
Call MsgBox("You must enter Address1", vbExclamation, "Add Address1")
Me.Company.SetFocus
Me.Address1.SetFocus
Cancel = True
ElseIf Len(City) = 0 Or IsNull(City) = True Then
Call MsgBox("You must enter City", vbExclamation, "Add City")
Me.Company.SetFocus
Me.City.SetFocus
Cancel = True
ElseIf Len(State) = 0 Or IsNull(State) = True Then
Call MsgBox("You must select a State", vbExclamation, "Add State")
Me.Company.SetFocus
Me.cboState.SetFocus
Cancel = True
ElseIf Len(Zip) = 0 Or IsNull(Zip) = True Then
Call MsgBox("You must enter a Zip", vbExclamation, "Add Zip")
Me.Company.SetFocus
Me.Zip.SetFocus
Cancel = True
ElseIf Len(Phone) = 0 Or IsNull(Phone) = True Then
Call MsgBox("You must enter a Phone No", vbExclamation, "Add Phone")
Me.Company.SetFocus
Me.Phone.SetFocus
Cancel = True
ElseIf Len(cboCSR) = 0 Or IsNull(cboCSR) = True Then
Call MsgBox("You must select a CSR", vbExclamation, "Add CSR")
Me.Company.SetFocus
Me.cboCSR.SetFocus
Cancel = True
ElseIf Len(cboMod) = 0 Or IsNull(cboMod) = True Then
Call MsgBox("You must select a Model", vbExclamation, "Add Model")
Me.Company.SetFocus
Me.cboMod.SetFocus
Cancel = True
ElseIf Len(Serial) = 0 Or IsNull(Serial) = True Then
Call MsgBox("You must select a Serial Number", vbExclamation, "Add Serial")
Me.Company.SetFocus
Me.Serial.SetFocus
ElseIf Len(ProblemDesc) = 0 Or IsNull(ProblemDesc) = True Then
Call MsgBox("You must select a Problem Description", vbExclamation, "Add ProblemDesc")
Me.Company.SetFocus
Me.ProblemDesc.SetFocus
End If
If Not cancel Then
RunCommand acCmdSaveRecord
'Load a new record after the save.
DoCmd.GoToRecord , , acNewRec
End If
End Sub
Private Sub btnSave_Click()
'/ you must declare a Cancel variable
Dim cancel as Boolean
'If MsgBox("Do you want to SAVE the record (or the change)", vbYesNo, "Save Record...") = vbNo Then
' Me.Firstname.SetFocus
' Me.Company.SetFocus
' Me.Undo
' Cancel = True
' Validates that Control has Data for required feilds
If Len(Firstname) = 0 Or IsNull(Firstname) = True Then
Call MsgBox("You must enter First Name", vbExclamation, "Add Firstname")
Me.Company.SetFocus
Me.Firstname.SetFocus
Cancel = True
ElseIf Len(Lastname) = 0 Or IsNull(Lastname) = True Then
Call MsgBox("You must enter Last Name", vbExclamation, "Add Lastname")
Me.Company.SetFocus
Me.Lastname.SetFocus
Cancel = True
ElseIf Len(Address1) = 0 Or IsNull(Address1) = True Then
Call MsgBox("You must enter Address1", vbExclamation, "Add Address1")
Me.Company.SetFocus
Me.Address1.SetFocus
Cancel = True
ElseIf Len(City) = 0 Or IsNull(City) = True Then
Call MsgBox("You must enter City", vbExclamation, "Add City")
Me.Company.SetFocus
Me.City.SetFocus
Cancel = True
ElseIf Len(State) = 0 Or IsNull(State) = True Then
Call MsgBox("You must select a State", vbExclamation, "Add State")
Me.Company.SetFocus
Me.cboState.SetFocus
Cancel = True
ElseIf Len(Zip) = 0 Or IsNull(Zip) = True Then
Call MsgBox("You must enter a Zip", vbExclamation, "Add Zip")
Me.Company.SetFocus
Me.Zip.SetFocus
Cancel = True
ElseIf Len(Phone) = 0 Or IsNull(Phone) = True Then
Call MsgBox("You must enter a Phone No", vbExclamation, "Add Phone")
Me.Company.SetFocus
Me.Phone.SetFocus
Cancel = True
ElseIf Len(cboCSR) = 0 Or IsNull(cboCSR) = True Then
Call MsgBox("You must select a CSR", vbExclamation, "Add CSR")
Me.Company.SetFocus
Me.cboCSR.SetFocus
Cancel = True
ElseIf Len(cboMod) = 0 Or IsNull(cboMod) = True Then
Call MsgBox("You must select a Model", vbExclamation, "Add Model")
Me.Company.SetFocus
Me.cboMod.SetFocus
Cancel = True
ElseIf Len(Serial) = 0 Or IsNull(Serial) = True Then
Call MsgBox("You must select a Serial Number", vbExclamation, "Add Serial")
Me.Company.SetFocus
Me.Serial.SetFocus
ElseIf Len(ProblemDesc) = 0 Or IsNull(ProblemDesc) = True Then
Call MsgBox("You must select a Problem Description", vbExclamation, "Add ProblemDesc")
Me.Company.SetFocus
Me.ProblemDesc.SetFocus
End If
If Not cancel Then
RunCommand acCmdSaveRecord
'Load a new record after the save.
DoCmd.GoToRecord , , acNewRec
End If
End Sub
ASKER
Thanks to all who have responded.
Rey, I am intrigued by the code in the second link you provided, would I have to recreate that for each control or simply tag each control with REQ and it will know which control is blank
Rey, I am intrigued by the code in the second link you provided, would I have to recreate that for each control or simply tag each control with REQ and it will know which control is blank
Private Sub Form_BeforeUpdate(Cancel as integer)
Dim strCtlName As String, NullCtl As String, Msg As String, ctl As Control
strCtlName = ""
For Each ctl In Me.Controls
If ctl.Tag = "REQ" Then
If Len(Me(ctl.Name) & "") = 0 Then
strCtlName = strCtlName & ctl.Name & ";"
End If
End If
Next
If Len(strCtlName) > 0 Then
NullCtl = Mid(strCtlName, 1, InStr(strCtlName, ";") - 1)
Msg = "Please fill out the required fields!" & vbCr & vbCr
Msg = Msg & Left(strCtlName, Len(strCtlName) - 1)
MsgBox Msg, vbCritical, "Required Fields"
Cancel=True
Me(NullCtl).SetFocus
Exit Sub
End If
end sub
Yes, It's a loop. It looks at every control and looks a every tag for "REQ"
For Each ctl In Me.Controls
If ctl.Tag = "REQ" Then
If Len(Me(ctl.Name) & "") = 0 Then
strCtlName = strCtlName & ctl.Name & ";"
End If
End If
Next
It then does some nice legerdemain -- which may not always play nice -- to coerce the controls value to a string and test if that string is longer than zero. It then msgbox's about them.
This works.
I don't like it.
It does not customize the message
It tests only for a character in the control, not a valid value ("a" in a control that requires an integer, e.g.)
It throws the focus to the last invalid control found -- but you can't control the order that Me.Controls is checked.
It depends upon the Tag property and therefore splits the functionality between the code and the control's properties.
It works and is commonly used.
Given its flaws, a well-written validation function is a better thing to leave a successor.
YMMV
For Each ctl In Me.Controls
If ctl.Tag = "REQ" Then
If Len(Me(ctl.Name) & "") = 0 Then
strCtlName = strCtlName & ctl.Name & ";"
End If
End If
Next
It then does some nice legerdemain -- which may not always play nice -- to coerce the controls value to a string and test if that string is longer than zero. It then msgbox's about them.
This works.
I don't like it.
It does not customize the message
It tests only for a character in the control, not a valid value ("a" in a control that requires an integer, e.g.)
It throws the focus to the last invalid control found -- but you can't control the order that Me.Controls is checked.
It depends upon the Tag property and therefore splits the functionality between the code and the control's properties.
It works and is commonly used.
Given its flaws, a well-written validation function is a better thing to leave a successor.
YMMV
<I don't like it.>
then don't use it.
@skull52
just simply tag each control with REQ and it will know which control is blank..
and this function is a general function to test if an entry was made or not in a control.
it is not a do everything to check the content of a control.
i have one like that but it was requested and paid for by a client and can't post it here
then don't use it.
@skull52
just simply tag each control with REQ and it will know which control is blank..
and this function is a general function to test if an entry was made or not in a control.
it is not a do everything to check the content of a control.
i have one like that but it was requested and paid for by a client and can't post it here
@Rey
<I don't like it.>
then don't use it.
I don't.
And I don't recommend it.
And I point out why I don't recommend it.
it is not a do everything to check the content of a control.
Everywhere you go, every platform you use, for as long as you are in the business, you should be validating user input before doing anything with it. That's a good, life-long habit to cultivate.
The Bobby Tables cartoon
http://xkcd.com/327/
<I don't like it.>
then don't use it.
I don't.
And I don't recommend it.
And I point out why I don't recommend it.
it is not a do everything to check the content of a control.
Everywhere you go, every platform you use, for as long as you are in the business, you should be validating user input before doing anything with it. That's a good, life-long habit to cultivate.
The Bobby Tables cartoon
http://xkcd.com/327/
ASKER
Rey, I tested it and it works just fine, I don't have content validation on any of the controls so that is not an issue, the message informs the user they have left a required field blank and which one(s), If you leave more than one required field blank and you fix one and not the others it prompts and places focus on each one progressively. I have no controls that are integers, so no issue there either.
The click event of your save button is the WRONG place to do validation. Your save button should force the record to save and that will fire the FORM's BeforeUpdate event and the FORM's BeforeUpdate event is where the validation code actually belongs. That way, no matter what the user does, the code will fire if Access decides it needs to save the current record.
Split the conditions into individual If statements as Nick suggested. the conditions are not dependent on each other so logically it is incorrect to nest them.
If all you want to check for is presence, then the code posted by Rey is OK but it needs to be included in each form. I prefer to put this type of code in a standard code module. Then I call it from the BeforeUpdate event of the form and pass in a reference to the form object.
Split the conditions into individual If statements as Nick suggested. the conditions are not dependent on each other so logically it is incorrect to nest them.
If all you want to check for is presence, then the code posted by Rey is OK but it needs to be included in each form. I prefer to put this type of code in a standard code module. Then I call it from the BeforeUpdate event of the form and pass in a reference to the form object.
If CheckForEmpty(Me) = true Then
''' at least one field is empty
Cancel = True
Exit Sub
End If
Public Function CheckForEmpty(frm as Form) as Boolean
CheckForEmpty = False
Dim ctl As Control
For Each ctl In frm.Controls
If ctl.Tag = "REQ" Then
If ctl.Tag & "" = "" Then
Msgbox ctl.Caption & " is required. Please enter a value.",vbOKOnly
ctl.SetFocus
CheckForEmpty = True
Exit Sub
End if
End If
Next ctl
Set ctl = Nothing
Exit Sub
ASKER
Pat, To explore all avenues I tried as nick suggested and Split the conditions into individual If statements and put that validation in the BeforeUpdate event and the Save and New record code on the On Click event of the save button, if a control is blank it fires the validation with a message identifying the blank control, but it throws a Debug Error 2501 (the run command was canceled) in procedure btnSave_click at that point you have to click OK on the error it will then put focus on the blank control and after adding data it will save. However I can't have that error in production.
Save button
Before Update
Save button
Private Sub btnSave_Click()
'Save the record.
On Error GoTo btnSave_Click_Error
RunCommand acCmdSaveRecord
'Load a new record after the save.
DoCmd.GoToRecord , , acNewRec
On Error GoTo 0
Exit Sub
btnSave_Click_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure btnSave_Click of VBA Document Form_ISSC"
End Sub
Before Update
Private Sub Form_BeforeUpdate(Cancel As Integer)
' Validates that Control has Data for required feilds
If Len(Firstname) = 0 Or IsNull(Firstname) = True Then
Call MsgBox("You must enter First Name", vbExclamation, "Add Firstname")
Me.Company.SetFocus
Me.Firstname.SetFocus
Cancel = True
Exit Sub
End If
If Len(Lastname) = 0 Or IsNull(Lastname) = True Then
Call MsgBox("You must enter Last Name", vbExclamation, "Add Lastname")
Me.Company.SetFocus
Me.Lastname.SetFocus
Cancel = True
Exit Sub
End If
If Len(Address1) = 0 Or IsNull(Address1) = True Then
Call MsgBox("You must enter Address1", vbExclamation, "Add Address1")
Me.Company.SetFocus
Me.Address1.SetFocus
Cancel = True
Exit Sub
End If
If Len(City) = 0 Or IsNull(City) = True Then
Call MsgBox("You must enter City", vbExclamation, "Add City")
Me.Company.SetFocus
Me.City.SetFocus
Cancel = True
Exit Sub
End If
If Len(State) = 0 Or IsNull(State) = True Then
Call MsgBox("You must select a State", vbExclamation, "Add State")
Me.Company.SetFocus
Me.cboState.SetFocus
Cancel = True
Exit Sub
End If
If Len(Zip) = 0 Or IsNull(Zip) = True Then
Call MsgBox("You must enter a Zip", vbExclamation, "Add Zip")
Me.Company.SetFocus
Me.Zip.SetFocus
Cancel = True
Exit Sub
End If
If Len(Phone) = 0 Or IsNull(Phone) = True Then
Call MsgBox("You must enter a Phone No", vbExclamation, "Add Phone")
Me.Company.SetFocus
Me.Phone.SetFocus
Cancel = True
Exit Sub
End If
If Len(cboCSR) = 0 Or IsNull(cboCSR) = True Then
Call MsgBox("You must select a CSR", vbExclamation, "Add CSR")
Me.Company.SetFocus
Me.cboCSR.SetFocus
Cancel = True
Exit Sub
End If
If Len(cboMod) = 0 Or IsNull(cboMod) = True Then
Call MsgBox("You must select a Model", vbExclamation, "Add Model")
Me.Company.SetFocus
Me.cboMod.SetFocus
Cancel = True
Exit Sub
End If
If Len(Serial) = 0 Or IsNull(Serial) = True Then
Call MsgBox("You must select a Serial Number", vbExclamation, "Add Serial")
Me.Company.SetFocus
Me.Serial.SetFocus
Cancel = True
Exit Sub
End If
If Len(ProblemDesc) = 0 Or IsNull(ProblemDesc) = True Then
Call MsgBox("You must select a Problem Description", vbExclamation, "Add ProblemDesc")
Me.Company.SetFocus
Me.ProblemDesc.SetFocus
Cancel = True
Exit Sub
End If
End Sub
Debug Error 2501 (the run command was canceled)
However I can't have that error in production.
Since that is both an expected and desired error -- you did cancel the action -- handle it
btnSave_Click_Error:
if err.Number = 2501 then exit sub
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure btnSave_Click of VBA Document Form_ISSC"
However I can't have that error in production.
Since that is both an expected and desired error -- you did cancel the action -- handle it
btnSave_Click_Error:
if err.Number = 2501 then exit sub
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure btnSave_Click of VBA Document Form_ISSC"
ASKER
OK... Nick, after adding that line of code it doesn't Error when the control is blank but when data is added and the save button is click the record saves but another error pops up "Error 0 () in procedure btnSave_click of VBA Document Form_ISSC" so I commented out the MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure btnSave_Click of VBA Document Form_ISSC" section of btnSave_Click_Error: and that stopped that. Was that the correct procedure.
after adding that line of code it doesn't Error when the control is blank
A bit loose in the wording
The btnSave_Click() routine is suppose to attempt a Save.
That forces a BeforeUpdate event
If the code in BeforeUpdate causes the save to cancel, an error will cascade back to btnSave_Click()
That should be the 2501 error and it is now handled by silently exiting
if err.Number = 2501 then exit sub
Is that a proper description of what happens with an empty control?
but when data is added and the save button is click the record saves but another error pops up "Error 0 () in procedure btnSave_click of VBA Document Form_ISSC"
Error 0 is indicating success, so that too, we'd like to handle with a silent exiting
In general, with an error handler, you'd like the code to specify what will occur with the errors that occur
A Select Case True structure is brilliant for this:
btnSave_Click_Error:
Select Case True
Case err.Number = 0 'no error, just let the sub end
Case err.Number = 2501 'BeforeUpdate was cancelled an a msgbox generated, let this sub end
Case else 'something we haven't encountered yet has occurred. Msgbox it
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure btnSave_Click of VBA Document Form_ISSC"
End Select
so I commented out...
Was that the correct procedure.
Not really.
That gutted your error handler
In effect, all errors silently fail when you do that
A bit loose in the wording
The btnSave_Click() routine is suppose to attempt a Save.
That forces a BeforeUpdate event
If the code in BeforeUpdate causes the save to cancel, an error will cascade back to btnSave_Click()
That should be the 2501 error and it is now handled by silently exiting
if err.Number = 2501 then exit sub
Is that a proper description of what happens with an empty control?
but when data is added and the save button is click the record saves but another error pops up "Error 0 () in procedure btnSave_click of VBA Document Form_ISSC"
Error 0 is indicating success, so that too, we'd like to handle with a silent exiting
In general, with an error handler, you'd like the code to specify what will occur with the errors that occur
A Select Case True structure is brilliant for this:
btnSave_Click_Error:
Select Case True
Case err.Number = 0 'no error, just let the sub end
Case err.Number = 2501 'BeforeUpdate was cancelled an a msgbox generated, let this sub end
Case else 'something we haven't encountered yet has occurred. Msgbox it
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure btnSave_Click of VBA Document Form_ISSC"
End Select
so I commented out...
Was that the correct procedure.
Not really.
That gutted your error handler
In effect, all errors silently fail when you do that
ASKER
OK... through testing the various solutions I have decided the best for this application is to go with discrete blocks and exit subs as Nick suggested as I want to annoy the user with popups of each control left empty, and to use the Select Case True structure for error handling, again I thank everyone for their suggestions.
For what it is worth,
I go with the Select Case True structure for my validating, instead of If Thens
Select Case True
Case Len(nz(Firstname,0)) = 0 'no valid firstname
MsgBox("You must enter First Name", vbExclamation, "Add Firstname")
Me.Firstname.SetFocus
Case Len(nz(Lastname,0)) = 0 'no valid lastname
MsgBox("You must enter Last Name", vbExclamation, "Add Lastname")
Me.Lastname.SetFocus
....
Case Else 'nothing unhappy happened, save and advance
'Save the record.
RunCommand acCmdSaveRecord
'Load a new record after the save.
DoCmd.GoToRecord , , acNewRec
End Select
A single nicely-readable conditional walks once down the possibilities of things gone wrong, and if none are found, executes what needs doing.
Select Case True is very powerful.
Any number of cases that evaluate to a Boolean can be constructed and they test in order.
The first one that evaluates true has its code execute and then the structure exits.
It removes the need for the exit sub statements and about a third of the code length, as the closing end ifs aren't needed, either.
But at the beginning, I didn't know if you would want a Select Case true, understand it, or want to refactor the Ifs that much. Since I discovered it, I don't think I've used an ElseIf since.
And since you can put Ifs in the code that executes for a true case, it can make for a lot cleaner and more readable code than nested ifs ever could.
YMMV
Nick67
I go with the Select Case True structure for my validating, instead of If Thens
Select Case True
Case Len(nz(Firstname,0)) = 0 'no valid firstname
MsgBox("You must enter First Name", vbExclamation, "Add Firstname")
Me.Firstname.SetFocus
Case Len(nz(Lastname,0)) = 0 'no valid lastname
MsgBox("You must enter Last Name", vbExclamation, "Add Lastname")
Me.Lastname.SetFocus
....
Case Else 'nothing unhappy happened, save and advance
'Save the record.
RunCommand acCmdSaveRecord
'Load a new record after the save.
DoCmd.GoToRecord , , acNewRec
End Select
A single nicely-readable conditional walks once down the possibilities of things gone wrong, and if none are found, executes what needs doing.
Select Case True is very powerful.
Any number of cases that evaluate to a Boolean can be constructed and they test in order.
The first one that evaluates true has its code execute and then the structure exits.
It removes the need for the exit sub statements and about a third of the code length, as the closing end ifs aren't needed, either.
But at the beginning, I didn't know if you would want a Select Case true, understand it, or want to refactor the Ifs that much. Since I discovered it, I don't think I've used an ElseIf since.
And since you can put Ifs in the code that executes for a true case, it can make for a lot cleaner and more readable code than nested ifs ever could.
YMMV
Nick67
ASKER
Yeah... I like it. I use case statements in my MSSQL codes a lot, didn't know I could use them in Access as well, I will rewrite my validation statements to use that, thanks.
ASKER
Nick would I put this on the on_click event of the save button or on the beforupdate event?
As you are coming to see, there are questions of style in how a user data entry form gets created.
The challenge is 'how do I keep bad data from being entered'
@ PatHartman denotes one style
Your save button should force the record to save and that will fire the FORM's BeforeUpdate event and the FORM's BeforeUpdate event is where the validation code actually belongs. That way, no matter what the user does, the code will fire if Access decides it needs to save the current record.
As you have seen, trying to keep the user from experiencing any VBA-generated errors when you go that route is not intuitive (not to me anyway.) Many things can set the BeforeUpdate event off, and when the code in BeforeUpdate enacts a Cancel, it throws the error back to whatever set it off -- and then you have to find all those spots and build error handlers, or have a generic error handler up and working, and it ALL get bloody complex in a hurry.
I don't care for that.
What I typically do is put unbound controls on the form for the user to pick, select or enter data. My command button validates that the data is good and writes the records. This is kick-ass for those multi-table situation where the order that the records get created in matter for referential integrity. I can also ask the user if they are certain before committing.
And the data entered in the unbound controls -- well, its unbound! So if the user exits the form, or navigates or the power fails, or whatever, no bad data is partially entered. This allows the user to bail out AFTER they have started entry without consequences. That's hard to do otherwise. Data either gets pounded in by MY coding, after validating, or not at all. This is more work to set up, but I think it makes for a better user experience AND bulletproof data. The unbound controls I put in the form header. Below I put a subform showing the committed records for viewing and possible editing.
Nick would I put this on the on_click event of the save button or on the beforupdate event?
I would put the code validating in its own function that returns a Boolean
In the function, set it as false at the beginning and true in the Case Else
You can then call the code from BOTH the button and the BeforeUpdate
The button will run things nicely and error-less as you intend and the users will gravitate to that.
The BeforeUpdate may toss some unhappiness -- and the users will quickly learn not to do anything but use your button.
Does that make sense?
The challenge is 'how do I keep bad data from being entered'
@ PatHartman denotes one style
Your save button should force the record to save and that will fire the FORM's BeforeUpdate event and the FORM's BeforeUpdate event is where the validation code actually belongs. That way, no matter what the user does, the code will fire if Access decides it needs to save the current record.
As you have seen, trying to keep the user from experiencing any VBA-generated errors when you go that route is not intuitive (not to me anyway.) Many things can set the BeforeUpdate event off, and when the code in BeforeUpdate enacts a Cancel, it throws the error back to whatever set it off -- and then you have to find all those spots and build error handlers, or have a generic error handler up and working, and it ALL get bloody complex in a hurry.
I don't care for that.
What I typically do is put unbound controls on the form for the user to pick, select or enter data. My command button validates that the data is good and writes the records. This is kick-ass for those multi-table situation where the order that the records get created in matter for referential integrity. I can also ask the user if they are certain before committing.
And the data entered in the unbound controls -- well, its unbound! So if the user exits the form, or navigates or the power fails, or whatever, no bad data is partially entered. This allows the user to bail out AFTER they have started entry without consequences. That's hard to do otherwise. Data either gets pounded in by MY coding, after validating, or not at all. This is more work to set up, but I think it makes for a better user experience AND bulletproof data. The unbound controls I put in the form header. Below I put a subform showing the committed records for viewing and possible editing.
Nick would I put this on the on_click event of the save button or on the beforupdate event?
I would put the code validating in its own function that returns a Boolean
In the function, set it as false at the beginning and true in the Case Else
You can then call the code from BOTH the button and the BeforeUpdate
The button will run things nicely and error-less as you intend and the users will gravitate to that.
The BeforeUpdate may toss some unhappiness -- and the users will quickly learn not to do anything but use your button.
Does that make sense?
I guess it is a matter of style. Somehow, I have no trouble using bound forms and the BeforeUpdate event to validate data. If you want the user to be able to bail without saving, teach him how to do it using the esc key or the undo or give him a button. The way I look at it, I've written my million lines of code so I don't need the practice. That is one of the best things about Access. Once you understand how events and properties work, you can save yourself a ton of code. I once took 1500 lines of code that attempted to ensure fields were not null and turned them into 30 that were called from the correct event. Notice I said attempted. The programmer had code in every conceivable event EXCEPT the correct one - the BeforeUpdate event and bad records were being saved regularly because of it.
<earnest and heartfelt>
@PatHartman
I invited the discussion.
I cannot provide what I have never been able to certainly ascertain.
Which is -- how in the name of the seven bells of hell do I ABSOLUTLEY, POSITIVELY ensure that no VBA error gets to the user if I code up a BeforeUpdate event.
You clearly know how to get that done. I don't. So teach me and skull52 at the same time, please.
Doesn't mean I'll use bound controls for data entry, but it would be nice to have that tool in the shed.
I don't at the moment
If you want the user to be able to bail without saving, teach him how to do it using the esc key or the undo or give him a button.
Excuse me while I ROFLMAO for a moment. If that could be managed, it'd be one of the great miracles of modern life.
I went the direction I did precisely because that task was so frustrating.
Once you understand how events and properties work, you can save yourself a ton of code.
I understand BeforeInsert and BeforeUpdate well enough.
I've never been able to trap the errors that a Cancel=True generates to my satisfaction -- which is to say, 100% of the time.
So I went a different path. I don't use them, and they don't generate errors 100% of the time.
And while me.undo is a nice idea, my users were pissed off when ALL the stuff they entered disappeared.
And they were pissed when they would try to close an object and instead be trapped on it.
Or close the whole app and be trapped in it
They had UI expectations that the 'x' in the upper corner would close something
And they expected that 'x' to be there
And some of them knew ctrl-alt-delete | task manager well enough not to take any crap from the app.
So, it is a matter of style, and I have acknowledged that you have a style that you have mastered well.
If you could provide a roadmap about how to avoid all the pitfalls in your style, I'd be grateful.
</earnest and heartfelt>
Nick67
@PatHartman
I invited the discussion.
I cannot provide what I have never been able to certainly ascertain.
Which is -- how in the name of the seven bells of hell do I ABSOLUTLEY, POSITIVELY ensure that no VBA error gets to the user if I code up a BeforeUpdate event.
You clearly know how to get that done. I don't. So teach me and skull52 at the same time, please.
Doesn't mean I'll use bound controls for data entry, but it would be nice to have that tool in the shed.
I don't at the moment
If you want the user to be able to bail without saving, teach him how to do it using the esc key or the undo or give him a button.
Excuse me while I ROFLMAO for a moment. If that could be managed, it'd be one of the great miracles of modern life.
I went the direction I did precisely because that task was so frustrating.
Once you understand how events and properties work, you can save yourself a ton of code.
I understand BeforeInsert and BeforeUpdate well enough.
I've never been able to trap the errors that a Cancel=True generates to my satisfaction -- which is to say, 100% of the time.
So I went a different path. I don't use them, and they don't generate errors 100% of the time.
And while me.undo is a nice idea, my users were pissed off when ALL the stuff they entered disappeared.
And they were pissed when they would try to close an object and instead be trapped on it.
Or close the whole app and be trapped in it
They had UI expectations that the 'x' in the upper corner would close something
And they expected that 'x' to be there
And some of them knew ctrl-alt-delete | task manager well enough not to take any crap from the app.
So, it is a matter of style, and I have acknowledged that you have a style that you have mastered well.
If you could provide a roadmap about how to avoid all the pitfalls in your style, I'd be grateful.
</earnest and heartfelt>
Nick67
Open in new window