Evil Bound Control Problem

Dear friends,
I encountered a strange thing which I think is a special one for all database Gurus. Especially for you, David, since it is a bound control and maybe evil. (Don't tell me to read your article, because I did. ;))

I have made and attached a small demo project which isolates the problem to the very tip, leaving very, very few lines of code to show what happens. It is a very simple addresslist based on an Access table. It only has 3 fields, Name, Country and EMail. It only contains 2 records either, because that's enough to show.

Please run the little demo in the IDE. It's only few lines of code.
It gives you a typical standard input form and buttons to navigate between records, plus Edit and Cancel (the other buttons have no code). The text fields are bound to an adodb.recordset.

If you click "Edit" the input fields are unlocked and you can change the contents.
Clicking "Cancel" will make the changes undone, using the rs.CancelUpdate method.
Now you see, when clicking "Edit" a combo box with all countries appears.
You can select a country, and it is transfered to the country textbox, like entered manually. No data binding with the combo.
But when you click "Cancel" now, the old content will not reappear. The new text is already stored in the record and CancelUpdate will not bring the old value back. It will, however, if the country name was typed in with the keyboard.

What happens here???????

Interesting. Definitely should not happen but it appears that it is related to setting the text property of the bound control. I rarely use bound controls due to the fact that they will auto update the db sometimes.

I did play around with it a bit and if I replace the code which sets the textbox.text property with something like this ....

Code:

---

txtCountry.SetFocus
txtCountry.SelStart = 1
txtCountry.SelLength = Len(txtCountry.Text)
SendKeys cboCountry.Text

---

It will behave as expected. I hate using sendkeys but it does work.

Yes, great. That's what we'd call a "workaround". Thanks for the effort of working it out.
But like you, I hate to use sendkeys.
Now, after playing around on and on, I think I have found a solution.
But I'm quite curious, what David would have to say about this evil control. :)

Might need to use two different connections, seeing that they're bound. I also like to open the connection in one place (a sub). Once for each connection in your case.

Well, they ARE bound, and I fear they have a strange and unwanted behaviour.
I don't see how a second connection could help there. Did you run the example and see the evil misbehaviour?

Well, after some fiddling I found a solution which, strangely enough, works:
I assigned the text from the combo directly to the .Text property of the bound control like
txtCountry.Text = cboCountry.Text
and this didn't work when cancelling.
Now I assign the new text directly to the field of the recordset like:
rsClient!Country = cboCountry.Text
and everything is fine. The bound TextBox shows the new value, but when .CancelUpdate is executed, the old value reappears.

I'd say this might be a bug in the control binding code of the TextBox control.

I remember when VB6 was first released whenever we would bind a datagrid the first cell would always appear blank.

I Tried under cancel event :
txtcountry=empty

This woked and made the textbox clear.

This is even worse, Rahul.
It will not only empty the textbox, it will also erase the previous value from the record. And that's what was to be avoided. When pressing Cancel you want to have the old value back, not the field erased.

It happens when anything is assigned to the .Text property of the bound field. The value, even Empty, will immediately be stored to the recordset.