Help in VBA

[Ronaldo9]

Hi there,

This could be a easy one for a VBA "savvy"..
I have the following piece of validation code(part of a function) that returns True/False based on whether the required data is filled:

For Each ctl In Controls
If IsNull(ctl) And (ctl.Tag = "*") Then
ValidateRequiredFields = False
ctl.SetFocus 'Set focus to the control
Me.btnMissingData.Visible = True
Me.imgYellowbck.Visible = True
Exit Function
Else
ValidateRequiredFields = True
Me.btnMissingData.Visible = False
Me.imgYellowbck.Visible = False
End If
Next ctl

Now my issue is, I have added five new fields that are also mandatory. However, each one of them is mandatory, depending on the identity of the user or which group they belong to.(The db captures the windows log in and looks up the group for the user).

So only one of these 5 fields is mandatory at a time... For example if usergroup = HR then DateHR is mandatory, etc...

How can incorporate this nicely in my piece of code above without adding long messy code just for the 5 new fields..

I would really appreciate any help..
Thanks,
Elias.

 

[mdlueck]

For Each ctl In Controls
<snip>
Next ctl

So only one of these 5 fields is mandatory at a time... For example if usergroup = HR then DateHR is mandatory, etc...

First thing that comes to mind based on that loop logic is to test for each special condition and if true then handle that special condition within said loop.

 

[vbaInet]

Use a SELECT CASE structure:

Select Case UserGroup
    Case "HR"
        ... HR code here
    Case "Finance"
        ... finance code here
    Case Else
        ... other code here
End Select

 

[Ronaldo9]

I was trying to combine all the criteria in one line..
Since the new five fields are only enabled for the corresponding user, I tried:

If IsNull(ctl) And (ctl.Tag = "*") And (ctl.Enabled = True) Then

but it seems the enabled property is not supported for control object. and that didn't work.

Thanks,
Elias.

 

[vbaInet]

For what corresponding user?

 

[smig]

It was easier to read if you put your cofe into a #Code sniff instead of making it bold

 

[Galaxiom]

You need to include only the controls that are valid for all the tests being made or you will get an error that the property doesn't exist.

So test the Tag property first then the Enabled property or first limit the control type to those which have an enabled property.

BTW With Boolean values the If test can simply be
... And ctl.Enabled

No need to test against True because it is already True or False.

 

[Ronaldo9]

For what corresponding user?

fields are enabled depending on who's logged in for example if HR user is logged in, the HRReviewdate is enabled, if they're finance, the FINReviewDate is enabled etc...
These review dates are mandatory but only one at a time...

 

[Ronaldo9]

You need to include only the controls that are valid for all the tests being made or you will get an error that the property doesn't exist.

So test the Tag property first then the Enabled property or first limit the control type to those which have an enabled property.

BTW With Boolean values the If test can simply be
... And ctl.Enabled

No need to test against True because it is already True or False.

The required fields are all either text box or combo box
I tried: Dim ctl as Textbox but that didn't work either.
Thanks for the boolean tip..
Elias

 

[vbaInet]

fields are enabled depending on who's logged in for example if HR user is logged in, the HRReviewdate is enabled, if they're finance, the FINReviewDate is enabled etc...
These review dates are mandatory but only one at a time...

You need to include only the controls that are valid for all the tests being made or you will get an error that the property doesn't exist.

So in your case when you loop through the controls test for those controls that are Enabled and only use those that enabled.

So test the Tag property first then the Enabled property or first limit the control type to those which have an enabled property.

BTW With Boolean values the If test can simply be
... And ctl.Enabled

Plus the above comment by Galaxiom. ctl.ControlType

 

[Galaxiom]

The required fields are all either text box or combo box

Labels, Lines and Rectangles are all Controls too but they don't have an Enabled property so will break the loop when you refer to ctrl.Enabled on them.

 

[Dairy Farmer]

Just a quick comment
Don't use IsNull
Rather use If Len(Me.textbox1 & "") = 0 Then

There is a difference between Null and Zero Length especially with Dates.

 

[vbaInet]

Just a quick comment
Don't use IsNull
Rather use If Len(Me.textbox1 & "") = 0 Then

Yes this is a better alternative. There's also If Len(Nz(Me.textbox1 , vbNullString)) = 0. But this implicit "cast" to string is not necessary if you set the Allow Zero Length property of the field to No, in which case you only use Nz().

There is a difference between Null and Zero Length especially with Dates.

In this case you use IsDate().

 

[Dairy Farmer]

Ahhh, I always forget NZ.

I had a nice validation for missing fields and NewRecord, I just can't find it. Basically there is a button at the end of the form that speciffically check all fields are filled in and then allows a new reord. I had to do this for a database where the historical data had missing info. These records would "pass" validation.

 

[vbaInet]

I had a nice validation for missing fields and NewRecord, I just can't find it. Basically there is a button at the end of the form that speciffically check all fields are filled in and then allows a new reord. I had to do this for a database where the historical data had missing info. These records would "pass" validation.

What I would normally do is write a function to validate all the required fields and if all is well it returns True. Otherwise, if one or more fields are invalid or blank, it will return references of all the bound controls and then I can use these references to highlight the controls.

 

[Ronaldo9]

What I would normally do is write a function to validate all the required fields and if all is well it returns True. Otherwise, if one or more fields are invalid or blank, it will return references of all the bound controls and then I can use these references to highlight the controls.

This is exactly what I did here. I added a "*" in the tag property of all required fields.
My validation function loops thru the controls, if the field has a "*" in the tag property and is Null, the function returns false, otherwise returns true.

My issue here is: I can't incorporate this logic with my newly added date fields, since not all date fields on the form are required.. ONLY the enabled ones. Different date fields are enabled for different users.(depending on the logged in user)

 

[mdlueck]

My issue here is: I can't incorporate this logic with my newly added date fields, since not all date fields on the form are required.. ONLY the enabled ones.

Then either place smarts into the loop logic to handle the special conditions, or you must do away with the loop.

For one generic/shared/common sub in my program I had to add special handling if the code is encountering a unique control name. So even though that control is only on one form, the generic code must test the control names in order for the sub to be generic and able to work with any form in the application.

 

[vbaInet]

This is exactly what I did here. I added a "*" in the tag property of all required fields.
My validation function loops thru the controls, if the field has a "*" in the tag property and is Null, the function returns false, otherwise returns true.

That was just information for Dairy Farmer. It wasn't directed at you

My issue here is: I can't incorporate this logic with my newly added date fields, since not all date fields on the form are required.. ONLY the enabled ones. Different date fields are enabled for different users.(depending on the logged in user)

I thought all the information you needed was mentioned in post #10? Test the Enabled property as well as Null AND "*". Incorporate this extra test in your current condition.

 

[Ronaldo9]

That was just information for Dairy Farmer. It wasn't directed at you

I thought all the information you needed was mentioned in post #10? Test the Enabled property as well as Null AND "*". Incorporate this extra test in your current condition.

Yes.. I was just re-stating my issue..
I incorporated the enabled property in my condition and it works just fine now..
Since all my required fields are texboxes or combo boxes ,I did the following:

If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
If IsNull(ctl) And (ctl.Tag = "*") And (ctl.Enabled) = True Then

Thanks for you help.

 

[vbaInet]

Pleased to hear