Solved ComboBox Blank When Field Contains Data Not In Row Source (1 Viewer)

I have a combobox with the following characteristics:
Data
Control Source: ContractingOfficerName
Row Source: qryContacts_KOs
Row Source Type: Table/Query
Bound Column: 1 (ContactID, autonumber)
Limit to List: Yes
Allow Value List Edits: Yes
List Items Edit Form:
Inherit Value List: Yes
Show Only Row Source Values: No
Input Mask:
Default Value:
Validation Rule:
Validation Text:
Enabled: Yes
Locked: No
Auto Expand: Yes

Format
Column Count: 2 (ContactID, ContactName)
Column Widths: 0",1.5"

Control Source: ContractingOfficerName
Record Source: tblCases
RescordSet Type: DynaSet

Row Source: qryContacts_KOs
Fields:
ContactID
Table: quniContactsTags, Sort: , Show: yes, Criteria:
ContactName
Table: quniContactsTags, Sort: ascending, Show: yes, Criteria:
ContactTag
Table: quniContactsTags, Sort: , Show: yes, Criteria: "Contracting Officer"
ContactStatus
Table: quniContactsTags, Sort: , Show: yes, Criteria: "Active"

Problem:
Field stores the ContactID, Dropdown List for ComboBox shows contacts from the qryContacts_KOs query which only shows active Contracting Officers. When a contracting officer becomes inactive, they no longer show up in the query or the dropdown list and the combobox displays nothing. The ContactID is still stored in the field but the name is no longer displayed.

Desired outcome:
Continue to display the contracting officer name in the field even if they are not in the query result or the dropdown. The purpose of having the contracting officer not show in the query results is to have a shorter dropdown list by only showing active contracting officers in the dropdown.

One idea is to prefix inactive rows with "x-" so that inactive data still appears, but sorts to the bottom of the list. Imagine if your combo RowSource was something like...
hth
Mark

this is another way to do it.
change the rowsource of your ContactingOfficerName combobox to:
(yourTable, there should be replaced with actual table name)

Add code to ContactingOfficerName combobox's OnGotFocus and OnLostFocus Events:
again, change the Where condition (ActiveField=True) there with the correct fieldname and condition.

This will satisfy your desired outcome

Thank you.

Using "SELECT tblContacts.ContactID, tblContacts.ContactName FROM tblContacts;" as the row source for the ContractingOfficerName ComboBox displays the name of the contact regardless of what is showing in the dropdown list, enabling display of legacy data that is not a current dropdown list selection.

Using the following code for the got focus event limited the dropdown list to active contracting officers.
Private Sub comboContractingOfficerName_GotFocus()
Dim SQLText As String
SQLText = "SELECT quniContactsTags.ContactID, quniContactsTags.ContactName, quniContactsTags.ContactTag "
SQLText = SQLText & "From quniContactsTags "
SQLText = SQLText & "WHERE quniContactsTags.ContactTag=""Contracting Officer"" " 'AND quniContactsTags.ContactStatus IS NULL "
SQLText = SQLText & "ORDER BY quniContactsTags.ContactName;"
Me.comboContractingOfficerName.RowSource = SQLText
End Sub

I am not sure of the purpose of the code for the lost_focus event and would appreciate it if you would clarify that.

I think I figured it out what the lostfocus code is for.

Using "SELECT tblContacts.ContactID, tblContacts.ContactName FROM tblContacts;" as the row source for the ContractingOfficerName ComboBox displays the name of the contact regardless of what is showing in the dropdown list, enabling display of legacy data that is not a current dropdown list selection.

Using the following code in the combobox gotFocus event displays the limited list of selections when the combobox is clicked. This changes the row source to what is needed for the dropdown list.

Private Sub comboContractingOfficerName_GotFocus()
Dim SQLText As String
SQLText = "SELECT quniContactsTags.ContactID, quniContactsTags.ContactName, quniContactsTags.ContactTag "
SQLText = SQLText & "From quniContactsTags "
SQLText = SQLText & "WHERE quniContactsTags.ContactTag=""Contracting Officer"" " 'AND quniContactsTags.ContactStatus IS NULL "
SQLText = SQLText & "ORDER BY quniContactsTags.ContactName;"
Me.comboContractingOfficerName.RowSource = SQLText
End Sub

Using the following code in the combobox lostfocus event returns the row source back to the original enabling display of the legacy data (name that is not on the dropdown list).

Private Sub comboContractingOfficerName_LostFocus()
Me.comboContractingOfficerName.RowSource = "SELECT tblContacts.ContactID, tblContacts.ContactName FROM tblContacts;"
End Sub

Fast learner!

I am guessing you are involved with DoD contracting?

i supposed he is.

i was in saudi arabian army ordnance corp, under american contractor, saudi operations and maintenance (somc) for 10 years here in saudi. our site administrator is active colonel in us army. Col. Aycock. We have many ex- american, british, german, korean military personal working there. our job is to maintain army ground equipment. anything that does not fly or at sea, we repair. Like hammer, M60 tanks, howitzer, fire controls, truck mounted rockets, small arms, etc. The newer tanks (M1A2, M1A1) are maintained by another contractor, General Dynamics. Have worked there from 1997-2007.

But i am not Ex military. got the chance to worked there because i have credits in my ROTC during college.

Interesting, the activity (US Navy) that employees me fixes nothing BUT ships. I started off making a logistics application for them and they asked me to make/maintain a maintenance module for them.

It gets frustrating at times, but I'm getting paid to play with Access and live abroad. Loving the way my life sucks...!

Just involved with government contracting.