Change textbox backcolor (up to 20 conditions)

[GD]

I'm stumped!! I have a subform (SF01_POI_Screen_Analyzer; continuous form
view) with a textbox (txtDupCode1Inv) using a field (DupCode1) in a table
(T01_FormattedPOIScreen) of either null values or groups of numbers between 1
& 20.

I want to use different backcolors for each group of differing numbers
(1-20), but no backcolor for the nulls. The following code for 1 creates a
yellow backcolor for all textboxes in that field, including the 1s:

Private Sub Form_Current()

If Me.txtDupCode1Inv = 1 Then
Me.txtDupCode1Inv.BackColor = vbBlue
Else
Me.txtDupCode1Inv.BackColor = vbYellow
End If

End Sub

Private Sub txtDupCode1Inv_BeforeUpdate(Cancel As Integer)

If Me.txtDupCode1Inv = 1 Then
Me.txtDupCode1Inv.BackColor = vbBlue
Else
Me.txtDupCode1Inv.BackColor = vbYellow
End If

End Sub


Any ideas?! THANKS!!!

 

[Jeff Boyce]

Is there a chance that you have a table with a lookup data type defined for,
say the field underlying [txtDupCod1Inv]?

One way you could troubleshoot that code is to step through it and inspect
the actual value in [txtDupCode1Inv] ... I'm guessing is ISN'T a 1, thereby
forcing the ELSE condition.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[GD]

Thanks, Jeff! I think you're right. This field was created to detect
incidents of duplicate invoice numbers, and is populated using IIf
expressions to determine if an invoice number needs a duplication indicator,
and which one is needed. I thought that since the data type is set to
number, then the value in the field should be a number. Is this not correct?

One way you could troubleshoot that code is to step through it and inspect
the actual value in [txtDupCode1Inv] ...

I've never done this before. Can you give me a brief "step through" tutorial?

--
GD

Is there a chance that you have a table with a lookup data type defined for,
say the field underlying [txtDupCod1Inv]?

One way you could troubleshoot that code is to step through it and inspect
the actual value in [txtDupCode1Inv] ... I'm guessing is ISN'T a 1, thereby
forcing the ELSE condition.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm stumped!! I have a subform (SF01_POI_Screen_Analyzer; continuous form
view) with a textbox (txtDupCode1Inv) using a field (DupCode1) in a table
(T01_FormattedPOIScreen) of either null values or groups of numbers
between 1
& 20.

I want to use different backcolors for each group of differing numbers
(1-20), but no backcolor for the nulls. The following code for 1 creates
a
yellow backcolor for all textboxes in that field, including the 1s:

Private Sub Form_Current()

If Me.txtDupCode1Inv = 1 Then
Me.txtDupCode1Inv.BackColor = vbBlue
Else
Me.txtDupCode1Inv.BackColor = vbYellow
End If

End Sub

Private Sub txtDupCode1Inv_BeforeUpdate(Cancel As Integer)

If Me.txtDupCode1Inv = 1 Then
Me.txtDupCode1Inv.BackColor = vbBlue
Else
Me.txtDupCode1Inv.BackColor = vbYellow
End If

End Sub


Any ideas?! THANKS!!!

 

[Jeff Boyce]

You'd need to go back to the underlying table/field and check the design
view to see if that field has a Lookup datatype.

The 'step through' is fairly simple. Open the code in the VBA editor and
add either a breakpoint or a "Stop" command. Save and close. When you run
that portion of the application, the breakpoint (or stop) will halt the code
at that point and show you where it stopped. You can use the Immediate
window to see the value, or try 'floating your cursor' on top of the
control's name to see what value it has.

Another option is to insert a messagebox with something like:

Msgbox("YourField holds " & <<yourcontrolname>>)

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks, Jeff! I think you're right. This field was created to detect
incidents of duplicate invoice numbers, and is populated using IIf
expressions to determine if an invoice number needs a duplication
indicator,
and which one is needed. I thought that since the data type is set to
number, then the value in the field should be a number. Is this not
correct?

One way you could troubleshoot that code is to step through it and
inspect
the actual value in [txtDupCode1Inv] ...

I've never done this before. Can you give me a brief "step through"
tutorial?

--
GD

Is there a chance that you have a table with a lookup data type defined
for,
say the field underlying [txtDupCod1Inv]?

One way you could troubleshoot that code is to step through it and
inspect
the actual value in [txtDupCode1Inv] ... I'm guessing is ISN'T a 1,
thereby
forcing the ELSE condition.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm stumped!! I have a subform (SF01_POI_Screen_Analyzer; continuous
form
view) with a textbox (txtDupCode1Inv) using a field (DupCode1) in a
table
(T01_FormattedPOIScreen) of either null values or groups of numbers
between 1
& 20.

I want to use different backcolors for each group of differing numbers
(1-20), but no backcolor for the nulls. The following code for 1
creates
a
yellow backcolor for all textboxes in that field, including the 1s:

Private Sub Form_Current()

If Me.txtDupCode1Inv = 1 Then
Me.txtDupCode1Inv.BackColor = vbBlue
Else
Me.txtDupCode1Inv.BackColor = vbYellow
End If

End Sub

Private Sub txtDupCode1Inv_BeforeUpdate(Cancel As Integer)

If Me.txtDupCode1Inv = 1 Then
Me.txtDupCode1Inv.BackColor = vbBlue
Else
Me.txtDupCode1Inv.BackColor = vbYellow
End If

End Sub


Any ideas?! THANKS!!!

 

[John Spencer]

If you are using a continuous form, then the control on the form is going to
have just one color for all the images of the control on the form.
Conditional formatting will let you have up to 4 choices of the color, but
using VBA code as you currently have it means as you move from row to row
every image of the control should change to match whatever the current row
would set the control to.

So if you move to a row and txtDupCode1Inv is 1 then all images should have a
blue background, and if you move to a row and the value is not 1 all control
images should show a blue background.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County