Calendar display on a form?

B

bazzanoid

Registered User.
Local time
Today, 03:08
Joined
Aug 30, 2008
Messages
35
Afternoon all,

I'm building a bespoke car rental system to replace a 20 year old piece of software (yep, 20!), and the one feature that i've been asked to keep in is our car view calendar, which enables us to see at a glance where vehicles are, if they're booked, etc. While i would have no problem in filling up a calendar with the necessary queries to populate it's view, i have no clue on how to even start to display it. Here's the current one we use, and i would appreciate any help in recreating it's look if anybody has any suggestions!

bookingcalendar_zpsxhzupv7u.gif
[/URL]

TIA

Barry
 
How is it done currently? What is the current software?
You might google some Vacation Planner type databases using Access for ideas.

See this from Peter Hibbs
 
Last edited:
Wow! that's pretty slick. What's currently creating that display?

The only way I would know to do that is make it out of textboxes using some naming convention that would make them easy to access e.g., txtRow1Col1. You can access a textbox with a sub that would create the name and use statements like

Code: 
Sub ChangeBackColor(frm As Form, Row As Long, Col As Long, BckColor As Long)

frm.Controls("txtRow" & Row & "Col" & Col).BackColor = BckColor

End Sub


I think it will take a bit of code to transform the queries in to this display.
 
Hi Barry,

the way i approach calendar displays on forms is to use x number of unbound text boxes for the dates, in the forms header (x = days to show 7,14 etc). You can then add all types of controls to change the displayed dates (move day by day, week by week, month by month etc). Then create a query to return the values relevant for each of the dates represented by the unbound textboxes. The method you use to get these values depends on how the data is stored in your tables. If you store vehicle use day by day or if you have a start date / end date set up.

You can probably use Conditional formatting for the colouring.

If you can let us know the table structure, we may be able to help further.
 
Thanks all,

The software is a VERY old car rental program called "QuickDrive Office" - it's SQL based but as it's a custom front end it's not something that can be amended are looked into to find the underlying codes or queries etc. I can however access the data, and i've imported the vehicle and customer sets into it to save me some time and give me some data to work with for testing purposes.

Needless to say the columns are the days of the current month with the ability to move next/previous month.

The rows are each active vehicle in the Vehicles table and it's bookings (if any) from the Rentals table, which store the car (CarID) and customer (CustID) along with a start date (DateOut) and end date (DateDue), and a few other fields relevant to the particular hire but not the calendar. White space is unbooked/unused, blue is currently open hires, and yellows are 'special' bookings where a rental agreement is not created, but a note is placed on the file for the marked days with a comment on the purpose of the booking. I'm also planning on adding a grey colour to show closed rentals. And lastly, as i'm sure is obvious, < > indicators for start and end days of hire.

A simplistic design but truly brilliant for a standalone hire location with a fixed fleet!
 
Last edited:
I forgot to mention that any scheme that uses textboxes needs to consider that the number of controls and sections that you can add over the lifetime of the form or report is 754 according to this site. Maybe you could make each row a subform. I also should mention that you can't add controls to a form at run time so unless your user have full Access you need to populate the form with the maximum number of controls you will need and hide the ones that are not in use.
 
The Peter Hibbs calendar is quite easy (Not beginner level) to adapt and customise, it will scroll and allow various display modes/days or hours viewing.

I've added 4 or 5 more unit level lines to the form just to suit my display purposes (Which is not a ten minute job as the have to be named sequentially for the array's to populate them correctly :) ) I'm not sure how many more you could add, as I think I was up to about 600-650 objects.
 
I did an example of a "data grid" here. I used it to show data in a tabular row/column fashion rather than just row. It work like IssKint and sneuberg describe in post 2 & 3. Due to the nature of the design you can pretty much code whatever you want to do with the "cells".

You can get round the limit of the number of objects in a form by creating subforms. Since the text boxes are actually addressed through an array of custom text box classes, once the addressing of text boxes of the subforms to the array has been done, then changing the cells attributes in code is trivial.

Just to add, the current example is scrollable. A dataset his held in memory so scrolling works quite fluidly.

hth
Chris
 
Chris,

I went back to the example you mentioned in the Access and OOP examples.
Very interesting especially when you can select a table from a variety of tables in the combo and it populates immediately.

Is there any more documentation for the classes and application/example?

One thing I noticed is the arrows below the main material pointing Left and Right seems to alter the columns vertically; and correspondingly the arrows at right pointing up and down, seem to modify the horizontal data.

At some point I'll study it further, but was wondering if there was any additional info to go with the example. I keep saying I'm going to learn more about classes, but there is always something else.

Thanks.
 
