Altenative for 365 Dlookups

[FL0XN0X]

Dear,

I have built a calendar on a form, where each day is presented by a small command button. For each command button, I execute a Dlookup function to retrieve data from a Query (i.e. Query with first column holding the date and second column holding the data to retrieve) on the load-event of the form. It takes around 8 minutes to finish all Dlookups (because it needs to execute 365 Dlookups). Is there a way to speed this up?

Thank you
Kind regards, Bart

[Micron]

Maybe a vba recordset? Very hard to be precise because the supplied info is very sketchy. The calendar probably should have 42 textboxes and one button, not 42 buttons.

[FL0XN0X]

The Calendar does have 365 command buttons (not textboxes), 1 for each day, because, additionally, I want the user to be able to pick out a date by clicking the corresponding button which will execute a query returning data for that particular date. On the load event of the form, the color of each command button (i.e. each day) is set based on the result of a query executed by the Dlookup function I have mentioned, so since the form represents 1 year, ie. 365 days, 365 Dlookups needs to be executed, which take ca 8 minutes to finish. Is there a better/faster way ?
I hope this description gives a better idea of what I'm truying to accomplish

Thank you

[Micron]

If only one choice is permitted, why use a custom calendar? Are you aware that there is a built in date picker for textboxes if the control is set to Date format? If the users don't want to navigate to a month that is not current, then I think you are bearing the brunt of someone else's laziness.

Otherwise, I'm still a bit confused because:
AFAIK, you can only lookup one field in one table/query using DLookup, so I fail to see how you can use it to get a date plus other values.
AFAIK, you are running the query for each lookup call (I don't believe the query records are stored in memory, allowing you to mine the records multiple times)

365 buttons means 365 separate procedures. While they all could possibly run a single function to return some value based on which was clicked, it is way more work than should be necessary. Still thinking a recordset may be the answer, but I don't think you're expounding enough to say definitively. You have to take the approach that we know nothing of your db or process as it applies to the problem, because we don't. There are too many gaps. Also, watch out for using alternative words and phrases to mean the same thing. It only confuses the issue.

[isladogs]

First of all I admire your diligence in creating a form with 365 buttons .... what about leap years though?
However I think its impractical for lots of reasons:
1. Too many buttons so confusing for users
2. Ridiculously slow in use for the reasons you describe

It seems the only reason for running all your lookups at the start is to colour the buttons
If you scrap that it should open instantly
Then when a user clicks a button run the code for that date only

If you must have the colours then you need to store data in a table in a format that allows that info to be retrieved instantly
For example, this calendar form has data for a school year - possibly more than 365 records as there can be several on the same day.
Its colour coded & opens instantly


However, I still think fewer buttons would be far better.
Suggest you use a monthly calendar
I have a nice calendar/date picker you can download from here
https://www.access-programmers.co.uk...d.php?t=298062

[FL0XN0X]

Hi,

Thank you for your feedback. I think I will leave my current approach and go for the one you have posted. Question about this approach, how do you color the cell in the Datasheet view?

[isladogs]

Hi,

Thank you for your feedback. I think I will leave my current approach and go for the one you have posted. Question about this approach, how do you color the cell in the Datasheet view?

Do you mean the continuous form in the larger screenshot? It's not a datasheet partly because that can't include buttons