Excluding old records from combo box

[Maryamka]

Hi,
I'm struggling with a problem. To simplify, I have a relational database and a main from with a bunch of combo boxes. One of the combo boxes shows a list of users. The list of users is linked to the UserID (in the user table) but actually displays the user's name. When a user leaves I do not want to delete him/her because I need to keep old entries of work done for that user, however I do not want the user to continue to appear in the combo box list. When I tried to chnage the combo box and based it on a query which excludes old users, all old entries for those users showed a blank user name. Is there a way to get around this? I would like to see the names of old users in old records but not have the option to select them for new jobs. Any help will really be appreaciated! Thank you....

 

[mbailey]

I'd add a field to the same table that the UserId is located in and make it a yes/no data type. Set it to yes whenever/however you want to determine that the user is an "old user". Then set the rowsource property of your combo to exclude all users that are set to yes. Hope this helps.

 

[mbailey]

You could also use a date calculation in the where clause of the combo's rowsource property and eliminate the need for the yes/no field.

 

[Pat Hartman]

As you have seen, you need to include the old users in the list or records you view that reference them will show blank in the name column. I would solve the problem by adding an active flag to the user table and including that column in the combo box so following the username would be the active flag. Then in code, you'll need to check the active flag when a user is selected to ensure that only active users are selected. Put the test in the AfterUpdate event of the combo box because you only want to do the edit in the case where the value of the combo box is actually changed.

 

[Maryamka]

Thank you for your help everyone. I have a field which shows the termination date of a user who has left. I now have everything working. I have one form for data entry of jobs with the list of users limited to existing users and another form which lists all jobs from the past which includes all users.