By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,790 Members | 1,409 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,790 IT Pros & Developers. It's quick & easy.

Slow List box refreshing

P: 365
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)

Feb 10 '09 #1
Share this Question
Share on Google+
10 Replies

Expert 100+
P: 1,287
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.
Feb 10 '09 #2

Expert 5K+
P: 8,623
What does the underlying Row Source look like?
Feb 10 '09 #3

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

Expand|Select|Wrap|Line Numbers
  1.     Me!ListHD.RowSource = "" & LogStaffID & "qryHolidayDates"
  2. '(Me!ListHD.RowSource = "1qryHolidayDates")
the sql for this query is (dates are dynamic):
Expand|Select|Wrap|Line Numbers
  1. SELECT [49qryHolidayDates2].Name, [49qryHolidayDates2].StaffID, MinsLeft([StaffID],2009) AS Minutes, _
  2. 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], _
  3. 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], _
  4. 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] _
  5. FROM 49qryHolidayDates2;
Custom functions;

Expand|Select|Wrap|Line Numbers
  1. Function MinsLeft(SID As Long, Yr As Integer) As Integer
  2. MinsLeft = Nz(DLookup("TotalStart", "tblAllo", "StaffID= " & SID & " AND Year= " & Yr) _
  3. - DLookup("TotalUsed", "tblAllo", "StaffID= " & SID & " AND Year= " & Yr), 0)
  4. End Function
Expand|Select|Wrap|Line Numbers
  1. Function DateCount(s As String) As String
  2. Select Case s
  3. Case "0"
  4.     DateCount = "__/__"
  5. Case "1"
  6.     DateCount = "AM/__"
  7. Case "2"
  8.     DateCount = "__/PM"
  9. Case "3"
  10.     DateCount = "AM/PM"
  11. Case "4"
  12.     DateCount = "xx/__" 
  13. Case "7"
  14.     DateCount = "__/xx"
  15. Case "6"
  16.     DateCount = "xx/PM"
  17. Case "8"
  18.     DateCount = "AM/xx"
  19. Case "11"
  20.     DateCount = "xx/xx"
  21. Case Else
  22.     DateCount = "ERROR"
  23. End Select
  24. 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,

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??
Feb 10 '09 #4

P: 365
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

Feb 11 '09 #5

P: 675
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.

Feb 12 '09 #6

Expert 2.5K+
P: 2,653
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.
Feb 12 '09 #7

P: 675
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?
Feb 13 '09 #8

P: 365
The table is a local temporary table that i ensure is deleted.

I would prefer to stop requries if poss. i believe it would be faster?
Feb 13 '09 #9

Expert 2.5K+
P: 2,653

What will happen if value list exceeds 2048 characters?
Feb 13 '09 #10

P: 675
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?
Feb 13 '09 #11

Post your reply

Sign in to post your reply or Sign up for a free account.