Combo box blank due to record source query only selecting records marked as active

[chopnflash]

Hi All,

Using Access 2003, SP3.

I'd like to be able to mark contacts in my database as inactive so, for example, it's not possible for product to be dispatched to a contact (a customer) that is inactive.

Any contact marked as inactive doesn't appear in a combo box list since the row source query selects only the contacts that are active.

This works fine, however if a record involving this contact is viewed (one that was entered before the contact was made inactive), the combo box is empty - the contact name doesn't appear since they aren't in the list any more.

After much searching, I found a partial solution in another thread - https://www.pcreview.co.uk/forums/combobox-blank-t3960886.html

I can confirm that this works, but I needed to add Me.Refresh to the end of the code. As expected, this solution does not work for a form in datasheet view.

Does anyone know of a solution that works for a form (or subform) in datasheet view?

I've managed to work through most of the issues I've had with Access, however this one has been surprisingly difficult to solve. Please help!

 

[chopnflash]

An update...

It seems there are several ways to handle this issue.

For a main form, the cleanest result can be achieved by using the method described in the thread I linked above. The code example in that thread doesn't handle a new form well - it fails because it doesn't handle the null value of the combo box on a new record.

I used an IIf statement to build the string correctly. Instead of putting the Combobox = ControlSource part at the end, put it first, right after the WHERE clause - put the following code right after 'WHERE' and if the rest of your code is sweet then it should work fine on a new record instead of throw an error because of the null...

" & IIf(Not (IsNull(Me.Combobox)), "tblTable.ControlSource =" & Me.Combobox & " Or ", "") & "

For a subform in datasheet view there are three possible ways to handle the issue:

1. Display all possible values in the combo box and use a VBA procedure to validate the data and disallow invalid selections.

2. Don't use subforms in datasheet view for editing - only use them for displaying data. Use text fields, combo boxes etc. on the main form for editing. Link these fields to the values of the currently selected record in the subform.

3. Use a combination of OnGotFocus and OnLostFocus events on the combo box and OnExit event on the subform. Set the RowSource to a query selecting all possible values. Use the OnGotFocus event to set the RowSource property to a query which selects only the valid options. Use the OnLostFocus and OnExit events to set the RowSource to a query which displays all values.

I suggest Me.Refresh after the code which does this. Also, it didn't seem to work well when the combo box is the first visible field in the subform, and it didn't seem to work well unless the OnExit procedure included a SetFocus event to move the focus away from the combobox.

Again, this is all in Access 2003 SP3. Other versions may exhibit different behaviour.

Good luck!