billcute
asked on
Ignoring validation rule with clause if a record is cancelled
How can I ignore a date field from being validated from a mandatory code if and when a record is cancelled using a combo (cboCancel = "Yes")?
......such that if a record is cancelled and date field is set to "Null" then "ignore or skip" validation rule for the date field which has been set to "Null" because a record is cancelled.
However, in the new record mode, if cboCancel = "No" then the data validation code will operate as normal thereby checking for all mandatory fields.
My current Mandatoryfields function works using "TAG" properties of the control to validate the required fields. However, In order to avoid pasting too many code on this post, the MandatoryFields Function can be found at the link below:
https://www.experts-exchange.com/questions/22861455/Tag-property-question.html
' *************
In my cmdAddNew and cmdExit, the following line code first check for mandaory fields and validates the required fields before adding or exiting a record. This line code is present both in the AddNew and Exit buttons.
If Not MandatoryFields Then ' <---Check for Mandatory Fields
Exit Sub
End If
' **************************
Private Sub btnAddNew_Click()
On Error GoTo Err_btnAddNew_Click
If Not MandatoryFields Then ' <---Check for Mandatory Fields
Exit Sub
End If
' Call SaveCurrentControlValues ' For Autofill Routine - this works fine
' **************************
If Me.AllowAdditions = False Then Me.AllowAdditions = True
Me.Filter = ""
Me.FilterOn = False
DoCmd.GoToRecord , "frmMain", acNewRec
Exit_btnAddNew_Click:
Exit Sub
Err_btnAddNew_Click:
MsgBox err.Description
Resume Exit_btnAddNew_Click
End Sub
......such that if a record is cancelled and date field is set to "Null" then "ignore or skip" validation rule for the date field which has been set to "Null" because a record is cancelled.
However, in the new record mode, if cboCancel = "No" then the data validation code will operate as normal thereby checking for all mandatory fields.
My current Mandatoryfields function works using "TAG" properties of the control to validate the required fields. However, In order to avoid pasting too many code on this post, the MandatoryFields Function can be found at the link below:
https://www.experts-exchange.com/questions/22861455/Tag-property-question.html
' *************
In my cmdAddNew and cmdExit, the following line code first check for mandaory fields and validates the required fields before adding or exiting a record. This line code is present both in the AddNew and Exit buttons.
If Not MandatoryFields Then ' <---Check for Mandatory Fields
Exit Sub
End If
' **************************
Private Sub btnAddNew_Click()
On Error GoTo Err_btnAddNew_Click
If Not MandatoryFields Then ' <---Check for Mandatory Fields
Exit Sub
End If
' Call SaveCurrentControlValues ' For Autofill Routine - this works fine
' **************************
If Me.AllowAdditions = False Then Me.AllowAdditions = True
Me.Filter = ""
Me.FilterOn = False
DoCmd.GoToRecord , "frmMain", acNewRec
Exit_btnAddNew_Click:
Exit Sub
Err_btnAddNew_Click:
MsgBox err.Description
Resume Exit_btnAddNew_Click
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Private Sub LblUpdate_Click()
Dim txStr As String
On Error GoTo LblUpdate_Click_Error
If Me.Dirty Then
txStr = "Save the edited record?"
Select Case msgbox(txStr & vbCrLf & _
"(No to return to the form.)", vbYesNo)
Case vbYes
if me.cboCancel = "Yes" then goto SaveCommand
If Not MandatoryFields Then ' < ----- Checks for missing fields
Exit Sub
End If
SaveCommand:
DoCmd.RunCommand acCmdSaveRecord
Case vbNo
Forms!frmMain.Undo
End Select
End If
On Error GoTo 0
Exit Sub
LblUpdate_Click_Error:
If DebugMode Then On Error GoTo 0: Resume
LogError err.Number, err.Description, "Form_frmMain", "LblUpdate_Click", Erl
End Sub
' *********
(C).
Private Sub LblExit_Click()
On Error GoTo ErrRtn
Dim txStr As String
If Me.Dirty Then
' User has entered new data
txStr = "Save Edited Record?"
Select Case msgbox(txStr & vbCrLf & _
"(Cancel to return to the form.)", vbYesNoCancel)
Case vbYes
if me.cboCancel = "Yes" then goto ContinueCode:
If Not MandatoryFields Then
Exit Sub
End If
ContinueCode:
DoCmd.RunCommand acCmdSave
' **********
Call UpdateEntryDate ' Updating something else
' ***************
' Exit form
DoCmd.Close
Case vbNo
' Undo changes
Forms!frmMain.Undo
' Exit form
DoCmd.Close
Case vbCancel:
' Exit sub and return to form
Exit Sub
End Select
Else
DoCmd.Close
End If
btnExit_Exit:
Exit Sub
ErrRtn:
msgbox err.Description
End Sub
Dim txStr As String
On Error GoTo LblUpdate_Click_Error
If Me.Dirty Then
txStr = "Save the edited record?"
Select Case msgbox(txStr & vbCrLf & _
"(No to return to the form.)", vbYesNo)
Case vbYes
if me.cboCancel = "Yes" then goto SaveCommand
If Not MandatoryFields Then ' < ----- Checks for missing fields
Exit Sub
End If
SaveCommand:
DoCmd.RunCommand acCmdSaveRecord
Case vbNo
Forms!frmMain.Undo
End Select
End If
On Error GoTo 0
Exit Sub
LblUpdate_Click_Error:
If DebugMode Then On Error GoTo 0: Resume
LogError err.Number, err.Description, "Form_frmMain", "LblUpdate_Click", Erl
End Sub
' *********
(C).
Private Sub LblExit_Click()
On Error GoTo ErrRtn
Dim txStr As String
If Me.Dirty Then
' User has entered new data
txStr = "Save Edited Record?"
Select Case msgbox(txStr & vbCrLf & _
"(Cancel to return to the form.)", vbYesNoCancel)
Case vbYes
if me.cboCancel = "Yes" then goto ContinueCode:
If Not MandatoryFields Then
Exit Sub
End If
ContinueCode:
DoCmd.RunCommand acCmdSave
' **********
Call UpdateEntryDate ' Updating something else
' ***************
' Exit form
DoCmd.Close
Case vbNo
' Undo changes
Forms!frmMain.Undo
' Exit form
DoCmd.Close
Case vbCancel:
' Exit sub and return to form
Exit Sub
End Select
Else
DoCmd.Close
End If
btnExit_Exit:
Exit Sub
ErrRtn:
msgbox err.Description
End Sub
ASKER
Leigh:
I'll try these out and let you know later.
Regards
Bill
I'll try these out and let you know later.
Regards
Bill
Bill,
Just so that you don't think i am ignoring you I am just about to head of on a camping trip with my son for a few days and will be away from my computer.
I hope what you have works for you and I will be in touch soon.
Cheers,
Leigh
Just so that you don't think i am ignoring you I am just about to head of on a camping trip with my son for a few days and will be away from my computer.
I hope what you have works for you and I will be in touch soon.
Cheers,
Leigh
ASKER
Leigh:
I was away for few days...just came back and saw your earlier comments. If and when you see this message, please find my test result.
When the btnAddNew was clicked, it did not advance to a new record nor bypassed the mandatory fields check.
I also got this Microsoft Access dialog box message error:
"The object Type argument for the action or method is blank or invalid.
Regards
Bill
I was away for few days...just came back and saw your earlier comments. If and when you see this message, please find my test result.
When the btnAddNew was clicked, it did not advance to a new record nor bypassed the mandatory fields check.
I also got this Microsoft Access dialog box message error:
"The object Type argument for the action or method is blank or invalid.
Regards
Bill
Are there any other form events that may be causing this eg "On Current" or "On Exit" ?
Leigh
Leigh
ASKER
Leigh:
Here is my On Current Event..
On Error GoTo Form_Current_Error
If IsLoaded("frmSearchForm") = True Then
Call Twentyfourmonth_Locks '<<<---- For locking form record over 24 months
End If
On Error GoTo 0
Exit Sub
Form_Current_Error:
If DebugMode Then On Error GoTo 0: Resume
LogError err.Number, err.Description, "Form_frmWater", "Resume Form_Current", Erl
End Sub
Here is my On Current Event..
On Error GoTo Form_Current_Error
If IsLoaded("frmSearchForm") = True Then
Call Twentyfourmonth_Locks '<<<---- For locking form record over 24 months
End If
On Error GoTo 0
Exit Sub
Form_Current_Error:
If DebugMode Then On Error GoTo 0: Resume
LogError err.Number, err.Description, "Form_frmWater", "Resume Form_Current", Erl
End Sub
ASKER
Leigh:
I resolved all the errors in my App and stated above, then tested your code again. Your amended code to btnAddNew did not bypass the Mandatory Appdate field.
Please note that the mandatory employs the use of tag property to check for missing fields which is likely to "not" allow your code to bypass the controls that were dimmed mandatory.
Regards
Bill
I resolved all the errors in my App and stated above, then tested your code again. Your amended code to btnAddNew did not bypass the Mandatory Appdate field.
Please note that the mandatory employs the use of tag property to check for missing fields which is likely to "not" allow your code to bypass the controls that were dimmed mandatory.
Regards
Bill
ASKER
Leigh:
If and when you have time, please check the problem associated with your amended code.
Regards
Bill
If and when you have time, please check the problem associated with your amended code.
Regards
Bill
Bill,
Sorry I've been flat out.
Could you upload you app so I can see exactly what is going on with it.
I can't understand why it isn't working.
Also, Are the other buttons working as expected ?
Leigh
Sorry I've been flat out.
Could you upload you app so I can see exactly what is going on with it.
I can't understand why it isn't working.
Also, Are the other buttons working as expected ?
Leigh
ASKER
Leigh:
I will accept your answer as is...I may bring up the matter again in the nearest future post. Thanks
I will accept your answer as is...I may bring up the matter again in the nearest future post. Thanks
ASKER
(b)
Private Sub LblUpdate_Click()
Dim txStr As String
On Error GoTo LblUpdate_Click_Error
If Me.Dirty Then
txStr = "Save the edited record?"
Select Case msgbox(txStr & vbCrLf & _
"(No to return to the form.)", vbYesNo)
Case vbYes
If Not MandatoryFields Then ' < ----- Checks for missing fields
Exit Sub
End If
DoCmd.RunCommand acCmdSaveRecord
Case vbNo
Forms!frmMain.Undo
End Select
End If
On Error GoTo 0
Exit Sub
LblUpdate_Click_Error:
If DebugMode Then On Error GoTo 0: Resume
LogError err.Number, err.Description, "Form_frmMain", "LblUpdate_Click", Erl
End Sub
' *********
(C).
Private Sub LblExit_Click()
On Error GoTo ErrRtn
Dim txStr As String
If Me.Dirty Then
' User has entered new data
txStr = "Save Edited Record?"
Select Case msgbox(txStr & vbCrLf & _
"(Cancel to return to the form.)", vbYesNoCancel)
Case vbYes
If Not MandatoryFields Then
Exit Sub
End If
DoCmd.RunCommand acCmdSave
' **********
Call UpdateEntryDate ' Updating something else
' ***************
' Exit form
DoCmd.Close
Case vbNo
' Undo changes
Forms!frmMain.Undo
' Exit form
DoCmd.Close
Case vbCancel:
' Exit sub and return to form
Exit Sub
End Select
Else
DoCmd.Close
End If
btnExit_Exit:
Exit Sub
ErrRtn:
msgbox err.Description
End Sub