Program change of row source induces rogue values

Question

I have a combo-box on a form with:

ControlSource=Target Basis ---------- [A field in the form recordsource]
RowSource=Target Basis -------------- [A separate table of the same name]
RowSourceType = Table/Query

The table "Target Basis" has 20-odd values. The above behaves as expected ... until,

VB code changes the above as follows:

Select Case xyzVar   'xyzVar is taken from another field on form.
   Case xyzValue
      Me.Target_Basis.RowSourceType = "Value List"
      Me.Target_Basis.RowSource = vbNullString
      Me.Target_Basis.RowSource = "'Trend Channel'; 'Adjusted Trnd Ch.'"
      Me.Target_Basis.Locked = False
      Me.Target_Basis = "Choose"
   Case Else    'etc.

Upon completion of the above code, the text "Choose" appears in the combo as expected, but when the user drops the choice list, only one option appears, and it's the following text --

SELECT [Target Basis].Basis FROM [Target Basis]

I used documenter on the entire database and determined that the above query is not defined anywhere. I assumed that the system must be generating it from the controlSource name. To check this, I changed the name of the separate table (i.e. the rowSource table) from "Target Basis" to "Target Type". I got the identical behavior, except that now the single item in the list was --

SELECT [Target Type].Basis FROM [Target Type]

At that point I changed the name of the table back to "Target Basis"; however, the item that appears in the droplist remains as above -- despite that now, neither the query nor the table "Target Type" exist in the system. I tried closing, compacting and re-opening the Db; no change.

Further experimentation revealed the following:

When the code completes execution, the rowSourceType and rowSource are as they should be. I checked this by creating a button that outputs these properties. As soon as the user clicks on the combo's drop arrow however, the rowSource property changes to the SELECT statement above. The rowSourceType stays "Value List", which is why it appears in the drop list rather than executing.
If the controlSource is deleted, i.e. the control is unbound, the problem disappears.

Where is this query coming from??

Much obliged for any insight - IG

E Mett E Mett · Accepted Answer · 2013-06-11T13:51:38

Firstly, I'm not sure why you have:

Me.Target_Basis.RowSource = vbNullString

I think it is unnecessary and may be causing the problem.

Try adding Me.Target_Basis.Requery to refresh the combobox list.

Program change of row source induces rogue values

2 Answers