check all comboboxes in form and if one has value of "fail"

[megatronixs]

Hi all,

I have a form with about 150 comboboxes. I need to check if one has a value "fail" and then set a field in the main form to red background.
I got the below code, but it fails to show the value of the combo box.

Dim ctl As Control
For Each ctl In Me.Controls
    If ctl.ControlType = acComboBox Then
        ctl.Value
    End If
Next

Any idea to get this done?

Greetings.

 

[Galaxiom]

There is nothing in that code to show anything. I would expect it to throw an error.

Moreover the 150 combos is an unlikely structure. Tell us more about what you are recording in this database.

 

[jdraw]

I agree with Galaxiom that 150 combos on a form seems extremely rare.

Here is code that may display result in immediate window.

Dim ctl As Control
For Each ctl In Me.Controls
    If ctl.ControlType = acComboBox Then
       if ctl.Value ="Fail" then  debug.print ctl.name & "  " & ctl.value
    End If
Next

 

[megatronixs]

hi Galaxiom,

The form is for quality check, and it is opened from the main form and linked to the record_id. There are 4 forms that will open based on criteria. There are a lot of question with each 5 comboboxes according to 1st check, 2nd check and so on. When a user does a check, he needs to select from the combo box "fail" or "pass".
What I need to check with the code that when some one closes the form and there is at least one combo box with "fail", then in the main form, I need to put in the first check field a "fail" and set the background of the text box to red.
Is there a way to loop trough all the combo boxes in the form and catch one that has a value of "fail" ?

greetings.

 

[megatronixs]

Hi Jdraw,

Your code works nice

I will not try to pass it to the text box in the form.

Greetings.

 

[Galaxiom]

When a user does a check, he needs to select from the combo box "fail" or "pass".

These tests results should be stored as separate records in a related table and displayed in a linked subform. The related table will have a field for the key from the parent table, one for the TestTypeID and one for the test result.

The subform will only have one combo and it will be displayed for each related record by using Datasheet form ContinuousForms mode.

The test for Fail can be conducted by querying the related table and will be much faster and more efficient than a control loop.

See this example of the structure for this type of form in post 3 of this thread. The records are only entered into the related table when Pass/Fail is selected on the subform.