Slow List box refreshing

[Dan2kx]

hello, i have a quick question, is there anyway to speed up a listbox refreshing, its quite large with 18 columns and so far upto 30 rows, but it updates row by row every time the screen changes (ie ALT+TAB)

(new PC isnt an option sadly)

Dan

[ChipR]

Making the list not visible while you are requerying it helps, but I don't know how you would do that in response to an alt+tab.

[ADezii]

hello, i have a quick question, is there anyway to speed up a listbox refreshing, its quite large with 18 columns and so far upto 30 rows, but it updates row by row every time the screen changes (ie ALT+TAB)

(new PC isnt an option sadly)

Dan

What does the underlying Row Source look like?

[Dan2kx]

Row source is set to a query at the end of code

    Me!ListHD.RowSource = "" & LogStaffID & "qryHolidayDates"
'(Me!ListHD.RowSource = "1qryHolidayDates")

the sql for this query is (dates are dynamic):

SELECT [49qryHolidayDates2].Name, [49qryHolidayDates2].StaffID, MinsLeft([StaffID],2009) AS Minutes, _
DateCount([10/08/2009]) AS [D 10/08/2009], DateCount([11/08/2009]) AS [D 11/08/2009], DateCount([12/08/2009]) AS [D 12/08/2009], DateCount([13/08/2009]) AS [D 13/08/2009], _
DateCount([14/08/2009]) AS [D 14/08/2009], DateCount([15/08/2009]) AS [D 15/08/2009], DateCount([16/08/2009]) AS [D 16/08/2009], DateCount([17/08/2009]) AS [D 17/08/2009], DateCount([18/08/2009]) AS [D 18/08/2009], _
DateCount([19/08/2009]) AS [D 19/08/2009], DateCount([20/08/2009]) AS [D 20/08/2009], DateCount([21/08/2009]) AS [D 21/08/2009], DateCount([22/08/2009]) AS [D 22/08/2009], DateCount([23/08/2009]) AS [D 23/08/2009] _
FROM 49qryHolidayDates2;

Custom functions;

Function MinsLeft(SID As Long, Yr As Integer) As Integer
MinsLeft = Nz(DLookup("TotalStart", "tblAllo", "StaffID= " & SID & " AND Year= " & Yr) _
- DLookup("TotalUsed", "tblAllo", "StaffID= " & SID & " AND Year= " & Yr), 0)
End Function

Function DateCount(s As String) As String
Select Case s
Case "0"
    DateCount = "__/__"
Case "1"
    DateCount = "AM/__"
Case "2"
    DateCount = "__/PM"
Case "3"
    DateCount = "AM/PM"
Case "4"
    DateCount = "xx/__" 
Case "7"
    DateCount = "__/xx"
Case "6"
    DateCount = "xx/PM"
Case "8"
    DateCount = "AM/xx"
Case "11"
    DateCount = "xx/xx"
Case Else
    DateCount = "ERROR"
End Select
End Function

it runs fast, but the rows dribble in onto the list box, but it varies with speed, and the listbox "redraws" with the same dribble at certain times,

Additional:
it seems to requery after the ALT/TAB or other general form focus gain, although i have not told it too, should i change the query to a make table??

[Dan2kx]

Following my test with the make table query my problem is solved,

i didnt realise it would keep requerying (the other way), can anyone explain this?

Thanks for the direction guys

Dan

[OldBirdman]

A listbox that contains more rows (ListCount) than can be shown will partially requery whenever Access needs to display another row. Access may in effect do a Select query for the rows to display, and using the scrollbar causes this to happen.
I have a ComboBox that seems to requery whenever the mouse is moved over ANY control on the form. First it requeries the hidden Key column, and if different, then requeries the remaining columns. Repaint is very slow. This is done even when the listbox portion is displayed, and I can watch the values fill. The continual requery doesn't bother me as much as the slowness.

OldBirdman

[FishVal]

....
I have a ComboBox that seems to requery whenever the mouse is moved over ANY control on the form....

That is actually a normal behaviour of all Access controls.
If it is an issue, then control could be populated with values list obtained from correspondent recordset to prevent Access query this slow recordset each time it likes.

[OldBirdman]

FishVal's post says it is normal for ComboBox to requery by just having the mouse pass over another control, say a TextBox. A requery may also occur by leaving and returning to Access. Is there any way to not allow these requeries?
I could use value list, but although it hasn't happened yet, a value list could exceed 2048 characters. I could design around this, IF NECESSARY, but then it will put limits on program.
I could use a query, but this is beyond my current abilities. Dan2kx was using a query, and it did not solve his problem. Won't a make-table query bloat the database?

[Dan2kx]

Won't a make-table query bloat the database?

The table is a local temporary table that i ensure is deleted.

However,

Is there any way to not allow these requeries?

I would prefer to stop requries if poss. i believe it would be faster?

[FishVal]

@OldBirdman.

What will happen if value list exceeds 2048 characters?

[OldBirdman]

If RowSource is too long - Access Run-time error '2176' "The setting for this property is too long." But logically, it would appear that records were non-existant, and user might try to enter duplicates.
I guess I want to learn when Access updates a ComboBox or ListBox list and when it doesn't. A ValueList is static, and the only update is if the ValueList is replaced with another. ComboBox/ListBox where RowSource is a query doesn't seem to update if the data in the table changes, but it will if a row in the table is deleted. Not sure this is consistant/logical, one or the other.
When my ComboBox is partially requeried by Access as a result of a non-event in my program, such as moving to WebBrowser window and returning, the requery is partial, not complete. Some fields (column, row) will be changed, and not others. Listrows may now be wrong, and code using the value will malfunction. "Ghost" rows can occur, where there are now not enough rows to reach listrows, and the fields are not updated by the requery.
In writing this, I have probably answered my own querstion. Looks like I will have to use ValueList, doesn't it?