Adding activities to a table with a form

[JemCain]

I'm running a summer holiday club for around 30 famiies that has 19 different activities on different dates. I'm trying to create a database to record which activities parents have booked for. They can, and will, change their bookings fairly often.

So far I have 4 tables: tblParentData, tblChildrenData, tblActivityData and tblActivityBookings related as below...

The structure assumes that when a parent books an activity they will bring all their children, but that's OK.

I've also made a form to show what each parent has booked for, using a subform which is linked to a query to find which activities parents have booked for...

My problem is what happens when I want to add a new activity for that particular parent. My ideal is that I can click on the "Add activity" button to open up another form which looks something like the one below but has a check box next to each activity. Checking the box will automatically add a new record to tblActivityBookings for that parent (and unchecking a box would delete the existing record). But if that's not possible, what's the best way to achieve the same end?

Thanks very much for any help!

 

[plog]

First, I always have to address table issues:

1. You're not using ID values properly. You've assigned them as primary key in all your tables, which is great. However, when you JOIN tables together, you do so by a primary key. As structured, tblChildrenData should not have ParentNumber in it, it should have the ID from tblPArentData in it. That's how primary/foreign keys work. The same applies to your other tables.

Now, if ParentNumber is unique it can serve as the primary key of tblPArentData and its ID field isn't needed at all. In whichever case you choose, the primary key of one table goes into any other tables you are joining it with.

2. ActivityDate and ActivityTime should not be seperate fields. Its a Date/Time datatype for a reason--it can hold both pieces of data.

3. Don't save data you can calculate. You have a table that holds child data, with that you can calculate how many children a parent has, therefore you do not need NumOfChildren field in tblPArentData.


As for your issue about forms, I would create the subform such that it can handle inputs. The subform would have tblActivityBookings as its source and be a continous form with just one input--a drop down. That drop down will show the name and date/time (and any other data from tblActivityData you want) but use the ID field from tblActivityData and put it into tblActivityBookings. Here's a link on how those type of drop downs work:

MS Access 2003: Bind Combo Box to Primary Key but display a Description field

This MSAccess tutorial explains how to bind a combo box to a primary key (ie: autonumber field) but display a description field in the combo box results in Access 2003 (with screenshots and step-by-step instructions).

www.techonthenet.com

But, I suggest you get your structure right first.

 

[JemCain]

Hi plog,

Thanks for all the advice. I've done every you suggest and it works brilliantly.

ActivityTime is a text field that contains a time range (e.g. 10am to 2pm) and is only there so I can print it out on a report to give to parents.

Thanks again,

Jeremy.