Combo box - inactive value should disappear from combo box but not from form

[danyd]

I have a form with a combo box that displays active firms. When a firm is marked inactive, it should disappear from the combo box, but not from the record in the form. I checked the table that stores this data and the inactive firm is in the record, but it just doesn't show on the form.

Below is the code for the combo box.

SELECT dbo_IA_FIRM.IA_FIRM_ID, dbo_IA_FIRM.IA_FIRM_NAME
FROM dbo_IA_FIRM
WHERE (((dbo_IA_FIRM.INACTIVE)=False))
ORDER BY dbo_IA_FIRM.IA_FIRM_NAME;

Thank you for your help.

[June7]

This is a bound combobox used to select firm in a record?

In which case it should have nothing to do with display of records.

I have no idea what is causing the record display issue. Would have to review db. If you want to provide, follow instructions at bottom of my post.

[danyd]

The form that the combobox is in is called 'IA Estimate Form' and the name of the combobox is 'Firm'. The row source for the combobox is the code that was mentioned earlier (pulling records from the dbo_IA_FIRM table, and the Control Source is IA_FIRM_ID from the dbo_IA_ESTIMATE table.
I'd like to attach my database for you to look at, but can't seem to find the advanced post editor window. Could you tell me where to find it please. Thank you very much for your help.

[June7]

Click the Go Advanced button below the Quick Reply post window, next to the Post Quick Reply button.

[danyd]

haha... knew it had to be right in front of me... thanks. Now lets see if it shows up.

i did read about a work-around of displaying a textbox over top of the combobox, leaving just enough room at the end to still select the dropdown arrow, and this does work. but maybe i'm missing something else.

i do wonder if the Limit To List property should be set to No, but can't figure out how to do that and still display the firm name rather than the ID.

thanks for your help.

[June7]

I thought the issue was that the record does not display on form. Data not showing in combobox is to be expected when the combobox is a lookup with alias. You are saving an ID but displaying descriptor. When the combobox is filtered to exclude items the descriptor is not available for display. A nasty catch-22 and there is no really nice way to handle. The locked textbox on top of a combobox (sized to show only the drop arrow, as you noted) is about the only way to go.

[danyd]

ok... will do the textbox. Thank you so much for your help.

[June7]

Also, that requires including the 'lookup' table in the form RecordSource (join type 'Show all records from {your primary data table} ...') or DLookup expression in textbox.