Requery a listbox 1

[Crookshanks]

Hello there,

Iam populating a listbox on a form with an SQL statement like this (DbName is a variable name for the BE-database).

SQL(2) = "SELECT tbl_Batch.IdBatch, tbl_Batch.Type, tbl_Batch.[NAV/Unit], tbl_Batch.Units, tbl_Batch.UserEntry, tbl_Participants.Name, tbl_Batch.EntryDate " & _
"FROM tbl_Participants INNER JOIN tbl_Batch ON tbl_Participants.IdPar = tbl_Batch.IdPar " & _
"IN '" & DbData & "';"

The listbox is populated with:

listbox.rowsourcetype = "Table/Query"
listbox.rowsrouce = SQL

and this work perfectly fine until I change a record which is part of the query. After that I perform a requery of the listbox but the changes (written with rs.update) are not reflected in the listbox afterwards. I also tried the .repaint on the form.

After a next call of the form (and the listbox within) the query is updated. Why is this not the case immediatly? Anybody who met the same problem or recognizes this in some way. Any help appreciated!

Kind regards,

 

[Crowley16]

hmm, never come across that problem...

how are you requerying your listbox? Also how are you updating your recordset?

some code please...

if this persists, then why not just put a form requery in there somewhere?

 

[Crookshanks]

De records are changed in the following way:

Sub SaveChanges()

rs.Edit 'Opens record for editing and writes values
With Form_Transactiondetails 'from Form back to current record in tbl_Batch
rs![MarketValue] = .txtMarketValue.Value
rs![Brokerage] = .txtBrokerage.Value
rs![MutationCost] = .txtMutationCost.Value
rs![CTT] = .txtCapitalTransferTax.Value
rs![Transvalue] = .txtTransactionValue.Value
rs![NAV/Unit] = .txtNAV.Value
rs![Units] = .txtUnits.Value
rs![ContractDate] = .txtContractDate.Value
rs![S'mentDate] = .txtSettlementDate.Value
rs![IdPar] = .comboParticipant.Value
rs![Remarks] = .txtRemarks.Value
rs![EntryDate] = Format(Now, "dd/mm/yy")
rs![IdPar] = .comboParticipant.Value
If rs![UserEntry] <> userName Then 'userentry check
mes ("Transactie zal met andere inbrenger worden weggeschreven naar de batch")
rs![UserEntry] = userName 'Overwrite UserEntry with current user
End If
rs.Update 'actually writes (changed) values to recordset
.cmdTransEdit.Enabled = True: .cmdTransEdit.SetFocus 'sets buttons correct for next call of
.cmdTransSave.Enabled = False 'this form
.Visible = False
End With
updatebatchscreen
End Sub

After the change the sub updatebatchscreen is called. This sub has the following lines:

Sub updatebatchscreen()
dummy = rs.RecordCount 'get number of records from record set
With Form_Batch
.Visible = True: .SetFocus
.listBatch.Value = Null 'resets selection to re-enable selectioncheck
.lblNumTxnBatch.Caption = dummy & " transacties in de batch" 'update label
.listBatch.RowSource = SQL(2): .listBatch.Requery
.Repaint
End With
End Sub

 

[Zion7]

Crookshank, from the actual form, that the listbox is on, try Me.Refresh.

Using Me.Repaint, Me.Requery & Me.Refresh in the past, I've always found, Me.Refresh to be the most spontaneous, called from the form, the data is on. Or, at least, that's the form you want to refresh.(in case the query is changed, on a different form).

Forms!lstBoxForm.Refresh



Hope this Helps, good luck!

 

[Crookshanks]

Thanks for your post. But this does not work for me. I've done a bit of testing with the following code:

Sub updatebatchscreen()
dummy = rs.RecordCount 'get number of records from record set
With Form_Batch
.Visible = True: .SetFocus
.listBatch.Value = Null 'resets selection to re-enable selectioncheck
.lblNumTxnBatch.Caption = dummy & " transacties in de batch" 'update label
.listBatch.RowSource = ""
.listBatch.RowSource = SQL(2): .listBatch.Requery
.Refresh
End With
End Sub

If I run this, the listbox becomes first empty and after that it fills the listbox with a NOT updated result of the SQL-statement. Very strange. Could it have something to do with the focus? When you insert a msgbox line it does update. But of course that is something I would not like to do. Anyone a suggestion?

Kind regards,

 

[Zion7]

Crookshank, I'm sorry, but I'm at a bit of a loss.
You're saying, your listbox updates on the 2nd run of the procedure, not the 1st? So, it's always an update behind?
Is the table you're editing, is that in sync with the procedure, or is it behind a step also.
Theoretically, the list box should update immediately, by the SQL satatement, regardless of the state of the table.
I see, in the code that you showed, there was no declaration of the recordset variable, and a set statement, was this for brevity, or an oversight...
...point being, how's the state of the actual table?

Sorry Crookshanks, I'm stretching here!

 

[TheAceMan1]

How are ya Crookshanks . . . . .

This appears to be one of those [purple]quirks[/purple] I ran into many moons ago. My problem was, after requery, the first item in the list was automatically selected (no explanation!).

Anyway after extensive testing, I found the following line always reliable instead of Requery (been using it ever since . . . . . no problemo):

[blue]Me!ListBoxName.RowSource = Me!ListBoxName.RowSource[/blue]

It may appear circular but its not. Give it a whirl and let me know. . . . . .

See Ya! . . . . . .

 

[RoyVidar]

This is a recordset and a rowsource from a backend database?

If so, it might be a timing issue, the requery of the listbox might be performing before the recordset is fully populated/updated to the backend, so when adding a msgbox, you allowed it the extra time to update, providing a correct requery. If this is the case, I'm not sure how to circumvent it. Adding some time interval, using some kind of flag to use to check the liscount of the lisbox in the cases there should be one more item. I'm not fluent enough in DAO to know whether there might be a method of ensuring/verifying the record is written to the tables or if .Update is supposed to be that method... other ideas?

Roy-Vidar

 

[TheAceMan1]

Roy . . . . .

Yes . . . . your right. Need more coffee here . . . .

See Ya! . . . . . .

 

[Crookshanks]

Thanks all for your contributions, I am convinced that it is a time problem and I just solved it by inserting a msgbox. No the most beautiful method, but al least a very effective one!

Kind regards en greetings from Holland!