Change font if #Name?

[jjones]

I have a simple '97 database for tracking our equipment inventory, and one
component of that references the status of the equipment—“Deployedâ€, “On
Holdâ€, or “In Stockâ€. For doing a quick stock check, I have a continuous
form based on an underlying query. The form has a row for each type of
equipment and a column for each status where the records are counted (and I
have the back color of the text boxes color coded…i.e. Deployed=red, On
Hold=yellow, In Stock=green) So my row 1 looks something like “Widget xâ€,
then column 1 has a total of the # of widget x’s deployed, column 2 - # of
widget x’s on hold, column 3 - # of widget x’s in stock. The only
problem—and this is really more cosmetic than being an actual functional
problem—but if there’s not any equipment at all in any particular
status—let’s say none of my various widget types are on hold—then I see a
whole column full of #Name? errors. I’m trying to write an event procedure
with an if statement so that if the field contains this error then the
forecolor (font) is changed to match the backcolor of the text box so that I
won’t actually see the #Name? errors. In Excel this would be something like
if(iserror(…). I tried the following code and inserted it into events for
before update, after update (I guess that would work?).

Private Sub On_Hold_AfterUpdate()
If Me.[On Hold] = "#Name?" Then
Me.On Hold.ForeColor = 10092543
ElseIf Me![On Hold] <> "#Name?" Then
Me.On Hold.ForeColor = -2147483640
End If

End Sub

 

[Marshall Barton]

I have a simple '97 database for tracking our equipment inventory, and one
component of that references the status of the equipment—“Deployed”, “On
Hold”, or “In Stock”. For doing a quick stock check, I have a continuous
form based on an underlying query. The form has a row for each type of
equipment and a column for each status where the records are counted (and I
have the back color of the text boxes color coded…i.e. Deployed=red, On
Hold=yellow, In Stock=green) So my row 1 looks something like “Widget x”,
then column 1 has a total of the # of widget x’s deployed, column 2 - # of
widget x’s on hold, column 3 - # of widget x’s in stock. The only
problem—and this is really more cosmetic than being an actual functional
problem—but if there’s not any equipment at all in any particular
status—let’s say none of my various widget types are on hold—then I see a
whole column full of #Name? errors. I’m trying to write an event procedure
with an if statement so that if the field contains this error then the
forecolor (font) is changed to match the backcolor of the text box so that I
won’t actually see the #Name? errors. In Excel this would be something like
if(iserror(…). I tried the following code and inserted it into events for
before update, after update (I guess that would work?).

Private Sub On_Hold_AfterUpdate()
If Me.[On Hold] = "#Name?" Then
Me.On Hold.ForeColor = 10092543
ElseIf Me![On Hold] <> "#Name?" Then
Me.On Hold.ForeColor = -2147483640
End If

End Sub

You could use the IsError function:
If IsError(Me.[On Hold]) Then

But I suspect that the underlying problem is in the form's
record source. Based on the nature of your question, I
surmise that the form's record source is a cross tab query
that does not have that column in that situation. If I am
guessing correctly, then I think all you really need to do
is set the query's Column Headings property to:
“Deployed”,“On Hold”,“In Stock”
to force all three columns regardless of the existence of
any values.

 

[Mike Painter]

then I see a whole column full of #Name? errors.

This is not a cosmetic problem but an error in your code.

It probably comes from
"Me.On Hold.ForeColor = 10092543"
which sholud be
Me.[On Hold].ForeColor = 10092543

better yet would be to get rid of the spaces in your field names and use
OnHold to solve this problem.

Private Sub On_Hold_AfterUpdate()
If Me.[On Hold] = "#Name?" Then
Me.On Hold.ForeColor = 10092543
ElseIf Me![On Hold] <> "#Name?" Then
Me.On Hold.ForeColor = -2147483640
End If

Private Sub On_Hold_AfterUpdate()
If Me.[On Hold] = "#Name?" Then
Me.On Hold.ForeColor = 10092543
Else
Me.On Hold.ForeColor = -2147483640
End If

If it *not* equal to "#name?" then the else portion is run and there is no
need to see if it's not becasue it has to be.

 

[jjones]

Awesome! Thanks Marshall!!! I took your advice on actually fixing the
source of the problem rather than masking it. I didn’t know you could
actually force column headings that weren’t there…good to know. 1000 points
to you my man!

I have a simple '97 database for tracking our equipment inventory, and one
component of that references the status of the equipment—“Deployedâ€, “On
Holdâ€, or “In Stockâ€. For doing a quick stock check, I have a continuous
form based on an underlying query. The form has a row for each type of
equipment and a column for each status where the records are counted (and I
have the back color of the text boxes color coded…i.e. Deployed=red, On
Hold=yellow, In Stock=green) So my row 1 looks something like “Widget xâ€,
then column 1 has a total of the # of widget x’s deployed, column 2 - # of
widget x’s on hold, column 3 - # of widget x’s in stock. The only
problem—and this is really more cosmetic than being an actual functional
problem—but if there’s not any equipment at all in any particular
status—let’s say none of my various widget types are on hold—then I see a
whole column full of #Name? errors. I’m trying to write an event procedure
with an if statement so that if the field contains this error then the
forecolor (font) is changed to match the backcolor of the text box so that I
won’t actually see the #Name? errors. In Excel this would be something like
if(iserror(…). I tried the following code and inserted it into events for
before update, after update (I guess that would work?).

Private Sub On_Hold_AfterUpdate()
If Me.[On Hold] = "#Name?" Then
Me.On Hold.ForeColor = 10092543
ElseIf Me![On Hold] <> "#Name?" Then
Me.On Hold.ForeColor = -2147483640
End If

End Sub

You could use the IsError function:
If IsError(Me.[On Hold]) Then

But I suspect that the underlying problem is in the form's
record source. Based on the nature of your question, I
surmise that the form's record source is a cross tab query
that does not have that column in that situation. If I am
guessing correctly, then I think all you really need to do
is set the query's Column Headings property to:
“Deployedâ€,“On Holdâ€,“In Stockâ€
to force all three columns regardless of the existence of
any values.