Requerying a ListBox

[jeff_carver]

In an Access 2000 database, I have a data entry form with a listbox, a
textbox, and a command button. Here's the way it's supposed to work:

- When the user clicks on a row in the listbox, the text from the
row's last column is copied into the textbox.
- The user edits the text in the textbox and clicks the button.
- The button's OnClick event handler updates the underlying record
via ADODB and then calls the listbox's Requery method to show the
results of the update in the applicable row in the listbox.

In reality, the listbox doesn't show the change unless the button is
clicked two or three more times (exception: if I put a breakpoint on
the Requery line in the code and step through it, the listbox displays
the change immediately).

At first I assumed that there was something going on behind the scenes
that was causing the listbox's Requery method to determine that the
data hasn't changed yet and thus no action was required. However,
putting a delay loop before the Requery line didn't make any
difference.

I also tried changing the RowSource value, requerying, changing the
RowSource back to what it was originally, and then requerying again; no
joy there, either.

I'm at a loss. Anyone have an idea how I can fix this?

 

[Chriske911]

In an Access 2000 database, I have a data entry form with a listbox, a

textbox, and a command button. Here's the way it's supposed to work:

- When the user clicks on a row in the listbox, the text from the
row's last column is copied into the textbox.
- The user edits the text in the textbox and clicks the button.
- The button's OnClick event handler updates the underlying record
via ADODB and then calls the listbox's Requery method to show the
results of the update in the applicable row in the listbox.

In reality, the listbox doesn't show the change unless the button is
clicked two or three more times (exception: if I put a breakpoint on
the Requery line in the code and step through it, the listbox displays
the change immediately).

At first I assumed that there was something going on behind the scenes
that was causing the listbox's Requery method to determine that the
data hasn't changed yet and thus no action was required. However,
putting a delay loop before the Requery line didn't make any
difference.

I also tried changing the RowSource value, requerying, changing the
RowSource back to what it was originally, and then requerying again; no
joy there, either.

I'm at a loss. Anyone have an idea how I can fix this?

post the exact code you are using and feedback will come your way

grtz

 

[jeff_carver]

This code, which follows immediately after the record update
(ADODB.Connection.Execute), is run only if it is determined (via the
Execute method's RecordsAffected parameter) that the update was
successful:

With Me.lstEmpData
Dim intCurrRow As Integer
intCurrRow = .ListIndex + 1
.SetFocus
.Selected(intCurrRow) = False
.Requery
.Selected(intCurrRow) = True
End With

 

[jeff_carver]

I figured it out! The trick is to save the new text value (from the
textbox), then requery the listbox repeatedly until the applicable cell
value matches it; doing that allows for record-update latency

Dim strCellData, strCellDataNew As String
strCellDataNew = <textbox data>
:
:
With Me.lstEmpData
Dim intCurrRow As Integer
intCurrRow = .ListIndex + 1
''''''' .SetFocus '''''''' REMOVE THIS -- CAUSES ERROR 2118!
.Selected(intCurrRow) = False
Do While True
.Requery
strCellData = "" & .Column(2, intCurrRow)
If strCellData = strCellDataNew Then
Exit Do
End If
Loop
.Selected(intCurrRow) = True
End With

Please note that, in my previous message, I had the ".SetFocus" line in
the code by mistake. I put that into my form module last night just
before giving up for the night, but I didn't actually run it. When I
ran it this morning (after posting the code in my previous message), it
raised error 2118 ("You must save the current field before you run the
Requery action"), which was what tipped me off to the real source of
the problem.