Array Practice, Missing Object

[SanguineWizard]

Hi everyone.

I'm trying to make 12 different fields turn red if the value inside the field is outside a certain range. It works if the field is checked individually, but I would like to practice making arrays and not copying me code 12 times.
I'm told that "LaserField(12).BackColor = RGB(255, 255, 255)" & "LaserField(12).BackColor = RGB(255, 0, 0)" is missing an object. But I thought I had defined my objects. Please help me make sense of this.

Code which works:

Private Sub Laser1_AfterUpdate()
Dim checkerror As Integer
checkerror = Forms![Product Tests]![UF TestGigaLaserFinal].Form![UF TestGigaLaserLyspanelLeft]![Laser1]
If checkerror >= 450 And checkerror <= 550 Then


Forms![Product Tests]![UF TestGigaLaserFinal].Form![UF TestGigaLaserLyspanelLeft]![Laser1].BackColor = RGB(255, 255, 255)


Else


Forms![Product Tests]![UF TestGigaLaserFinal].Form![UF TestGigaLaserLyspanelLeft]![Laser1].BackColor = RGB(255, 0, 0)
End If
End Sub

Code Which doesn't work, due to "no objects":

Private Sub Form_Current()
Dim checkerror As Integer
Dim LaserField(12) As Variant


LaserField(1) = Forms![Product Tests]![UF TestGigaLaserFinal].Form![UF TestGigaLaserLyspanelMid]![Laser1]
LaserField(2) = Forms![Product Tests]![UF TestGigaLaserFinal].Form![UF TestGigaLaserLyspanelMid]![Laser2]
LaserField(3) = Forms![Product Tests]![UF TestGigaLaserFinal].Form![UF TestGigaLaserLyspanelMid]![Laser3]
LaserField(4) = Forms![Product Tests]![UF TestGigaLaserFinal].Form![UF TestGigaLaserLyspanelMid]![Laser4]
LaserField(5) = Forms![Product Tests]![UF TestGigaLaserFinal].Form![UF TestGigaLaserLyspanelMid]![Laser5]
LaserField(6) = Forms![Product Tests]![UF TestGigaLaserFinal].Form![UF TestGigaLaserLyspanelMid]![Laser6]
LaserField(7) = Forms![Product Tests]![UF TestGigaLaserFinal].Form![UF TestGigaLaserLyspanelMid]![Laser7]
LaserField(8) = Forms![Product Tests]![UF TestGigaLaserFinal].Form![UF TestGigaLaserLyspanelMid]![Laser8]
LaserField(9) = Forms![Product Tests]![UF TestGigaLaserFinal].Form![UF TestGigaLaserLyspanelMid]![Laser9]
LaserField(10) = Forms![Product Tests]![UF TestGigaLaserFinal].Form![UF TestGigaLaserLyspanelMid]![Laser10]
LaserField(11) = Forms![Product Tests]![UF TestGigaLaserFinal].Form![UF TestGigaLaserLyspanelMid]![Laser11]
LaserField(12) = Forms![Product Tests]![UF TestGigaLaserFinal].Form![UF TestGigaLaserLyspanelMid]![Laser12]


checkerror = LaserField(12)
If checkerror >= 450 And checkerror <= 550 Then


LaserField(12).BackColor = RGB(255, 255, 255)


Else


LaserField(12).BackColor = RGB(255, 0, 0)
End If
End Sub

Any help is appreciated. Thanks

[CJ_London]

Have you considered using conditional formatting?

and If you want to go the code route, loop through the controls and if control name like’laser*’ then if outside range then apply your back colour

with regards your long reference to the control, assuming this code is in the form where the controls are, you just need the control name, prefix with me. If you want

on my phone so can’t really provide example code

[June7]

VBA is fine if form displays only one record at a time. VBA setting properties affects ALL instances of control so every record shows same setting - essentially useless for continuous or datasheet form. Use Conditional Formatting.

I think your code is populating array with values of controls, not control names or objects, hence the error message.

I presume you really mean "textboxes" when you say "fields". Looping through your controls could be like:

Dim x As Integer
For x = 1 to 12
With Me("Laser" & x)
    .BackColor = IIf(.Value >= 450 And .Value <= 550, RGB(255, 255, 255), RGB(255, 0 , 0))
End With
Next

[Micron]

if the value inside the field is outside a certain range

Looping is fine if the range is the same for all of them. If not, I think you'd need a Select Case block. Learning how to create and use arrays is fine, but IMO they're not suitable for this.

The reference looks odd - like a subform is being referenced 2x
Forms![Product Tests]![UF TestGigaLaserFinal].Form![UF TestGigaLaserLyspanelMid]![Laser9]

[SanguineWizard]

Have you considered using conditional formatting?

and If you want to go the code route, loop through the controls and if control name like’laser*’ then if outside range then apply your back colour

with regards your long reference to the control, assuming this code is in the form where the controls are, you just need the control name, prefix with me. If you want

on my phone so can’t really provide example code

Never tried conditional formatting before. Didn't even know it was a thing. I'm going to look into that and see if it can work with what I'm trying to achieve. Thank you.

[SanguineWizard]

VBA is fine if form displays only one record at a time. VBA setting properties affects ALL instances of control so every record shows same setting - essentially useless for continuous or datasheet form. Use Conditional Formatting.

I think your code is populating array with values of controls, not control names or objects, hence the error message.

I presume you really mean "textboxes" when you say "fields". Looping through your controls could be like:

Dim x As Integer
For x = 1 to 12
With Me("Laser" & x)
    .BackColor = IIf(.Value >= 450 And .Value <= 550, RGB(255, 255, 255), RGB(255, 0 , 0))
End With
Next

Yes, textboxes. Sorry. I should have posted a picture of the form, so people can see what I'm talking about. I dunno how conditional formatting works, but it seems like I'm trying the long and hard road with code, and should try the formatting instead. I'm just trying to get more familiar with VBA and coding in general.

Would that piece of code be able to detect which fields I'm refering to?

[SanguineWizard]

Looping is fine if the range is the same for all of them. If not, I think you'd need a Select Case block. Learning how to create and use arrays is fine, but IMO they're not suitable for this.

The reference looks odd - like a subform is being referenced 2x
Forms![Product Tests]![UF TestGigaLaserFinal].Form![UF TestGigaLaserLyspanelMid]![Laser9]

I dunno if it could be done easier or shorter, but I'm basically referring to a textbox within a subform, that is inside another subform. I realise this may seem wierd, but it's because I have made many different kinds of testforms inside the "Product Tests" form.
This particular form I had to chain together in a weird way to display the same form (and table) but in different ways defined by postion. Venstre = Left. Midt = Middle. Højre = Right.

[CJ_London]

your form is quite complex and not clear whether you have a singie form with 12 controls, or a continuous form with 12 rows

I dunno how conditional formatting works

easy:

1. in design view select all the controls you want to conditionally format with the same rule (e.g. between 450 And 550)
2. on the ribbon select the format tab then conditional formatting. If you are using 2003 or earlier it will be a bit different. The rule manager window will open
3. in the rules manager click 'new rule'
4. populate with the following 'Field value is'...'between'...450....550
5. select red back colour from the preview section
6. click OK, then apply


Whenever one of those controls has a value within the specified range, the back colour of that control will be red

[June7]

Would that piece of code be able to detect which fields I'm refering to?

Not sure what you are asking for. The code constructs names of textboxes. Assuming textboxes have same names as fields - if not, adjust code. But again, conditional formatting is probably better approach.