How to change combo box text conditionally?

[HarmonyGirl]

In my Access 97 database, I have a form with a combo box that allows you to select an employee.

In my Employee table, I have a Yes/No field called Active. This field value determines whether the employee is employed or has left the company.

In records assigned to inactive employees, I would like the text value in the Employee combo box to be formatted differently, perhaps in Italic vs. a different forecolor.

I know I can run a SQL query against the Employee table, but I need to evaluate the selected employee in the current record/form, not just run a query against all records in the table.

How would I do this?

 

[KDg]

Hi Harmony,

if you drop the Active field into your combo box ( set it's width to 0 if you dont want to see it ) you can use something like

Private Sub YourCOmboBox_AfterUpdate()
If Me.YourCOmboBox.Column(1) Then
Me.YourCOmboBox.FontItalic = False
Else
Me.YourCOmboBox.FontItalic = True
End If
end sub

you'll just need to change the reference to Column(1) to whichever column you drop the Active field onto

HTH

Drew

 

[HarmonyGirl]

Thanks for the quick reply!

When I added this code to my form, it works ... except it changes all to italic, and then when I reopen the form, all selections in the field remain italic.

I tried adding similar code to the form's OnCurrent event ... what am I still missing?

Also, I'm thinking about adding a message box in case someone tries to assign a record to an inactive employee. Can I use the same column reference to evaluate whether the message box should appear?

 

[KDg]

Hi Again,

to your second question - yes you can use that field to validate whatever you like.

for the first, sorry you're right it will change all the records in your box and i don't believe there is any way around it. It shouldn't permanently change the settings though, unless you've gone into design and saved it that way. How about a label or similar that displays "Inactive Employee" and just toggle it's visibility After_Update? You could then also toggle enabled on other fields to stop your users attempting to add records for inactive employees. It's better to simply stop people doing something than let them do it and then tell them they're wrong.

Sorry that doesn't sort the italics - i didn't really think it thru - hope it helps anyway

Drew