Problem with Combo Box RowSourceType

[Eric Ellsworth]

Hi,
On a form in an A2K ADP, I have a combo box that is set on Form Load
to use "Value List" as its row source type, and the values are filled in
in the same procedure. Later, the combo box is changed to execute a
stored procedure on an MSDE2000 server. To do this, I do:

cboThis.RowSourceType = "Table/Query" cboThis.RowSource = "EXEC
MyStoredProc '" & tbParam &"'" cboThis.Requery

The problem is that after the Requery combo box invariably has 1 empty
item, even though the stored proc returns many records when executed
directly. Additionally, when I run the code in debug mode, I get a
warning saying there may be a problem with this control's RowSourceType
propery.
If the RowSourceType is never changed to "Value List", the combo box works
fine.

Is this a bug? Is there something else I need to do for the combo to
change from a value list to a query?

Barring that, is it possible to create a query string in an ADP
that does not result in a round trip to the server?

Many thanks for any ideas,

Eric

 

[Wayne Morgan]

I'm haven't used MSDE2000, so I'm shooting in the dark here. What happens if you drop the
cboThis.Requery? The combo box should automatically requery when it's Row Source is
changed.

As a work around, could you use 2 combo boxes, one set on top of the other. Make the
appropriate one visible as needed. You will have to keep track of which combo box is in
use in a given situation so you'll know which one to use. You may have to change them
between bound and unbound by clearing and setting the Control Source so that only the
visible one will update the table.

Hopefully, someone will have an idea to make it work as you have it. I tried what you're
doing using Access tables and it works properly.

 

[Eric Ellsworth]

Hi Wayne,
Many thanks for your good suggestions. Unfortunately, the ComboBox does
not requery automatically when you change the RowSource.

I'm hoping to avoid showing and hiding multiple controls, because I ran into
problems of unexpected situations where I was trying to use an invisble or
disabled control.

The only other way I can see to use just one control is to set the controls
RowSourceType to "Table/Query", then set the RowSource to some query like

"SELECT DISTINCT 'The Name' as Fullname, 'The Phone Number' as
PhoneNumber,'The Fax' as Fax FROM MyTable"

I'm worried that this requires a new round trip to the server for each of
these combos (there are 4), as ADPs have no query parsing engine. Is there
a way to avoid this?

Anyone have any bright ideas? Perhaps a RowSourceType function...

Thanks,

Eric