Weird combobox behavior

J

jay

Database is mdb with SQLServer2000 back-end, Access 2003 front-end. I create
a local (Access client-side) table. I add a single row to the temp table with
"INSERT INTO tmpTable (Field3, Field4) VALUES (xyz, abc);" Then,

myComboBox.RowSource = tmpTable

The added row does NOT show up in the combo box or in the tmpTable. However,
if I do this:

myComboBox.RowSource = tmpTable
lngListCount = myComboBox.ListCount

it works. Anybody seen this behavior before?
Jay
 
J

jay

Thanks for your thoughts.
I'd already tried "myComboBox.Requery" and it didn't work, that is, it
doesn't include the record added with the "INSERT INTO" query in the
drop-down list. Just tried it again to make sure and it still doesn't work.
"myComboBox.RowSource.Requery" gives me a compiler error.
I've experimented with monitoring the ExecuteComplete event of the
connection object, but that didn't work. "lngListCount =
myComboBox.ListCount" does the trick. This combobox is a single column and
the connection is "CurrentProject.Connection" if that makes any difference.
Jay
 
O

Ofer

It should work, but try the doevents after insert command

MySql = "INSERT INTO tmpTable (Field3, Field4) VALUES (xyz, abc);"
Doevents
Me.myComboBox.RowSource = "Select Field3,Field4 From tmpTable"
Or if the record source already exist then
Me.myComboBox.requery
 
J

jay

I just tried the DoEvents command after the SQL INSERT and after the
assignment of the temp table to the ComboBox RowSource. I put in a
myComboBox.Requery followed by another DoEvents, just for good measure, and
it still didn't work.
At least I've got a work-around (lngListCount = myComboBox.ListCount).
Thanks for your help.
Jay
 
Top