Fill Combo Box with dates without query?

  • Thread starter Thread starter Jon
  • Start date Start date
J

Jon

I have a combo box that takes a good deal of time to fill... I have an
idea on how to speed it up but I'm not sure how to go about creating
the query/VBA to do the job.

The query that populates the combo box is:

SELECT qryMonthID.FullID, qryMonthID.YearID, qryMonthID.MonthNameID,
qryMonthID.SortID, qryMonthID.MonthID FROM qryMonthID;

With only FullID as being visible, and the qryMonthID behind that is
this:

SELECT DISTINCT Year([purchase_dte]) AS YearID,
FORMAT([purchase_dte],"mmmm") AS MonthNameID,
FORMAT([purchase_dte],"mmm/yy") AS FullID,
FORMAT([purchase_dte],"yyyymm") AS SortID, Month([purchase_dte]) AS
MonthID
FROM tblPurchaseOrders
ORDER BY FORMAT([purchase_dte],"yyyymm");

The problem I am having is that I have users who are complaining about
how long the combo box takes to activate, which is due to the number
of entries in tblPurchaseOrders having grown very rapidly lately.

Now I already have both the minimum and maximum values for
purchase_dte in memory as global variables since theyr'e needed for
some other searches. Can someone help me out by showing me how I can
fill the combo box with the needed dates using only those two values?
 
One approach to doing this would be to create a couple functions that return
those global variables. Then you could use those functions in a query, in
the Selection Criterion "cell", with something like:
Between Function1() And Function2().

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
That suggestion did the trick. Thank you!

One approach to doing this would be to create a couple functions that return
those global variables.  Then you could use those functions in a query, in
the Selection Criterion "cell", with something like:
     Between Function1() And Function2().

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP




I have a combo box that takes a good deal of time to fill... I have an
idea on how to speed it up but I'm not sure how to go about creating
the query/VBA to do the job.
Click to expand...
The query that populates the combo box is:
Click to expand...
SELECT qryMonthID.FullID, qryMonthID.YearID, qryMonthID.MonthNameID,
qryMonthID.SortID, qryMonthID.MonthID FROM qryMonthID;
Click to expand...
With only FullID as being visible, and the qryMonthID behind that is
this:
Click to expand...
SELECT DISTINCT Year([purchase_dte]) AS YearID,
FORMAT([purchase_dte],"mmmm") AS MonthNameID,
FORMAT([purchase_dte],"mmm/yy") AS FullID,
FORMAT([purchase_dte],"yyyymm") AS SortID, Month([purchase_dte]) AS
MonthID
FROM tblPurchaseOrders
ORDER BY FORMAT([purchase_dte],"yyyymm");
Click to expand...
The problem I am having is that I have users who are complaining about
how long the combo box takes to activate, which is due to the number
of entries in tblPurchaseOrders having grown very rapidly lately.
Click to expand...
Now I already have both the minimum and maximum values for
purchase_dte in memory as global variables since theyr'e needed for
some other searches. Can someone help me out by showing me how I can
fill the combo box with the needed dates using only those two values?- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
Back
Top