CalendarView for Bookings in VBA Access

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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Dev-Soln LLCCommented:
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 (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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.

Jeffrey CoachmanMIS LiasonCommented:
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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.