Multiple conditional formatting based on value of preceding textbox

[muchjubble]

Hey guys, I'm new here so a big HELLO to all who are reading this. I'm really glad to be here as there is so much I want to learn.

I wonder if anyone can help me with this?

I have 7 subforms, each containing 30 textboxes. Each field represents a half hour slot, in a 15 hour day. The fields are populated in two ways: 1. from linked tables from an online booking system and 2. user input on the forms. There are 3 field values that are constant on the website and when this data is pulled into the form I want to change the backcolor of the textbox. Now, as these entries are always for one hour but our forms need to show half hour slots, to allow for user entries, I need to change the backcolor of the textbox being evaluated plus the next one in line, to indicate the hour is booked.

I can do this of course with conditional formatting, but that would mean adding in approx. 400 rule expressions. Unless there is a way of referring to a textbox control without having to give its name? And then the following half hour field's textbox?

I tried looping through the controls but again couldn't figure out a way of referencing the next one in line.

Any help would be fantastic

/muchjubble

 

[Galaxiom]

The loop would usually be the way to go. A naming convention with a sequence of numbers would allow you to refer to the previous control.

However I would like to hear more about your data structure. Do you have anything like seven very similar tables with thirty fields or a table with 210 fields?

 

[muchjubble]

Hi Galaxiom Many thanks posting

I have 7 similar tables. tblMonday, tblTuesday, etc.

The table fields are like:

tblMonday
m0800, m0830, m0900, .....
tblTuesday
t0800, t0830, t0900, ....

I tried:

Dim ctl As Control

For Each ctl In Me.Controls
    If (ctl.ControlType = acTextBox) And (Left(ctl.Value, 3) = "Mat" Or Left(ctl.Value, 7) = "Machine") Then
        ctl.BackColor = RGB(255, 255, 204)
        
    End If
Next ctl

But this produces a 438 error - Object doesn't support this property or method.

Should I be trying something like Do While count <=30?

thanks again

 

[Galaxiom]

The problem is caused by testing controls that don't have a Value property such as labels and boxes.

Test for the ControlType being acTextbox first then only test for value if that returns True.

I will comment on the data structure later.

 

[muchjubble]

Thanks, but the code I posted above already checks for controltype textbox.

 

[Galaxiom]

Thanks, but the code I posted above already checks for controltype textbox.

Your code check for the ControlType at the same time that it checks for the value hence all controls on the form will be tested for Value.

Those which don't have a Value property will break.

For Each ctl In Me.Controls
    If (ctl.ControlType = acTextBox) Then
        If  (Left(ctl.Value, 3) = "Mat" Or Left(ctl.Value, 7) = "Machine") Then
            ctl.BackColor = RGB(255, 255, 204)        
        End If
    End If
Next

 

[muchjubble]

oops! ok I understand now, my apologies, and thank you

 

[muchjubble]

Still trying to work out how to change the backcolor of the next textbox though. Would be great if you can give me a hint

For Each ctl In Me.Controls
    If (ctl.ControlType = acTextBox) Then
        If (Left(ctl.Value, 3) = "Mat" Or Left(ctl.Value, 7) = "Machine") Then
            ctl.BackColor = RGB(255, 255, 204)
            'and make the next textbox backcolor also yellow
        Else
            ctl.BackColor = RGB(255, 255, 255)
        End If
    End If
Next