Query Caching / Speed up same query run multiple times?

[swisstoni]

Hey,

I have 6 drop down combo boxes, all with their row source set to the same query. The query takes approximately 1 second to run, and so the form now takes a while to load.

Can I run the query once for the first drop down, and then somehow cache it so that the other 5 dropdowns populate with the same info quicker?

Thanks.

 

[mearle]

I would remove the Row source propery from the combo boxes, and run the query in Form_Open using a dao or ado recordset then set the Recordset property of each of the combo boxes to this single recordset.

 

[swisstoni]

Thanks for the advice, I've now got the following code:

    Dim MyDB As Database, MyRec As Recordset
    Set MyDB = CurrentDb
    Set MyRec = MyDB.OpenRecordset("SELECT * FROM [qryCatLevel1] UNION select * from [qryCatLevel2] ORDER BY parent0, parent1")
    
    
    combo_cat1.Recordset = MyRec

But that gives me an "Operation is not supported by this type of object" error when I try to set combo_cat1.recordset.....

That SQL was in a "normal" Access query - is there anyway I can leave it back in there and reference it from the VBA code?

 

[mearle]

Try:
Set combo_cat1.Recordset = MyRec

 

[DCrake]

Simple Software Solutions

Why not create a Union query as a free standing query and simply set the recordsource to the query, as you do not seem to have any query conditions.

 

[gemma-the-husky]

out of interest, why would you need six combo boxes using the same query - can you elaborate -

one issue with combo boxes is the amount of data in them - are these queries big (ie many rows)?


--------
you know, designing a combo box is like making love to a beautiful woman .....

(US readers can safely ignore this very humorous reference)

 

[swisstoni]

Well - it's for an item in an online store, which can appear in upto 6 categories - hence 6 drop downs all displaying the same info.

I do have that union query in a separate query called "qryCatFinal".

I tried the code:

Set combo_cat1.Recordset = [qryCatFinal]

But that gives me an error - my newbie-ness to Access isn't helping me here!

 

[gemma-the-husky]

swiss

Set combo_cat1.Recordset = [qryCatFinal]

assuming combo_cat1 is a cbobox, and qrycatfinal is a stored query, then the syntax is

combo_cat1.rowsource = "qrycatfinal"

but rather than doing this dynamically, you can just set this in the cbobox properties. If the data changes, then simply requery the combobox with

combo_cat1.requery

Hope that makes sense

 

[swisstoni]

Many thanks,

Got this solved in the end.