Disappearing Data

[spalmateer]

Hello,
I need to find a work around to this problem. In my database I have a field in my employee table that marks an employee either active or non-active. Then on the form I use a query to filter the selection so only active employees can be chosen from the combobox. The problem is when an employee is selected as inactive, the data disappears in the form. For example, if I enter John Doe into the database as being the salesperson for an order, then 10 months down the road he quits. I mark him as inactive but whenever I view that old order in the form, the salesperson data has disappeared. However, when I look in the table its still there. I guess what's happening is that if the employee ID cannot be chosen in the combobox (from query), then the old data will not be shown. Any way around this? I want to limit new entry to only active employees, but I still want to show old inactive employees if they've already been entered. Thanks in advance for any help/ideas!
Scott

 

[Pat Hartman]

You need to remove the criteria from the combo's query and restrict the choices via code instead. You can use either the combo's BeforeUpdate event or the Form's BeforeUpdate event depending on when you want to display the error message.

 

[spalmateer]

Hi Pat,
Are you saying I should fill the combobox from code? Therefore the choices are not limited to the list? How do I fill the combobox from code? Could you elaborate a little? Thank You!
Scott

 

[Pat Hartman]

No, fill the combo with the query. Just remove the criteria that restricts the query to selecting only active records. Then in the BeforeUpdate event of the combo, you check the active flag. If the user has chosen an inactive record, display a message and cancel the update.

If Me.YourCombo.Column(2) = "Active" Then
Else
MsgBox "This employee is inactive. Please choose another.", vbOKOnly
Cancel = True
End If

The recordsource query for the combo should include the status flag. After you change the query, don't forget to change the properties of the combo to change the number of columns and change the column widths so that status will be displayed.

 

[spalmateer]

I'm still having a little trouble with this problem. Its really not a super big deal but more of an astetic problem. With the solution Pat presented, the user when entering data has to scroll through potentially hundreds of inactive records in the combobox. For example I'm using this for our selection of products on an order form, so when a user enters a new product from the combobox, they must also sift through the 1000's of older inactive products. It's kinda tedious. Any ideas or is this the difinitive way to go?? Thanks
Scott

 

[SteveA]

Why not sort the combo box so that all inactive products are at the end of the list. This way the user will see all active products first, and it will still allow information to be shown for inactive records.

Doing this and implementing the changes recommended by Pat will allow inactive records to still appear, prevent inactive records being selected for new records and reduce the need for users to scroll through numerous inactive entries in the combo box.

HTH
SteveA