Fill Combo Box with dates without query?

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

"Jon" wrote in message
...
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?

That suggestion did the trick. Thank you!

On Jan 2, 5:54*pm, "Jeff Boyce" wrote:
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

"Jon" wrote in message

...



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?- Hide quoted text -

- Show quoted text -