(I did serach for past topics, & found one, but the solution pointed to a link that doesn't work; sorry...)
I have a database (well, a half-database at the moment) which is used to book a hall. I want to add a calendar to the bookings form, so users can see what they're doing more easily.
This would only be useful, however, if I could grey-out days that have already been filled.
With some VBA code you can go through the booked dates and set the background field for each one to grey.
1. Set up a general calendar grid (5 weeks x 7 days) on your form
2. Determine what the month in question is.
3. Populate the calendar grid with the correct numbers (place the numbers in the right days)
4. Query the bookings to see what dates are filled
5. Loop through the days and grey out filled dates
Sort of a large topic, but if you have specific questions, let me know and I'll see if I can help.
1. Calendar Grid
Make 6 rows x 7 columns of unbound text boxes. [Did I say 5 rows before - silly me. The reason that you need so many rows is that a month of 31 days can span over 6 rows if it starts on a Fri or Sat.] It really helps to name each text box sequentially like CalDay01, CalDay02, etc.
2. Figuring the Month
You'll probably want a text box that stores the month used to populate the calendar. When you load the form, use the OnLoad event to set this value. The first day of the current month is convenient for some of the following calculations.
3. Populating the Grid
You will also need a VBA procedure to populate the calendar grid. It helps to break the reference month (the value stored in #2 above) into month, day, year and day of week (Sun, Mon etc). Fortunately, the DatePart function makes this relatively easy. Then, you step through the grid and place your numbers. Start in the first row with the correct day of the week and keep advancing. If you increment a temporary date variable as you do this, you can detect end of month and know when to stop.
4. Query the Bookings
Another procedure can create a query that checks the db to see what dates are all ready booked. I would declare a recordset and populate it with the booked dates (just for the month in question). I would then loop through the recordset and for each date, set the background color of the calendar grid to grey.
If you haven't done much VBA before, it can look like a daunting task. My best suggestion is to start in and see what you can do. If you get stuck at a specific point, try to work around it. If that doesn't work, post a specific question and let's see if we can help!
3. Populating the Grid
You will also need a VBA procedure to populate the calendar grid. It helps to break the reference month (the value stored in #2 above) into month, day, year and day of week (Sun, Mon etc). Fortunately, the DatePart function makes this relatively easy. Then, you step through the grid and place your numbers. Start in the first row with the correct day of the week and keep advancing. If you increment a temporary date variable as you do this, you can detect end of month and know when to stop.
I think this is the part that is holding me up. If I am reading this correctly, there is now way for access to "know" what day of the week a particular month should start on without me telling it so right? as far as advancing dates, you just mean something like text2=text1+1, text3=text2+1 or something right?
Good question. I'll try to explain this coherently ...
1. Calculating the Date for First of the Month.
You can get the first of the month through a series of calculations. E.g. suppose that Date1 is an arbitrary date value. It might be the first day, or not.
DatePart("m",Date1) returns the month (1-12) of Date1
DatePart("yyyy", Date1) returns the year (e.g. 2005) of Date1
DateSerial ( year, month, day) returns a date from the 3 inputs of year, month, day.
Let's call that FirstOfMonth for purposes of below.
2. Calculating the Day of Week
Now we can figure out which day of the week this is using the WeekDay function. E.g.
Weekday (FirstOfMonth)
This will return 1 - 7 (where 1 is Sunday, assuming default system settings).
3. Filling out the Calendar
So now, set up a loop to step through all the days of the week.
Start in the first row of the calendar grid, but offset a number of columns equal to the weekday. E.g. if the Weekday(FirstOfMonth) is Monday (= 2) then start in the second column. Set the value of the text box equal to the day of the month. (Okay, we probably needed to initialize all the text boxes as blank to begin with).
In code, this might look something like the following (note, this is NOT compiled and tested)
Code:
Dim Date1 as Date
Dim FirstOfMonth as Date
Dim TempDate as Date
Dim FirstDay as Int
Dim DayIndex as Int
Dim TextBoxIndex as Int
Dim Done as Boolean
' Get Date1 from somewhere... Use Now() function or something
' Initialize the calendar grid to blanks
FirstofMonth = DateSerial (Date("yyyy",Date1) , DatePart("m",Date1), 1)
FirstDay = Weekday(FirstofMonth)
DayIndex = 1 ' Start counting days at 1
TextBoxIndex = FirstDay ' Start indexing text boxes at first day in month
Done = False
While Not (Done)
' Set the value of the correct CalDayxx text box
Me.CalDay & right("00" & TextBoxIndex, 2) = DayIndex
DayIndex = DayIndex + 1
TextBoxIndex = TextBoxIndex + 1
' Are we done?
if (Month(FirstOfMonth + DayIndex) <> Month(FirstofMonth)) then
Done = True
endif
wend
I understand what we are trying to do with this line, but I am not good enough with VBA to know how to correct it. I tried a few thing, but to no avail. Can you help?
Whoops, my bad. There is a little trick in how to address the controls programmatically....
Code:
Dim ctl as Control
Dim strNum as String
strNum = Right("00" & I, 2)
Set ctl = Me("CalDay" & strNum)
ctl.Value = DayIndex
Note that the Me("control name") is an alternate way of referring to a control. This lets you build the strings to refer to each one, and why it is really helpful to name them in an ordered fashion.
I can't remember where I first saw this approach, but I didn't come up with it myself.
Let me know if you have any further questions. I'm obviously typing faster than I am thinking - and I'm not typing that fast either
Well I don't get an error anymore, but something is not working right. It only puts a number in the first box, but it put the number 30... even going by december, this is not the last day of the month, so I don't know how it came up with 30.
I have attached the DB so hopefully you can see if i screwed something up
Thanks for all your help!
********UPDATE********
I changed this
Code:
strNum = Right("00" & I, 2)
To this
Code:
strNum = Right("00" & TextBoxIndex, 2)
And it works correctly except that it stops a day before it should. It is still stopping at 30 instead of 31.
Sorry my example code was a little buggy, but I'm glad that you figured out what I meant (rather than what I said).
Your example code includes a "+2" for textbox Text45 (which I assume is supposed to be the month). Not really sure why this is in there and I think that you probably need to delete that part.
Yeah, I was changing the month around to verify that i had it working and forgot to change that back before i posted it. I will correct it. Thanks for pointing that out.
Got another question about this for you. Here is what I want to accomplish.
Have a calendar to track if a task has been done for that day. If the task has been done, then the user clicks the date and it will be grayed out showing it is done for that day. When the form is opened, the calendar should check to see if a record exists for the current month and year, if so, display that data, if not create a new record.
I know I am probably just over thinking this, but I can't figure it out.
now let me tell you what I have done, and if there is an easier/better way please let me know.
The calendar we created previously.
another set of text boxes bound to a table that will hold a 1 or 0 (1 if completed, 0 if not so I can sum the days the task was completed)
I set the on click of the date to change the value on the other textboxes and gray out the date (this works no problem)
when the calendar is opened, I have it open to a specific program ID, and that works fine, I just know it won't work when a new month comes. I tried setting the query behind the calendar to also check for the month, but it wasn't working.
I hope I have explained this good enough, if you need a better explanation, let me know.
Sure, I can try to help - but can you re-post as a new thread so that others can "tune in"?
Also, I think that I am understanding your question, but please clarify: What does the Program ID connect to - your tasks? What specifically do you think won't work for the new month?
as the icon...?)
Thanks!