Last edited:
Impressive stuff Chris - completely melted my brain as i've not ventured into classes and my skill level is relatively low haha

I'm going to go take a paracetemol and start trying to wrap my head round that!
 
Please see attached demo of my approach. Its very basic but the principles can be expanded easily enough.

When you update the rootdate on BookingDiary form (normally rootdate would be invizible) the diary is adjusted. The colours are worked from conditional formats.

I have used this approach in attendance diaries, work schedule diaries, goods in diaries etc.
 

Attachments

jdraw said:
Chris,

I went back to the example you mentioned in the Access and OOP examples.
Very interesting especially when you can select a table from a variety of tables in the combo and it populates immediately.

Is there any more documentation for the classes and application/example?

One thing I noticed is the arrows below the main material pointing Left and Right seems to alter the columns vertically; and correspondingly the arrows at right pointing up and down, seem to modify the horizontal data.

At some point I'll study it further, but was wondering if there was any additional info to go with the example. I keep saying I'm going to learn more about classes, but there is always something else.

Thanks.
Click to expand...
Thanks for the feedback. I've corrected the arrows (doh!). I've also added some more comments. I'll try and write a better overview of how it all fits together particularly the part that each of the three classes plays.

I suspect the op needs to go with one of the other solutions. They all seem good options.

If the op can send some example data I might try and do a cut down version of my datagrid for the sake of interest.
 
Isskint, that's exactly the sort of thing I'm looking at, with a bit of expansion and alteration of course!

Chris, I can't for the life of me squash the database down under the forum max size limit, so i've linked it instead - i've stripped out everything barring some bare data. The Rentals table is the one that holds the base data for the intended calendar view.

https://drive.google.com/file/d/0BxapSA34osyvcGRxaW42Wm5NaWc/view?usp=sharing
 
Chris,
Thanks for getting back. I think the updated database with class modules and comments would be a good addition to the sample database/code section. Also, would be useful to those following the Access and OOP thread.

Also, the sample code by Isskint (post 11) seems to fit the OP's need--- it may depend on volumes and data structure.
 
jdraw said:
Also, the sample code by Isskint (post 11) seems to fit the OP's need--- it may depend on volumes and data structure.
Click to expand...

That is the flaw of my method :banghead:. The more records you have and the more days you want to show on the calendar, the slower it gets. That said, i know an attendance diary i set up a couple of years ago, handles 350 employees, with records over 5 years, showing 7 days on the diary form and updates in just over 1 second.

But then, that is what archiving is for :p
 
Isskint said:
That is the flaw of my method :banghead:. The more records you have and the more days you want to show on the calendar, the slower it gets. That said, i know an attendance diary i set up a couple of years ago, handles 350 employees, with records over 5 years, showing 7 days on the diary form and updates in just over 1 second.

But then, that is what archiving is for :p
Click to expand...

lol mine is just for about 30 vehicles and however many bookings they have with a 30/31 day display
 
Please keep us up to date with your decision re approach. I would be interested in seeing your evolving database. This "type of requirement"/calendar display on form/ is the essence of posts from time to time.

Good luck.
 
I believe the Peter Hibbs example used over whole days would perfectly fit you needs.
It defaults to display a complete month of 31 or 32 days and will accommodate quite large amounts of data.

I can't do it right now but I could easily adapt your data to display on it. I increased the vertical display to show 16 units (people in my case) see the attached. It can scroll in either direction.

attachment.php
 

Attachments

  • CalendarGrid.jpg
    CalendarGrid.jpg
    99.7 KB · Views: 620
Minty said:
I believe the Peter Hibbs example used over whole days would perfectly fit you needs.
It defaults to display a complete month of 31 or 32 days and will accommodate quite large amounts of data.

I can't do it right now but I could easily adapt your data to display on it. I increased the vertical display to show 16 units (people in my case) see the attached. It can scroll in either direction.

attachment.php
Click to expand...

Could you give us a link to the site where we can get the Peter Hibbs database.
 
You must log in or register to reply here.

Similar threads

D
Calendar displaying autonumber from a combo box..
Replies
5
Views
347
Pat Hartman
P
M
Solved Event Click no longer working on a header label on a form
Replies
15
Views
875
theDBguy
theDBguy
E
Input to Form's RTF field: cursor disappears - on screen display does not "hold" to show cursor on bottom line of text
Replies
3
Views
669
EdK
E
skiller
Current record changes on resizing form - why?
Replies
19
Views
1,355
isladogs
isladogs
raziel3
Solved Cascading Combo Boxes on a Continuous Form and keep previous record data displayed
Replies
13
Views
1,369
raziel3
raziel3

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom