Combo Box control refusing to reflect update of SQL Row Source

[mdlueck]

I have a very strange one presently... In this application, I have perhaps 10 pick lists which are Combo Box controlled (rather than a dedicated pick list form which I use for either pick lists with extensive choices, or where choices have a significantly long value/string.)

Recently I went through and updated the schema of all pick lists. I was hooking them all to an administrative interface to manage the pick lists.

I just came across one which I had forgotten to update the Combo Box control... and thought it odd it would still be working since the DB schema changed. The Combo Box insists on not reflecting the DB schema change, nor the query change which fills the Combo Box pick list.

According to the old schema, I was selecting only the TITLE, ID cols of data, and had it displaying Col 1, bound to Col 2.

Now my standard is to select all columns from the pick list table, bind the control to Col 5, and Display Col 8. (This Pick List has one additional feature, so it actually has 9 cols.) Then the display settings are as follows:

a. Format Tab:
i. Update the Column Count: 8
ii. Specify custom widths: 0";0";0";0";0";0";0";3"
b. Data Tab:
i. Row Source: Verify the name of the QueryDef the control is bound to
ii. Bound Column: Bind the control to the ID column: 5

I can run the DAO.QueryDef that is the Data Source of the Combo Box and get the expected 9 cols of data.

I have even now pasted the SQL into the Combo Box Data Source instead of having it point to the DAO.QueryDef. Here is that query:

SELECT t.aid, t.authid, t.authusername, t.logtimestamp, t.id, t.sort, t.active, t.title, t.aqeflg
FROM tmptblqry_metoolingtooltype AS t
WHERE (((t.active)<>0))
ORDER BY t.sort, t.title;

I have run my "decompile / compact / compile" cleanup process.

Anyone have a suggestion as to what I might be missing? I have no clue why the Combo Box can still remember the original field layout.

 

[boblarson]

Could be the database needs a decompile. And sometimes I've found you just need to delete the entire thing and put a new one in.

 

[mdlueck]

Could be the database needs a decompile.

I had already performed this process, noted.

And sometimes I've found you just need to delete the entire thing and put a new one in.

And I just completed this work. Same thing! (Same as attached screen capture, when column width are blanked out so that all may show up and fight for screen space.) Somehow the Combo Box is receiving the old query schema STILL.

 

[boblarson]

Somehow the Combo Box is receiving the old query schema STILL.

Sure you don't have some code somewhere assigning that? I have Rick Fisher's Find and Replace which is great for finding things like that. It is about $40 but it is well worth it. I use it at work almost daily.

But, the surefire way to find out is if you delete the combo and comment out any code you know of that refers to it and then load the form and see if it errors anywhere because it can't find it. Or, make sure that Name AutoCorrect is turned off and then rename the combo and see what happens.

 

[mdlueck]

Sure you don't have some code somewhere assigning that?

When I rebuilt the control, I reverted to defining the Row Source as the DAO.QueryDef object again. I can run the DAO.QueryDef and receive the correct number of columns (9). Yet the combo box thinks it has two columns.

I know of no remaining code in the configuration... just the Combo Box refers to the DAO.QueryDef as its Row Source.

The funny thing is... I copy/pasted that control and plunked it on a new form... wired the correct new DAO.QueryDef into that pasted control, and that control behaves correctly... which is how I discovered that the column numbers were off per my new standard.

Perhaps I can change the name of the DAO.QueryDef and shake loose the lingering settings. Still, odd that copy/pasting in the raw SQL Query into the Row Source property would not be enough of a jiggle.

 

[mdlueck]

This morning I tried a slight of hands... First I commented out the code which creates the DAO.QueryDef the Combo Box uses to populate itself. Result: The control was empty, and clicking Row Source [...] brought up the Table/Query selection dialog with nothing per-selected.

I changed the name of the DAO.QueryDef the Combo Box was using, and updated the Combo Box's Row Source to be the new name DAO.QueryDef. The Combo Box still insists on the old column definition / layout... which the current query is not returning.

How odd that selecting a totally different DAO.QueryDef did shift it / cause it to realuize the new column layout... Say, perhaps I should point this control at a different DAO.QueryDef which is actually on this Form (DAO.QueryDef of another Combo Box) and see if that will jiggle the control to its senses... Off to try that!

 

[mdlueck]

Say, perhaps I should point this control at a different DAO.QueryDef which is actually on this Form (DAO.QueryDef of another Combo Box) and see if that will jiggle the control to its senses... Off to try that!

That did it... At run-time the control was flipped back to its normal Pick List choices... HUA!?!?!?!

I had forgotten that since this Pick List was a different schema, that meant additional capabilities. I had hard coded into the form an update of the control's RowSource.... statically (SQL) in the Form verses the Class which defines all of the Form (DAO.QueryDef) Queries. In other words, to get the Form to actually open I still need the class based code which will create the DAO.QueryDef in the first place, but once open the form ends up itself swapping out the DAO.QueryDef for custom SQL. :banghead:

At least the source of the weird behavior has been finally located!!! Now to stew on a possible update to prevent this sort of thing from happening again...

 

[mdlueck]

To fix this "properly" I...

1) Utilized my class object which contains the Form DAO.QueryDef information to locate / update the correct DAO.QueryDef object.
2) Only for the special case do I utilize a custom SQL query hard coded into the form and still do the above step.
3) Thus when the DAO.QueryDef needs the standard query, I no longer have the standard SQL query string duplicated in the form.
4) So, no more custom SQL strings in the Combo Box Row Source property!