Combobox AfterUpdate Bizarre

[Gerhard]

This is bizarre... Im having problems with the combobox AfterUpdate event:

Im running Access 2003.

I created an unbound combobox with 3 columns on a form.
The Row Source is from a table.
Bound Column = 1 and LimitToList = No,,, thus the user can edit the data in the 1st-Column after they selected one of the rows in the combobox.

However, I want to save the data of 2nd-Column and 3rd-Column, because if the user edits the 1st-column, the resulting text might not be in the Row Source, which will result in 2nd-Column and 3rd-Column to go blank.

I thus created two text boxes (TextA and TextB) to store the values of 2nd-Column and 3rd-Column each time the user selects a row from the combobox.

I entered the following code for the Combobox's AfterUpdate event:

Private Sub InvoiceDetailCombo_AfterUpdate()
    TextA = [Forms]![Mainform1]![InvoiceDetailCombo].[Column](1) '2nd-Column
    TextB = [Forms]![Mainform1]![InvoiceDetailCombo].[Column](2) '3rd-Column
    MsgBox "After Update" 'Just to make sure the event fires
End Sub

Now, if I select a row in the Combobox. Everything works fine. The data is copied to TextA and TextB and my little "After Update" message pops up.

Then I edit the text in the Combobox.
When I click the mouse on another control, the new edited data in 1st-Column is not found in the Row Source, which results in blanks/nulls being copied to TextA and TextB and the "After Update" message is displayed. Everything OK so far.

But, if I ever-so-lightly click on another control after the 1st-Column's data was edited (as if the combobox only lost focus), the "After Update" message still appears but this time no data is copied to TextA or TextB (you can even test the values of TextA and TextB with a query - they still have their old values).

It appears that the pressure of your finger on the mouse button influences something. However it does not look as if the entire AfterUpdate event was skipped, because my little message still came up - yet TextA and TextB were left unchanged.

It also works the same if you press the right-mouse-button.

What is more... they way the text in 1st-Column was edited also has an influence.
If text is added, TextA and TextB are left unchanged. But if some of the text is deleted, TextA and TextB are updated every time - regardless of left or right button or finger pressure.

I'm dumb folded
Gerhard

[hjozinovic]

Do you have any code in OnCurrent event of the form that holds the controls?
This code might be your problem....

[Gerhard]

Hallo

Yes, I have quite a lengthy peace of code in the main form's On Current event, but that is not the problem, because I copied the combobox and the two text boxes to a new blank form with nothing else on it. But the problem keeps on repeating itself.

Must I email you a copy of the mdb with everything removed except for this one little form and the small table for the combobox's Row Source?

I got a tip somewhere in a forum to move all my combobox's code from the After Update event to the On Lost Focus event. This seems to have done the trick. Still, maybe lots of people don't know that their comboboxes' AfterUpdate events work sometimes and sometimes not.

Gerhard

[NeoPa]

The trick is to put the code you want to run after a successful update into the AfterUpdate event procedure, and the code you want to run whenever the control loses focus, in the LostFocus event procedure.

[Gerhard]

Yes, but the problem is that it appears that not all the code in the AfterUpdate event executed after a successful update.

[NeoPa]

Did you do any debugging (using tracing of code - See Debugging in VBA) when this happened? If so, what did you discover?

[Gerhard]

I'm able to see the value of my combobox's 1stColumn in the "Locals" pane under "Oldvalue" and "value", but I can't find the values of the 2nd and 3rdColumns. Where must I look?

[Gerhard]

Found it under recordset > Items. Will come back.

[NeoPa]

Good for you. Did you find that link helpful?

[Gerhard]

Yes thanks. Why haven't anybody give you credit for it yet? Well done!

OK here is where I am:

Sorry to say but I'm now even a bit more confused.
You must see this to believe it.

I have eliminated everything in the mdb and reduced the combobox to only two columns to try to keep everything as simple as possible and I have left only one textbox called TextA. The idea is that when the user selects a row in the combobox, the data in the combobox's 2nd column must be copied to TextA.

It is very straightforward. My only code on the entire form is this:

Private Sub ComboTest_AfterUpdate()
    TextA = Eval("[Forms]![Form1]![ComboTest].[Column](1)")  '2nd Column
End Sub
 

In the debugger's locals pane I found the combobox referred to as Item 1.
Under Item 1, I found Record set > Fields > Item 1 and Item 2, which I take is the 1st and 2nd columns of the combobox.

I set a breakpoint before TextA = Eval.....

When I select a row in the combo box, it works fine and the value of the 2nd Column is copied to TextA. Even if the debugger pops up and I press F8 through the steps, everything looks fine.

But, if the debugger pops up and I first go into the locals pain to see the values in Item 1 >Record set > Fields > Item 1 and Item 2, I find the combobox's list's first row's data in these fields and subsequently this wrong data is also copied to TextA.

Thus, this correlates with my previous problem which is: the moment that the combobox's update is sort of "interrupted" it does strange things.

Maybe I should mail you the mdb. It is very small (0.3 meg).

[NeoPa]

Gerhard, I missed this reply earlier, and was lucky to find simply by looking through some old emails when I found the one for your posting this on Monday. Lucky, as otherwise I might have missed this thread altogether until you'd posted again sometime.

Let me see if I can add a few helpful comments (hints) as I go through your post.

Private Sub ComboTest_AfterUpdate()
    TextA = Eval("[Forms]![Form1]![ComboTest].[Column](1)")  '2nd Column
End Sub

Eval is not required, and [] around Column is incorrect. Try instead :

Private Sub ComboTest_AfterUpdate()
    TextA = Me.ComboTest.Column(1)  '2nd Column
End Sub

Item1 & Item2 in the debugger (under Fields) would refer to separate, individual fields within your datasource, the recordset.

In the debugger's locals pane I found the combobox referred to as Item 1.
Under Item 1, I found Record set > Fields > Item 1 and Item 2, which I take is the 1st and 2nd columns of the combobox.

I set a breakpoint before TextA = Eval.....

When I select a row in the combo box, it works fine and the value of the 2nd Column is copied to TextA. Even if the debugger pops up and I press F8 through the steps, everything looks fine.

But, if the debugger pops up and I first go into the locals pain to see the values in Item 1 >Record set > Fields > Item 1 and Item 2, I find the combobox's list's first row's data in these fields and subsequently this wrong data is also copied to TextA.

It's hard to understand exactly what is happening here, but I would guess that doing this without debugging doesn't have the problem. If so, then there is nothing to worry about I guess. The (Column()) code does depend on which row of the ComboBox is currently selected. If you do something which changes that before the code is run then the code will reflect this change, as you describe.

Maybe I should mail you the mdb. It is very small (0.3 meg).

I don't see a need for that at this time. If we get to that point (I'd rather avoid it personally), then you can zip up the file and attach it to a new post in this thread.

[Gerhard]

I Guess the best way to go forward is to avoid comboboxes where LimitToList = No. Thus way the combobox always find a valid row in it's list, which ensures that the AfterUpdate event runs smoothly.

[NeoPa]

That wouldn't be (wasn't) my advice. I would endeavour to find the fundamental problem then fix, or work around, that.