Combo box - showing active items only

S

Shelley

I have a combo box using a Salesrep table as its RowSource. In the Salesrep
table, I have an 'Active' checkbox. I want the combo box to show only Active
salesreps so I changed the RowSource to an SQL statement to achieve that. The
problem is if you go back and look at historical records for a sale, if the
Salesrep is no longer active, the combo box appears to be empty. I know the
data is there but can't be seen because of my RowSource and because I have
set LimitToList to Yes.

I have created a workaround by placing a TextBox behind the combo box
(disabled and locked). The TextBox is also bound to the same field. I made
the combo box transparent so that if the combo box is empty, you will see
through it to the text box. Does anyone have a more elegant solution for this
issue?
 
D

Douglas J. Steele

Create a query that only returns the active reps, and use the query as the
RowSource for the combo box.
 
S

Shelley

I've done that. The issue is that if you look at a historical record and the
rep in that field is not an active one, it will not show up on the form, even
though the value is in the underlying table.
 
D

Douglas J Steele

What's the point of your "Active" checkbox, then, if it still allows those
records to be seen?
 
S

Shelley

I don't want the people choosing from the combo box to have to deal with
seeing inactive salesreps within the combo box when they are making a
selection for a new invoice. However, if they are looking at a historical
invoice, I want them to see who the salesrep was then, even if they are not
active now.
 
D

Douglas J Steele

Afraid you can't have it both ways.

You may have to rethink your interface.
 
S

Shelley

Actually, I do have it both ways (if you read my original post). I wanted to
know if there was a more elegant method.

Thanks for your effort.

Shelley
 
Top