Avatar of Michael Paravicini
Michael Paravicini
Flag for Chile asked on

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
Microsoft Access

Avatar of undefined
Last Comment
Jeffrey Coachman

8/22/2022 - Mon
Dale Fye

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)

<<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 Coachman

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes