VBA set controls Enabled property

[Xarkath]

I have 6 controls that I want to update the Enabled property to on based on the selection of a 7th control. The code is working on 4 of the 6 and it is actually the middle 2 controls (3 and 4 below) that are not updating. I get Run-time error 438 Object doesn't Support this property or method. I am a little confused as to why it would handle 1 combo box fine but have issue with the other 2.

Any advice would be greatly appreciated. Thanks

The Control Types in order:

1) Date
2) Combo box with value section
3) Combo Box with table selection
4) Combo Box with value selection


5) Text
6) Date

The Code:

Private Sub Tracking_Reason_Change()
Dim ctl As Control
If Me.[Tracking Reason] Like "*Billing*" Then
For Each ctl In Screen.ActiveForm.Controls
If ctl.Tag = "Adj" Then
ctl.Enabled = True
End If
Next ctl
End If
End Sub

[June7]

Is weird - all looks good to me.

Consider using Conditional Formatting. Can select all 6 controls together and set the Conditional Formatting for all because the condition is the same for all.

Is the form in Single or Continuous or Datasheet view?

[Xarkath]

It is a Single Form.

I have 3 sets of fields with 40 total controls that I will want to build into the code, this is just the first set, so I would really like to get this working. Not that I am opposed to alternative methods I was just thinking that this would be the most efficient as it pertains to resource management.

Thanks

[June7]

Still think Conditional Formatting would be easier. Don't know how 'pertains to resource management' has any bearing.

What do you mean by '3 sets of fields'? - sounds like non-normalized structure.

[Xarkath]

UPDATE:

I fixed #4 and I think I found the root of the problem if not the understanding of the why or how to fix it. #3 is a bound control, all the others are unbound. Another detail is that #3 seems to get enabled but it also seems to get set to locked as I can open the combo box but cannot select anything.

I could change this control to unbound and set a value list but it is very feasible that the contents could change so it would be best to leave it as a bound control.

Again, any advice is appreciated.

Thanks

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

[rpeare]

What are the control types of the controls? combo boxes? check boxes? option groups?

You should also have a reciprocal statement for if your choice does NOT match *billing* like

Private Sub Tracking_Reason_Change()
Dim ctl As Control

If [Tracking Reason] Like "*Billing*" Then
    For Each ctl In Screen.ActiveForm.Controls
        If ctl.Tag = "Adj" Then
            ctl.Enabled = True
        Else
            ctl.enabled = false
        End If
    Next ctl
End If

End Sub

I would also consider breaking this into functions particularly for large numbers of controls (like you're talking about).

I would also throw in a debug.print statement (debug.print ctl.name) inside your if ctl.tag = "adj" statement to see if it's even getting to the controls that are 'bad' in your example.

i.e. if your controls have ADJ but items 3 and 4 have a tag of ADJx your code will fail because you're looking for an exact match you can try instead

if instr(ctl.tag, "Adj") then

EDIT geez louise 2 more posts while I was writing!

[rpeare]

You don't have to set a combo box's contents to a value list, you can set it dynamically with a SQL statement and setting the .controlsource property of the combo box to the SQL statement. I'm not a fan of bound forms, but I'm really not a fan of mixed (bound and unbound) forms.

[Xarkath]

What do you mean by '3 sets of fields'? - sounds like non-normalized structure.

This form is for tracking. It handles (or will handle) information for billing adjustments, initial enrollment letters and COBRA Election requests. We want all the tracking in one form as there are certain details that apply to all 3 sets and we want to avoid the redundency of filling out in multiple forms. However, no more than 2 sets of data would ever be submitted together. For example we may need to submit records for adjustments and enrollment letters or adjustments and COBRA but never for enrollment letters and COBRA. Or it could be that just 1 of the 3 sets would be tracked.

The idea is that the user would select the reason for tracking and only the fields that need to be completed will be available. It is a lot of hand holding but that is a whole other topic...

[Xarkath]

You don't have to set a combo box's contents to a value list, you can set it dynamically with a SQL statement and setting the .controlsource property of the combo box to the SQL statement. I'm not a fan of bound forms, but I'm really not a fan of mixed (bound and unbound) forms.

I am still a bit a newbie at this so I had not thought of going that route but I really like the idea.

Thanks

[rpeare]

This form is for tracking. It handles (or will handle) information for billing adjustments, initial enrollment letters and COBRA Election requests. We want all the tracking in one form as there are certain details that apply to all 3 sets and we want to avoid the redundency of filling out in multiple forms. However, no more than 2 sets of data would ever be submitted together. For example we may need to submit records for adjustments and enrollment letters or adjustments and COBRA but never for enrollment letters and COBRA. Or it could be that just 1 of the 3 sets would be tracked.

The idea is that the user would select the reason for tracking and only the fields that need to be completed will be available. It is a lot of hand holding but that is a whole other topic...

I am assuming based on your previous posts that you have the Cobra, enrollment and billing fields all on the same table. This is likely a mistake and not a normalized structure (There are a billion articles on normalization on the web if you want to look it up)

What you likely want is a structure where you have a table for your people, this would contain all the data that EVERYONE has in common (first name, last name, social, middle initial, birth date, etc)
And a separate table for each 'group' of items you want to track (one for cobra, one for enrollment, one for billing) that has a Foreign key link to the employee table. This can be done through forms/subforms or unbound forms pretty easily and you won't end up with table with a bunch of 'empty' information. Your secondary forms (cobra, billing, enrollment) would only need to contain the items that aren't collected on the 'main' employee record. Then when the employee makes their choice of what they want to do you can activate/deactivate (enable or visible properties) sections of your form to allow/disallow data entry.

[Xarkath]

I am assuming based on your previous posts that you have the Cobra, enrollment and billing fields all on the same table. This is likely a mistake and not a normalized structure (There are a billion articles on normalization on the web if you want to look it up)

What you likely want is a structure where you have a table for your people, this would contain all the data that EVERYONE has in common (first name, last name, social, middle initial, birth date, etc)
And a separate table for each 'group' of items you want to track (one for cobra, one for enrollment, one for billing) that has a Foreign key link to the employee table. This can be done through forms/subforms or unbound forms pretty easily and you won't end up with table with a bunch of 'empty' information. Your secondary forms (cobra, billing, enrollment) would only need to contain the items that aren't collected on the 'main' employee record. Then when the employee makes their choice of what they want to do you can activate/deactivate (enable or visible properties) sections of your form to allow/disallow data entry.

I admit that I may be going about this all wrong but this is not tied to a single table. Actually, with only a few exceptions none of the data is being pulled from tables at all. What I was planning to do was to setup append queries that would pull data from client tables as well as the data from the form itself using some of the form data as criteria. (hope that makes sense) Then I will code a submit button that will select the appropriate queries to be ran based on the tracking reason. Each query appending data to a table specific to the process, so adjustments would go to an adjustment table, enrollment letter requests to another table and the COBRA requests to a 3rd table. Then reporting tools will be made available to the appropriate staff that will be using that data in thier own processes. 2 of 3 will be appending to tables that are actually kept in completely different databases as otehr departments do the follow through work.

Rereading your post I think we are headed to the same place just using 2 different methods. And I have thought about using subforms and although I chose not to go that route I have not dismissed the option either.

Thanks

[Xarkath]

Thank you for the great advice. Changing the bound control to a SQL statement worked great. The debug command didn't help a whole lot at first but then when I selected the debug list to delete it I noticed that there were 2 empty lines at the bottem and that lead to me finding that I had inadvertantly added a tag to 2 labels. Once those were removed the code started working perfectly.