NauticalGent
Ignore List Poster Boy
- Local time
- Today, 07:27
- Joined
- Apr 27, 2015
- Messages
- 6,747
Good morning everyone,
I grinded this out yesterday when the Dev for the App I am documenting reached out to me with a new problem. I was able to make it work but I cant hlp but feel there is a better way to do it.
He wanted a way to disable some controls and alert the user as to which fields were required. Borrowing Gina's fValidateData Function (excellent job Gina!) I was able to give him what he wanted at first until he notified me of the problem.
The problem: I tagged each field with "Required" and all was well - until he told me that two of the Required fields are only required under a certain situation.
The Situation:
- If the fields ("Solicitation" and "DeliveryOrder" are NOT null) OR if the ("Solicitation" field is not null and the "DeleiveryOrder" IS null) then any control with "Required" gets evaluated.
- If the Fields "DeliveryOrder" is NOT null and the "Solicitation" field is null then all but two of the required fields get evaluated.
To account for this, for those records that fall into the second category, I added a "1" at the end of the "Required" tag: "Required1", designating that record as a "Delivery Order Only" record. I check for this on the Form's Current() event and it assigns either True or False to a variable: blnDO_Only. I pass the form name and blnDO_Only value to the function and it works.
The code:
As I said, it works exactly as I intend it to, but the fact that I have to use the same code twice in a single function grates on me but it was the only way I was able to get it to work.
I thought maybe I could pass the validation part off to a separate sub but I so not see how that could work. If this is the way it has to be then I'll live with it, but if there is something I am over-looking I would be interested in what you think.
Thanks in advance!
I grinded this out yesterday when the Dev for the App I am documenting reached out to me with a new problem. I was able to make it work but I cant hlp but feel there is a better way to do it.
He wanted a way to disable some controls and alert the user as to which fields were required. Borrowing Gina's fValidateData Function (excellent job Gina!) I was able to give him what he wanted at first until he notified me of the problem.
The problem: I tagged each field with "Required" and all was well - until he told me that two of the Required fields are only required under a certain situation.
The Situation:
- If the fields ("Solicitation" and "DeliveryOrder" are NOT null) OR if the ("Solicitation" field is not null and the "DeleiveryOrder" IS null) then any control with "Required" gets evaluated.
- If the Fields "DeliveryOrder" is NOT null and the "Solicitation" field is null then all but two of the required fields get evaluated.
To account for this, for those records that fall into the second category, I added a "1" at the end of the "Required" tag: "Required1", designating that record as a "Delivery Order Only" record. I check for this on the Form's Current() event and it assigns either True or False to a variable: blnDO_Only. I pass the form name and blnDO_Only value to the function and it works.
The code:
Code:
Function fValidateData(frm As Form, Optional blnDO_only As Boolean) As Boolean
On Error GoTo ErrHandler
'From https://www.access-diva.com/vba13.html
'Use with Command Button on Subform or on single Form in Before_Update event
'rtw 8.29.2015 Updated to cut off "ID" and split based on CamelCase
'rtw 7.30.2019 Updated to add highlighting of Control with missing data
Dim ctl As Control
Dim blnValid As Boolean
blnValid = True
For Each ctl In frm.Controls
If ctl.Tag <> "" Then
If ctl.Enabled Then
If InStr(1, ctl.Tag, "Require") Then
If Nz(ctl, "") = "" Then
If InStr(1, ctl.Tag, "1") Then
If blnDO_only Then
Select Case ctl.ControlType
Case acTextBox
ctl.BackColor = vbWhite
Case acComboBox
ctl.BackColor = vbWhite
End Select
Else
blnValid = False
Select Case ctl.ControlType
Case acTextBox
ctl.BackColor = RGB(254, 242, 154) 'Yellow
Case acComboBox
ctl.BackColor = RGB(254, 242, 154) 'Yellow
End Select
'Sets the focus to the Control
ctl.SetFocus
GoTo Complete
End If
Else
blnValid = False
Select Case ctl.ControlType
Case acTextBox
ctl.BackColor = RGB(254, 242, 154) 'Yellow
Case acComboBox
ctl.BackColor = RGB(254, 242, 154) 'Yellow
End Select
'Sets the focus to the Control
ctl.SetFocus
GoTo Complete
End If
Else
'If more than one Control will turn the filled in ones back to white
Select Case ctl.ControlType
Case acTextBox
ctl.BackColor = vbWhite
Case acComboBox
ctl.BackColor = vbWhite
End Select
End If
End If
End If
End If
Next ctl
Complete:
Set ctl = Nothing
fValidateData = blnValid
'Debug.Print "Valid = " & blnValid
Exit Function
ErrHandler:
blnValid = False
MsgBox ("Error validating: " & Err.Description)
Resume Complete
End FunctionAs I said, it works exactly as I intend it to, but the fact that I have to use the same code twice in a single function grates on me but it was the only way I was able to get it to work.
I thought maybe I could pass the validation part off to a separate sub but I so not see how that could work. If this is the way it has to be then I'll live with it, but if there is something I am over-looking I would be interested in what you think.
Thanks in advance!
