Loop works for textboxes but not combo boxes

[bakerld]

See below and let me know if you have any ideas. Thanks!
________________________________

Hello fellow VBA programmers,

I have the code below in one database to do stuff to all the textboxes on a form. I've copied it into another DB to disable some combo boxes on a form, but it doesn't want to work on the combo box controls. I get a "Type Mismatch" error each time it hits the 'Next cbo' line. And it also does not disable the control.

Any ideas welcome and appreciated!

____________________________________________
CODE:

Private Sub Form_Load()
Dim cbo As ComboBox

For Each cbo In Forms(frm).Controls
        If Not IsNull(cbo.Value) Then
            ctl.Enabled = False
        Else
            ctl.Enabled = True
        End If
Next cbo

End Sub

 

[boblarson]

Try this instead:

Private Sub Form_Load()
Dim ctl As Control

For Each ctl In Forms(frm).Controls
    If ctl.ControlType = acComboBox Then
        If Not IsNull(ctl.Value) Then
            ctl.Enabled = False
        Else
            ctl.Enabled = True
        End If
   End If
Next ctl

End Sub

 

[rainman89]

Try this code that was given to me by ajetrumpet

Dim c As Control 
For Each c In Me.Controls
    
If TypeOf c Is ComboBox Then
If Not IsNull(c) Then

etc...

 

[bakerld]

That looks much more logical, but i tried it and got error 2186: "This property isn't available in design view" on:

...
If Not IsNull(ctl.Value) Then
...

 

[bakerld]

Thanks Fellas - now for an array problem

Thanks to both of you. I used a combination of both responses - i used "Me." instead of Forms(frm) and used Bob's code technique and am no longer getting the error.

Now for a new challenge if you can help. I have never used an array because I just don't understand them. I need to put them to practical use before I can understand them. Plus, I'm taking a programming fundamentals course in college and arrays are my next chapter so I REALLY need to figure out a way to 'get' them. Perhaps you can help with this problem.

I have a single record in a table that contains a field for each individual 'approver' up to 12 'approvers'. There are 12 of these fields for each record in my table like: App1, App2, App3, etc up to App12.

How can I 1) get those values into an array and then 2) use them to populate 12 text boxes on a form?

I would very much appreciate some more help here. Thanks guys!!!

-Laura

 

[boblarson]

I have a single record in a table that contains a field for each individual 'approver' up to 12 'approvers'. There are 12 of these fields for each record in my table like: App1, App2, App3, etc up to App12.

Laura -

I'm sure you don't want to hear it, but that type of repeating field is definitely not normalized and should be done differently. However, why do you have it this way? Is there a specific reason why you chose the non-normalized path instead? What is the purpose of having those 12 fields on the form?

 

[bakerld]

So glad you asked!

No, it's OK Bob. I know they are not normalized. This entire project is becoming a nightmare!

Allow me to explain...

There is a high visibility workflow database that is designed around a VERY old MS Excel template. Years ago, not everyone in the company had Access licenses and they didn't want to pay for them, nor did the developer know how to build run-time solutions. So this workflow database (which is already built and in use) currently pulls in data from this Excel template I'm referring to (we call it X-Form). It has tons of code in it to make it run sort of like an application. So now I'm tasked with rebuilding 'X-Form' (currently the Excel template) in Access.

The data structures in this database are FAR from normalized and are extremely annoying and frustrating. I am rebuilding the Excel template in Access, but I cannot redesign the data structure that was based on the Excel template because 1) the powers that be do not want to dedicate resource to it since the whole program is eventually going to become a web app, and 2) there is 5 years worth of historical data in the current workflow database that links to this tool I'm rebuilding in Access. What I'm building is a temporary solution to a much larger issue. So I have to rebuild it around the existing data structure, which was built to handle imported data from Excel, not Access.

This 'Approvers' thing is only one example of the horridness of what I'm facing. Another example... TONS of non-indexed fields , no primary keys and several NUMBER fields that are TEXT data types in the tables... and so on and so on.

In this case, the 12 'Approvers' were originally designed in the old Excel template as one Approver per field numbered App1, App2, etc... in a single record. So now I have to make this work somehow without changing the existing data structure.

Lovely, huh?

Any ideas?