Dynamic assignment of Combo box rowsource generates error

[paradiso]

Hello friends,

In November another programmer posted this concern on the Access Forms forum, but no solution has been posted yet. I'm having the same problem, so I'll try this forum.

On a switchboard-type form I assign a rowsource to a single, unbound combo box depending upon the button clicked by the user. The combo box allows the user to choose from a list of retailers (text fields) in one case, a list of dates in another, and so forth. The rowsource is a different SQL query for each case.

When the form is first opened the code works no matter which button is clicked, allowing the user to open the appropriate form. Return to the switchboard and hit another button (assigning a new rowsource query), and up comes the error "the value you entered is not valid".

This did not occur in Access 97, but does pop up in when the db is run under Access 2000 and 2002 (I've converted the code). I do not wish to close the form, because it is the home screen for the application.

Depending upon the query, the data type of the bound column is either text, long int, or date/time. I've tried many things -- casting the non-text values to text fields, requerying, setting a dummy sql string as a rowsource in the properties box, compact, repair, decompile, recompile, praying to St. Isadore.

Can anyone shed light here?

most gratfully,
paradiso

 

[ObjectRequired]

Have you tried specifically requerying the box, as in Me.MyComboBox.Requery or Me.MyComboBox.Refresh? This should be run each time a button is clicked, either by putting it in each button's code, or in a ubiquitous Form or Detail event.

 

[paradiso]

Hello and thank you for your reply.

The buttons are selections in an option frame. In the option frame OnClick event I call a subroutine ClearEntries that sets the combobox value to "" and restores the form visibles and defaults to the way they look when the form is opened.

I make a requery call in ClearEntries, but to no avail.

I haven't tried refresh, so I'll give it a whirl.

 

[cmmrfrds]

Where did you do the casting? In the query?

Is there the same number of columns in all the queries?

 

[paradiso]

Hi,

There are four buttons in the option group. In the frame option OnClick routine I set up a Select Case, and for each case I set the rowsource first, then ColumnCount, then ColumnWidth.

In the date-related SQL string I do the casting in the query, specifically

SELECT DISTINCT CStr(TblAdData.AdDate) FROM TblAdData ORDER BY Cstr(TblAdData.AdDate);

After the End Select I requery the combo box and set the focus there.

When I debug, I see that the error occurs somewhere between the KeyDown and the BeforeUpdate event of the combo box.

And as I mentioned before, if you hit the date button first it works fine. It only acts up if you hit date after you've already hit one of the other buttons, viewed the related form, and returned focus to the switchboard. The behavior does not seem to be related to data type, but instead to sequencing.

Regarding an earlier post, I looked for Refresh, but it's not an option in the combo box command list.

 

[cmmrfrds]

Does the Date query have more columns than the prior query? Does it have the most number of columns? Is the total length of a row longer in the Date query?

 

[paradiso]

In the test sequence that always brings up the error, first I hit the Retailer button, select from the combo box, which opens the form for that retailer, close that to reveal the switchboard, hit the date button, select a date, get the error message.

Here is a snippet of those two cases from the onclick event:

Case 1: 'by Retailer
CmboSelection.RowSource = "SELECT TblRetailers.* FROM TblRetailers ORDER BY TblRetailers.Retailer;"
CmboSelection.ColumnCount = 2
CmboSelection.ColumnWidths = "0 in.; 2 in."

Case 2: 'by Date
CmboSelection.RowSource = "SELECT DISTINCT CStr(TblAdData.AdDate) FROM TblAdData ORDER BY CStr(TblAdData.AdDate) DESC;"
CmboSelection.ColumnCount = 1
CmboSelection.ColumnWidths = "2 in."

In the first case, the retailer table has only two fields, the ID field and the retailer name. The ID field is the bound column, hidden so that the user just selects the retailer name.

 

[cmmrfrds]

I would switch the order of setting the column count, it seems that it should be done first. It might be trying to load 2 columns at this point instead of just the 1 that you want.

Case 2: 'by Date
CmboSelection.ColumnCount = 1
CmboSelection.ColumnWidths = "2 in."
CmboSelection.RowSource = "SELECT DISTINCT CStr(TblAdData.AdDate) FROM TblAdData ORDER BY CStr(TblAdData.AdDate) DESC;"

 

[paradiso]

Cue the choir!

Your suggested fix worked! I am so very grateful for the time you spent discussing this with me.

The combo box accepted the values of a different data type when the column count and widths were identified before the rowsource query.

I'm hearing Handel's Hallelujah Chorus.

thanks,
paradiso

 

[cmmrfrds]

Great! I am glad it worked.

Merry Christmas.