Loading List Box data into memory

[strive4peace]

It is odd, for someone optimizing memory, to be using macros ... you
have so much more power and control with VBA!

"I do this for each control"

by this, do you mean each list/combo box control? How many rows does
each return? Are you using queries for the Rowsource? Are they nested
queries? What is the SQL of each?

When you say "goes to the last record", are you talking about the
RecordSource for the form? this has no effect on the Rowsource for a
combo or listbox


Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[dhstein]

I have 2 controls a list box and a combo box that have a lot of data
associated with them. The data doesn't all load at startup, so there is a
delay for the user to scroll down to the bottom. I created an "Autoexec"
macro which goes to the form, then goes to the control and goes to the last
record. I do this for each control but I still have the same issue. Is
there a way to get the data to stay resident in memory so that the user
doesn't see this delay? Thanks for any help you can provide.

 

[strive4peace]

Hi David,

there is nothing you can do in a macro that you can't do in VBA code ...
except things like keyboard shortcuts <smile>

any action you can pick in a macro can be put in VBA code by prefacing
it with

DoCmd.

Anyway, all you are doing is going to the last record in the
RecordSource -- this has nothing to do with the RowSource for a listbox
or combobox ... are you using saved queries for the source? Do you have
criteria? Perhaps, if there are a lot of rows, it would be good to
filter them -- might you have any logical criteria?

look at this article:
Combos with Tens of Thousands of Records, by Allen Browne
http://www.allenbrowne.com/ser-32.html


Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[dhstein]

Hi Crystal . Thanks for your reply. I do use VBA for everything. The only
reason I have this 1 macro is to try to load the listbox as soon as the form
loads in order to read the data into memory. The macro facility allows you
to "goto" the control and then "goto" the last record. I assumed that this
would load the data into memory so that when the user selects the list box,
there is no delay in scrolling down through the records. But it isn't doing
that, so I'm looking for a better solution. Have a great day.

David

 

[dhstein]

Hi Crystal. This macro is called "Autoexec". The Autoexec macro runs
automatically when the application loads. The idea is to have the macro go
to the list box and go to the last record to read all the data into memory.
That way when the user goes to the list box and scrolls down there is no
delay while the system reads in the underlying query data. The system works
fine - it's just that delay that I'm trying to solve. I'll look at the Allen
Browne article and see what it says. Thanks.

 

[Dirk Goldgar]

I have 2 controls a list box and a combo box that have a lot of data
associated with them. The data doesn't all load at startup, so there is a
delay for the user to scroll down to the bottom. I created an "Autoexec"
macro which goes to the form, then goes to the control and goes to the
last
record. I do this for each control but I still have the same issue. Is
there a way to get the data to stay resident in memory so that the user
doesn't see this delay? Thanks for any help you can provide.

I think you're probably going about this the wrong way. The best way I know
to "pre-load" all the rows in a list or combo box is to access its ListCount
property. You don't have to *do* anything with that count; just asking for
it forces the control to load all the records.

You would have to do this in VBA, but it wouldn't take much. You could use
the Load event of your form to do it:

'----- start of code -----
Private Sub Form_Load()

Dim lngCount As Long

lngCount = Me!YourListboxName.ListCount
lngCount = Me!YourComboboxName.ListCount

End Sub
'----- end of code -----

Of course, you'd have to replace the made-up control names in the above code
with the actual names.

 

[Jeff Boyce]

I'm still not getting an idea of how many rows are being loaded. What is "a
lot" to you may not be to others.

Allen Browne offers a very effective mechanism for speeding up the use of a
combobox that could (potentially) hold thousands of rows.

What is the SQL statement you're using as a source for each of those? Are
the underlying fields/tables properly indexed?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[dhstein]

Thanks Dick. It worked with the list box, but not for the combo box for some
reason. Unless you have any additional advice, I'm happy with that result.

 

[Dirk Goldgar]

Thanks Dick. It worked with the list box, but not for the combo box for
some
reason. Unless you have any additional advice, I'm happy with that
result.

I should work with the combo box just as well as with the list box. Are you
sure you spelled the name correctly? Is the combo box's rowsource dependent
on something else, such as the item selected in the list box? In that case,
you'd have to interrogate its ListCount property after you requery it or
reset its rowsource.

 

[strive4peace]

you're welcome, David -- looks like Dirk has you on the right track


Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal
(open by clicking on the video thumbnail so you get the "high quality"
option)

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[strive4peace]

if you use Me. instead of Me! then when you compile, Access will check names

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code, references, or switch versions, you should
always compile before executing.

from the menu in a VBE (module) window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

~~
if you run code without compiling it, you risk corrupting your database


Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal
(open by clicking on the video thumbnail so you get the "high quality"
option)

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*