VBA to search appointments based on date

[1superuser]

Hi beloved programmers, I am creating this appointment scheduling database for a clinic and am not that experienced with VBA, The database captures the date, appointment time (a lookup field from another table), and other details from patients. I want to create a form where i can be able to select a date and all the appointments on that day show up below the lookup field - like below. , After that i would want to create a form with textboxes that change background color if that time of the day has an active appointment. Kindly assist with ideas or procedures i will do the rest of googling and code

 

[The_Doc_Man]

First, it should be noted that a Date field actually can store both date AND time in the same field, and Access will handle it correctly. And it has tools to isolate dates from times if you later need to separate the numbers. Call that "food for thought."

For what you have (i.e. separated date/time fields), you have a potential point of confusion. According to your mock-up of the form, you are using the same name of the control as you use in a field in your table. If you were going to enter a date into that and hit "SEARCH" then that control should ALSO be unbound.

If you enter the date into that box and hit your search, your problem is that for Access, your appointments are not normally distributed horizontally as you have shown it. Access likes vertical things but those boxes are horizontal. So you would need to instead open a recordset. From that point you would have to write some code to loop through a group of records for that AppDate and test the Apptime for each. You would then decide which of your 20 text boxes to fill in to show the appointment for that time&date.

The trick of changing background color is that you need to visit each box and do a DCount to see how many appointments have that specific date and time. It would be possible to step through a series of times in 1-hour (or other convenient) intervals looking for zero and non-zero values. If the result of the DCount (of appointments for a specific time) is 0 or not 0, you have the basis for deciding whether to change colors or not. For each of those cases, the same logic would apply, just different names.

 

[arnelgp]

there is a Great appointment calendar here, by mr.Hibbs
UtterAccess.com

 

[1superuser]

Thank you so much for the reference. I want to modify the calendar and i need some assistance. I want when i select to show weekly and daily appointments, i want the database to show all records for all employees but not only the selected employee, kindly assist as according to the screenshots of the module, i need to remove or specify to show all employee IDs

 

[MajP]

I am kind of curious what version of the database it is. It looks like a version I posted where I added in my year view to Peter Hibbs' calendar since it includes the button for the year view that I incorporated. However, that version was built on a single user version.

Date Pickers, Calendar Controls, and Calendar resources

There has been some recent interest in a replacement for the MS calendar control that existed prior to 2010. I have been collecting date pickers, calendar controls, and calendars for a long time and thought it would be a good idea to have one thread with lots of good examples. Some of these...

www.access-programmers.co.uk

Peter Hibb's calendar that I modified was designed as a single user. The appointment table did not have a field to identify a specific person "employee id". So Peter Hibbs' or someone else then took that version that I modified and incorporated the database to work with a multi person. That code you show is a modified version of the code I have seen. And it is more than code because the appointments table would have to be modified and the queries that load the forms would have to be built and utilized.

Can you provide the direct link to that version? I was planning to do this myself to do exactly this, but never got around to it. I would need to see the database you are working with, because as I said this version will have table and query changes not just code additions.

 

[MajP]

However, now I see my yearview is not updating when you open or change years, only after you add an Appt. I will have to fix that. It should show all appointments with a color code identifying the category (leave, medical, general) etc. I swore I had this working.