Change font if #Name? jjones 12/4/2008 4:02:02 PM 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

Re: Change font if #Name? Marshall Barton <marshbarton[ at ]wowway.com> 12/4/2008 7:32:35 PM jjones wrote: [Quoted Text] >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. -- Marsh MVP [MS Access]

Re: Change font if #Name? "Mike Painter" <mddotpainter[ at ]sbcglobal.net> 12/4/2008 7:45:46 PM jjones wrote: > 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. [Quoted Text] > > 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.

Re: Change font if #Name? jjones 12/5/2008 2:40:38 PM 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! :-) "Marshall Barton" wrote: [Quoted Text] > jjones wrote: > > >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. > > -- > Marsh > MVP [MS Access] >