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
| Date | Value1 | Value2 |
|---|
| 01-Jan-03 | John | 1 |
| 02-Jan-03 | Paul | 2 |
| 03-Jan-03 | James | 3 |
| 04-Dec-02 | Fred | 4 |
| 03-Dec-02 | James | 8 |
| 01-Feb-03 | Martin | 7 |
| 02-Feb-03 | Sue | 6 |
| 01-Dec-02 | Steve | 4 |
| 01-Dec-02 | James | 5 |
| 05-Dec-02 | John | 5 |
| 19-Dec-02 | Fred | 4 |
| 21-Dec-02 | Paul | 3 |
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.
| 