Highlight multiple date ranges on a calendar

[MattK23]

This forum rocks!! I have found sooo many helpful tips and tricks here but I am finally stumped. I am in no way an expert with Access or VBA so my code might look amateurish at best but it works.

I have attached a sample db for reference as my explanation might not clearly explain my problem. Here it goes: I have a form with an annual calendar view and a subform with date ranges. The subform has multiple date ranges and the dates on the calendar are highlighted in green for these date ranges. Currently the calendar highlights one date range fine when that subform's record is selected. Once I select a new record in the subform, that record's date range gets highlighted in green and the previuos highlighted date range is no longer green. I need it to highlight all date ranges from that subform green at the same time.

I think my problem is that I have the "highlighting code" within the "load calendar code" but I do not know how to highlight the date ranges any other way. Please help! Thanks and apologies if this is not clear but the sample I've attached speaks tons.

 

[HiTechCoach]

My thought is that you will need to loop through all the records in the sub form as you "load" the calendar, not just he current sub form's record.

I tried to download and look at your example but it gets lots of errors cause by four (4) MISSING references. I am not sure why you have references set to libraries in the "c:\program files\Nero\Nero8\..." and other software apps files.

Since I can't get your app to run properly, I can't see what is happening.

Do you know which reference are require to make this run?

TIP: only set a reference to library you are actually using and also have the proper license to use. Just because you can search your drive and find the library on the hard drive does not mean that you should be using it. It may be possible that extra libraries that you do not know how to use could cause some of Access's standard functions to not work properly.

 

[MattK23]

Thanks for the response and tip HiTech. I have fixed the references and uploaded revised db.

I've tried looping through the subform's record when loading the calendar but nothing happens. I believe it's the way my if statement is set up for my green highlight code.

 

[HiTechCoach]

I was able to get this latest versions to run.

Currently you are using the sub form's On Current event to load just that rows data. Also ,the sub loadCalendar will only work with the current since it uses form reference like this:

Forms!Schedule![Schedule2 subform].Form!txtContractStartDate

It also appears that the sub loadCalendar clears the calendar and loads the calendar at the same time. This will only allow one date range to be loaded at a time.

The way I see the issue is that the sub loadCalendar will only allow one date range to be loaded. IHMO, this procedure will need to be modified to handle multiple date ranges.

If it we me, I would make a separate method to clear the calendar (set the BackColor = vbWhite fro the entire calendar) and have the sub loadCalendar only Load the calendar (set BackColor to some color).

I would also change the sub loadCalendar so that it does not mark both the Contract Start and End Dates and the Assignment Start and End Dates in one pass. This makes the procedure less generic and will not allow multiple data ranges to be loaded. I would add an additional parameter to allow the procedure to be called multiple times so that you can load the Contract dates and use multiple calls to the procedure to load all the different Assignment date ranges. I would probably have parameters for start date, end data, and back ground color. This would make the procedure more flexible and reusable.

This will allow you to either load the calendar by lopping through a recordset (my preference) or looping through the records in the sub form.

 

[MattK23]

Thank you for taking the time to look at my code. I'm pretty sure I didn't explain myself that well on my original post so your patience is much appreciated. I will definitely try these recommendations.

If it we me, I would make a separate method to clear the calendar (set the BackColor = vbWhite fro the entire calendar) and have the sub loadCalendar only Load the calendar (set BackColor to some color).

So you're saying one method would be to load the calendar only and another method to only highlight the calendar? Should I further separate it out so that there is one method for each color I wish to highlight or should I combine highlighting all colors into one method?

... I would add an additional parameter to allow the procedure to be called multiple times so that you can load the Contract dates and use multiple calls to the procedure to load all the different Assignment date ranges. I would probably have parameters for start date, end data, and back ground color. This would make the procedure more flexible and reusable.

How would I go about doing this? Can you please provide some sample air code or logic? It doesn't have to be working, I just would like to see how to implement such a method? Thanks again for your time and effort.

 

[HiTechCoach]

Thank you for taking the time to look at my code. I'm pretty sure I didn't explain myself that well on my original post so your patience is much appreciated. I will definitely try these recommendations.



So you're saying one method would be to load the calendar only and another method to only highlight the calendar? Should I further separate it out so that there is one method for each color I wish to highlight or should I combine highlighting all colors into one method?



How would I go about doing this? Can you please provide some sample air code or logic? It doesn't have to be working, I just would like to see how to implement such a method? Thanks again for your time and effort.

Between your explanation and the working example, your issue is very clear.

So you're saying one method would be to load the calendar only and another method to only highlight the calendar? Should I further separate it out so that there is one method for each color I wish to highlight or should I combine highlighting all colors into one method?

I think of "load" as something different.

I see it this way:
1) Initialize the values of the controls. This would be setting the calendar to be all white. No data loaded.

2) Load data into the calendar. I would use one generic method to do this using passed parameters. By passing different parameters, to allow the same method to handle multiple tasks usually not at the same time, but with multiple calls.

How would I go about doing this? Can you please provide some sample air code or logic? It doesn't have to be working, I just would like to see how to implement such a method? Thanks again for your time and effort.

Are you needing an example of a procedure that is using parameters?

Here is a working example with parameters:

Public Function CalcDiff(dteDate1 As Date, dteDate2 As Date) As Integer
        CalcDiff = Year(dteDate2) - Year(dteDate1) + (DateSerial(Year(dteDate2), Month(dteDate1), Day(dteDate1)) > dteDate2)  
End Function

 

[MattK23]

OK, baby steps here. I've separated the original loadCalendar into loadCalendar and highlightCalendar. And I've added a function called hlDates that highlightCalendar calls. The names can probably use some work but that can be taken care of later. Everything is working just like the original code. I've attached a new db. Am I on the right track here?

I will try looping through the subform's records and see if it works the way I want.

 

[HiTechCoach]

OK, baby steps here. I've separated the original loadCalendar into loadCalendar and highlightCalendar. And I've added a function called hlDates that highlightCalendar calls. The names can probably use some work but that can be taken care of later. Everything is working just like the original code. I've attached a new db. Am I on the right track here?

I will try looping through the subform's records and see if it works the way I want.

Took a quick peek at your new code.

The new function highlightCalendar is a great start. I would not use the second function called hlDates . They probably should be a single function. I do not see any benefit in splitting it the way you did. I would have the procedure highlightCalendar have the data range parameters.

Also:
You should not have the loadCalendar call the highlightCalendar. This is the original issue. The need to be two separate tasks. You will need to call the loadCalendar once, then separately call the highlightCalendar for each record.

he names can probably use some work but that can be taken care of later.

I have found it best to name the procedure with a name that is very descriptive from the start. I find this helps make sure that the code does only what it should and follows your design properly. Renaming the procedure later can be tricky and even introduce error if you do use a good naming convention fro the start.

 

[MattK23]

OK, did what you recommended and nixed hlDates and changed highlightCalendar to highlightDates. I've been racking my brain on implementing a loop to highlight the assignment dates green. Here's the code I'm using:

Do While Not rs.EOF
    Call highlightDates(frmControl, Nz(rs!AssignStartDate), Nz(rs!AssignEndDate), vbGreen, datStart)
     rs.MoveNext
Loop

But it does not do what I want and I can't figure out how it's working. It looks like it stops at the end of January for some reason. Revised db is attached.

 

[HiTechCoach]

I made some adjustments to the code and have it working. The code still could be cleaned up to make it better.

 

[MattK23]

Needless to say you're a genius HiTech. Looking at your code was like having the blinders pulled off my eyes. I was banging my head on this little piece for a very long time. Like you've been saying all along, I was trying to load and populate the calendar at the same time when I should have separated them.

I've cleaned up the names as well because I believe that was a big part in what threw me off too. You are right once again, begin with good descriptive names. I gave you rep points already but I wish I could give me for all your help.

The new cleaned up db is attached if anyone wants to take a look.

 

[HiTechCoach]

Thank you for the kind words.

I am glad I could assist and help you learn a little more about programming!