Active / Inactive data visibility in combo box

[chew1920]

I have a combo box that uses a list from a table that has both active and inactive rows in the table. I want the users to only be able to select active rows, but still be able to display records that are inactive when the combo box is not selected. When I use a query that only lists the active rows, the records that are inactive show no information in the dropdown. What are my options to be able to display active and inactive rows, but only display active rows in the drop down. Also if I must include inactive rows in the drop down is there a way that I can use a strike trough or color change for the inactive rows?

Tahnks for your help

 

[fneily]

On the Rowsource of the combo box place your query for active rows. eg. SELECT DISTINCT [SomeField] FROM tblSomeTable WHERE [ActiveFieldIndicator] = True ORDER BY [somefield];

Place the table as source for your form.

 

[chew1920]

When I use a query in the rowsource that is looking for active records, the rows from the main table do not show up in the list. I am trying to link based on the foreign key and then display the value associated with that record.

TableA
ID TableB_ID
-----------------
1 1
2 1
3 2

TableB
ID Type ActiveSw
------------------------
1 Open True
2 Closed False

What I want to happen is that user will see a Type of "Closed" when they are on ID = 3 of TableA, but if they went to look in the drop down they would only see "Open." I am trying to set up this database so that records will never be deleted, just made inactive, so if an old record is viewed I want the user to be able to see what value was in the field, but only to be able to select the current values if they wish to make a change.

 

[chew1920]

After some playing around, I think that what I really need is for the listbox to have 2 different rowsources. I have tested using the GotFocus() and LostFocus() to switch between the different rowsources that will be needed, so that users will be able to see historical data, but if they want to change the information then they will only get the current options. Tahnk you for your help