CalendarView for Bookings in VBA Access

Michael Paravicini
Michael Paravicini used Ask the Experts™
Hi, I need to program something similar to the attached screen in access. I tried as a datasheet but have an issue with the lack of flexibility of setting different background colors for individual cells - is there a better way of doing it in access?? Thank you Michael CalendarView.jpg
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

I believe you could handle this by including a [Status] column for each of the dates in the recordsource for datasheet, but not actually putting those fields on the form.  Then use conditional formatting based on this status column to set your colors.

I'm interested in how you get the text in some of the rows to span multiple columns of your datasheet, or is this being displayed in Excel in the image above?
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

<<is there a better way of doing it in access??>>

Yes and no.  When it comes to something like this (a complex grid interface), your hands are a bit tied because Access does not work well with many 3rd party controls.

But there are some (i.e. MSFlexGrid) that it does work with.  Not always completely, but sometimes stable enough that you can use them.  

I've always made it a point to stay away from non-native controls, so I don't know of anything off-hand, but I'm sure there are a few out there.

Dale mentions conditional formatting and that might work as well.

Last, there are continuous forms where you have much more control, and using the "old school" method of coloring backgrounds shown in the attached db, you can do some amazing things, including colors for individual columns and/or graphics.

 Basically the technique is that you use image control(s) placed behind the other controls and then fill them conditionally.   Since they are image controls, you can have them display not only colors, but anything.   Graphics, pictures, or whatever.

 But that interface will be tough to do in Access using only native controls.   One thing you have to keep in mind is that an Access form has a lifetime limit on the number of controls, which is stated as 754, but in truth is a little over 1,100 (somewhat depends on the version).

 I think if it were me, I might hunt for a grid control.

MIS Liason
Most Valuable Expert 2012
Do you need this "Gantt" interface for Viewing or editing (or both)?
May not be an option, ...but MS Project can present data in this fashion.

As the others have stated, MS Access does not directly support this type of interface.
So if your goal was to get an Access Gantt "Control" to simply drop into your app, ...there is none.

You have many options to doing this, ...just do a Google search of MS Access Gantt Charts, ...for many resources on rolling your own.
This has been around for a while (has had a downloadable Access version on for a few years)
Never used it myself, you should contact them first:

There a video on youtube demonstrating this as well, ...but again, I cant vouch for any of these products/companies.

My felling is that if Access UI/VBA cannot do this natively, ...dong this in Access from scratch will be a bear.

So while you might be able to get something in Access that "Looks" like this, ...having it "function" in the same way may be another story altogether.

If this interface is a deal breaker, ...then you may have to look at other development platforms.

BTW, ...note that some pretty cool Gantt-like interfaces have been possible in Excel for years, ...
(There is even a "Gantt Project Planner template" available under File-->New)
So you might want to do a search here (or on the web) for "Excel Gantt"

Finally, ...asking Access to create a slick Gantt-like interface is like asking Excel to create a slick Parent/Child interface.
Sure, can be done, but Excel is not designed to present data in that manner.


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial