Link to home
Create AccountLog in
Avatar of Michael Paravicini
Michael ParaviciniFlag 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 User generated image
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

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?
<<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.

Jim.
ContinuousFormColors.zip
ASKER CERTIFIED SOLUTION
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account