Microsoft Access Office VB VBA Help and Examples
           
             

Calendar Style Data Entry Forms

Lets say you have a pile of data in a table that has several entries for each date in a year or maybe just a single entry for each date in a year.

You really want to give the users the ability to add data in a calendar style form, showing a whole month at a time.

The below database has just three things in it.

1. tbl_Dates

DateValue1Value2
01-Jan-03John1
02-Jan-03Paul2
03-Jan-03James3
04-Dec-02Fred4
03-Dec-02James8
01-Feb-03Martin7
02-Feb-03Sue6
01-Dec-02Steve4
01-Dec-02James5
05-Dec-02John5
19-Dec-02Fred4
21-Dec-02Paul3

2. Dates_Subform. Which shows the Value1 and Value2 in a simply continous form and Date is hidden

3. Frm_Calendar. Which is where it all goes on. Frm_Calendar has 38 copies of Dates_Subform on it laid out in a classical calendar style. When you select a month from the drop down at the top it will do the following....

  • Hide the start and end subforms not required according to which day starts the month

  • Set each subform with its date

  • Set the recordsource to each subform to the appropriate single date

  • Set the defaultentry items for each subform so relations are maintained when user adds new data

  • Refresh the lot.

So you end up with a calendar style data entry form which users can edit multiple or single records or add new ones for a particular day.

It's  quiet a simple bit of code too, that shows just how re-usable access can be when required.

If you do use this then I would appreciate a donation as typically paying for any calendar style activex controls that let you do a limited version of the same thing would cost you about $100. Don't go mad just respect that I'm giving it out for free and I've got to pay my web costs.

Calendar Entry Example Database Access 2000

Calendar Entry Example Database Access 97

Author Comments : 

If you want to change whats on the sub form you can, but bear in mind the date field which is key to adding new records and must be set on the line 

frm.Controls("Date").DefaultValue = "#" & CStr((DayOfMonth)) & "/" & Format(Me.cbo_Month, "mmm/yyyy") & "#"

so that as users add records it gets the correct date, otherwise the data will be orphaned in the table.

Note : Yearly forms not an option as that many subforms requires too much memory and exceeds several limitations of MS Access.

 
HOME   SEARCH SITE   PRIVACY POLICY   CONTACT
The code and application content of this site is copyright of Smiley I.T. and as such reproduction in any form which is for commercial use requires the permission of the Webmaster. Any use of this code for non-commercial use only requires a link or comment back to the original page you took the code from.