Hello Everyone, this is my first post.
I have read, however may have missed a relevant post, on Calendar Reports on this site.
Several are far more complex than what I am seeking.
I require a Calendar Report to populate with Event Names and Locations from an existing Table containing Event dates.
The Table in question has for each entry the Event Name, Event Date and Location, therefore, there is no need to look-up another Table for data.
I have looked at a post here with an attachment regarding a Leave Request Calendar, however, the VB code is far more complex than is required for my purposes.

For arguments purposes the Table is called "ABX" and the date is "EventDate", the event name is "EventName", along with the location being "Region".

I have downloaded the "calform2k" and like the look of the calendar very much.

I can follow the code setup in order to display the calendar interface or report, however, I wish to simply have the calendar show the above entries on the relevant dates.

The calendar I downloaded is rather complex and provides many more options than I require.

Could someone here please help me with the VB code required in order to read the table data and populate the calendar?

Thank you in advance...

Hi,



Could you post what you have tried so far so we can see how much more you will need? Thanks.

Okay,

I have attached the working copy of the database as a zip attachment.
Please bear in mind that the Calendar within the database is based upon a sample from this website.
I have stripped out some of the code in order to remove the functions not required, however, I am unfortunately very new to Visual Basic and do not have the skills to properly code the requirements.
Again, I apologize and am posting here as I genuinely need help and do not know enough to do as my friend has asked.

Any help with this will be greatly appreciated and I will donate to this website once I have transferred money to my PayPal account.

Thank you goes out to anyone willing to provide time with this...

Okay, as I agreed I have donated through PayPal to this website.

Hi Lucrecious,

I had a look at your database example and the code looks just too complex to try and debug so I have attached a much simpler calendar database which you may find more useful. I don't know what you want exactly but this one allows you to select a month and then enter any text directly into any date cell (or modify and/or delete text in a cell). You can add more fields to the Appointments table if you need them although you will then have to provide some method for the users to enter text in any extra fields. Also the date field specifies a single date, i.e. you cannot have an appointment (if that is what you are recording) span several days. Note also that if your system is a multi-user system with a split database then the table tblMonthData must be kept in the front-end file.

If you need something more complicated then have a look at this Outlook Style Calendar in the UA archive.

HTH

Peter Hibbs.

Thank you for your response Peter.

I did have a look at your calendar and it is very impressive.

However, what I require is that the Calendar behave like a report and displays and/or reads from the table.

The code from the Calendar sample I provided is not what I require, it was there from the sample Calendar I downloaded from this website.

The code that I require; apart from that which is used to display the calendar, would simply show a concatenated string from data already in the table as per my initial post.

The Calendar should be unidirectional in the sense that it reads from the table various fields and displays the text on the appropriate date.

Please, if anyone here can provide code which gives some direction I will be deeply appreciative.

Thank you.

Hi Lucrecious,

Your question is a bit vague but as I understand it now - you have a form (presumably) which has a button and when you click this button you want to print a report to the printer which shows a calendar in which you want to show the contents of a table called ABX. The table has fields called EventDate, EventName and Region (I assume these will be Date/Time, Text and Text types), is this correct? Presumably you already have some mechanism to enter the data into the table?

The question is - what exactly do you want to print?
Do you want to show a specific month in a conventional calendar month layout or do you want some other layout?
How do you decide which week or month or year you want to print?
Do you want a multiple page report, say one page for each month for 12 months or what?
Do you want the print out to be in Portrait or Landscape mode?
How much text is there in each cell?

What you are asking for is pretty straightforward (albeit with a bit of VBA code as you suggested) but you need to be a lot more specific! Have a look at this earlier thread to see if this is anything like what you are looking for.

If you can provide more info perhaps we can come up with some suitable code (and perhaps a copy of the database would be useful).

Peter Hibbs.

Peter,

Thank you once again for your input.
The Calendar, upon reflecting on the requirements, should be able to show 3 to 6 months of upcoming events from the CRQ table.
All data, as previously stated, will already be in the table.
The information to appear from each entry should be: COLLECTION - MARKET - DOOR.
These three fields are form the CRQ table.
I have considered the fact that entries on a specific date could surpass the limits of the text box resulting in overflow.
Not certain how this should be handled.
The link you provided does contain a very interesting database, however, my need are that the Calendar should pop-up with the next 3 to 6 months of events already populated, in landscape view, and ready for print.
The Calendar is simply an at-a-glance view of: near future term - events forthcoming.
I have attached the table and that Calendar retrieved from another post on this website.
The Calendar pasted in the database is not concrete in terms of use, I simply liked the look of it.

I trusts I have been clear as to my requirements...

Thank you.

Hi Lucrecious,

OK, I am a bit confused now! We seem to have a new table and new fields now, table CRQ and fields COLLECTION - MARKET - DOOR.
It sounds like you want to open a Calendar report in Preview mode which shows the current month and the next two months (or more maybe) with the fields from the table displayed in the appropriate date boxes. The calendar should look something like the database you attached. Is this correct?
Can you post a cut down database with the CRQ table and include some data in a few records to show what sort of text will be stored. If the text in a date cell does 'overflow' then I don't see any easy way to show it, on a report you can't expand a field easily (without messing up the whole report layout) like you can on a form!

If you can provide a bit more information then I think the report design that I mentioned earlier could be modified to do what you want and I would be happy to do that for you. Let me know what you think.

Peter Hibbs.

Peter,

Yes, you are correct, the Calendar is a form which can be printed to a report.
I have attached sample data and the Calendar Report as well as the Calendar Form.

Thank you once again.

Peter,

Whichever working model you wish to provide will be acceptable.

I do not currently have the skills; nor the available reading and research time required in order to improve on my skills, so as to effectively write the code.

Thank you.

Peter,

Your design is very good.
Please provide direction or coding so yours can pull in the field data from the three fields mentioned.
In some instances there will be more than one entry per calendar date.
If your calendar can be expanded in a landscape setting it will work very well.

Thank you.

Hi Lucrecious,

Sorry for the delay, this was a bit more complicated than I first thought. Anyway, try out the attached demo and see if it does what you want. I don't know how you will get on with multiple events on the same day fitting in the calendar cells but I don't see that there is much else you can do. I have made the font fairly small for the date cells (Arial Narrow) and it would help if you used lower case characters instead of all upper case. I have concatenated the three fields you mentioned for the display and if there is more than one event for the same date, I have moved the event down to the next line (with a vbCrLf at the end of the line). You may want to adjust that in the code in the FillData sub-routine.

I had a few problems when I tried to import your CRF table into my demo, it threw up all sorts of errors so it may be that it is corrupted in some way. In the end I rebuilt your table from scratch which seems to work OK. Note that I removed the formatting on the Date type fields that you had as this is not normally a good idea, it will probably confuse you later and is not necessary. Any date formatting is better done on the forms or reports. Also, I changed the contents of the dates and text fields around in the table while I was testing so you will need to do those again. The calendar shows the next three months by default but you can change that in the code in the frmTest form to suit your requirements. If you have a separate form that has the button to open the calendar report then you will need to copy the code from my frmTest form to your own form.

Hope this will help but unfortunately I don't have a lot of time to do more programming on it so if you need a lot of modifications you will have to dive in yourself.

Good luck with the project.

Peter Hibbs.

Peter, fantastic stuff.

I amended the dates for events to show on EventDate rather than TodayDate.

Also, is it possible to change so the report works off of the current date rather than TodayDate (which is not the actual date).

The Report also does not increment the number of months properly as I changed it to show 6 months ahead rather than 3 and yet the report still only scrolls three months ahead.

So the ideal way it should work is as follows:

1. Read current date and display current + 5 months in future
2. Populate days with concatenated string from 3 fields in database (this works perfectly!)

I have attached the demo database with some amended entries and changes to VB, however, I cannot figure out the way to make it work from the current date forward.

Peter, thank you for all your efforts, it is almost there for what I need, can you please look at having it read the current date Now() or Date() and then increment the calendar 5 additional months in the report?

Hi Lucrecious,
See my comments in blue below.

Peter, fantastic stuff. Thanks.

I amended the dates for events to show on EventDate rather than TodayDate. OK, I didn't know which date you were using for that. However, you must make sure that every record has a date in this field or the DB will 'bomb out', I suggest you change the Required property of this field to 'Yes' in the table.

Also, is it possible to change so the report works off of the current date rather than TodayDate (which is not the actual date). I think you will find it already does (the error below probably made you think otherwise).

The Report also does not increment the number of months properly as I changed it to show 6 months ahead rather than 3 and yet the report still only scrolls three months ahead. You correctly amended the TodayDate changes in the VBA code but you did not change it in the query which is bound to the report. Open the report in Design mode, open the query definition in the Record Source property and change the two occurrences of TodayDate to EventDate and close the query. Everything should work OK then. However, you should also be aware that if there are no events for a particular month then the calendar for that month will not be displayed. I did not think there was much point in displaying a calendar for a month if there was no data to display and, in any case, it would be quite difficult to display blank months.

So the ideal way it should work is as follows:

1. Read current date and display current + 5 months in future
2. Populate days with concatenated string from 3 fields in database (this works perfectly!)

I have attached the demo database with some amended entries and changes to VB, however, I cannot figure out the way to make it work from the current date forward.

Peter, thank you for all your efforts, it is almost there for what I need, can you please look at having it read the current date Now() or Date() and then increment the calendar 5 additional months in the report? OK, hopefully it should work when you make the changes above.

BTW, a tip which may be useful. At the moment the report shows 6 rows of boxes for the possible 6 weeks than occur. To make more space for your events you could move the last two boxes (txtDate36 and txtDate37 plus the two Data boxes) up the report to cover the first two boxes (txtDate1 and txtDate2) so that when there are 6 rows of dates in the month the last one or two days of the month appear at the top of calendar. You could then make all the Data boxes a bit taller and so get more text in them. Make a copy of the report first though, just in case you want to go back to the original because editing those fields is a bit tricky when they are placed on top of others.

HTH and let me know if you get any problems.

Peter Hibbs.

Peter,

Brilliant!

A simple thank you may not suffice; therefore, let it be known that I am extremely appreciative.

Please accept my immense gratitude.

Hi Lucrecious,

Glad to be of help.

Peter Hibbs.