Adding records to a table through a subform

[ceatana]

Hi.



I am working on an employee database for a union.

I have a main form for data entry. On that main form, I would like to have subforms in a tab control for viewing and entering data in other tables.

For example, the main form will have the employee's work information (location, department, etc.). In the tab control, one tab would be for personal information (home address, email, etc), one tab would have information on work terms (start dates and end dates), one tab would have their leave without pay terms, etc.

The work terms and leave without pay terms are in their own tables, but all 3 tables are linked by a member number from the main membership table.

Can I add records to the leave without pay terms table through the subform without having to re-enter the member number (which I would ideally like to have Autonumber)?

[pbaldy]

Presuming the master/child links are the member number, you shouldn't have to enter it on the subform. It will sync to the value on the main form.

[ceatana]

Excellent! Thanks.

Next question: If my main form has a lookup field to pull up a record, can I have that same lookup field update the subforms? How do I do that (I've been searching and can't find the answer anywhere)?

[pbaldy]

Not clear on what you mean. Normally only one field would relate the tables.

[ceatana]

That is correct. One field relates the tables. What I would like to have is, instead of forms for each table for data entry, I want to create 1 master form with a set of subforms in a tab control. Each subform would add records to different tables. In the main form portion, I will have a lookup field to pull up the record for a particular employee. For example, when I select Bugs Bunny from the Lookup field, it should populate the main form with his work information. I also want the subform for leave without pay (which is for a different table) to populate only the information for Bugs Bunny's leave without pay information (if he has any).

Is that clearer?

I want it to look something like this: So, each tab has a subform that links to a separate table.

[pbaldy]

The master/child links I mentioned should do that. They keep subforms in sync with the main form. If you navigate to a record on the main form, the subforms will move with it.

[ceatana]

Ok. Thanks!

[pbaldy]

No problem, post back if you get stuck.

[ceatana]

New question:

One of my subforms adds records to a Dues table (recording how much dues was paid each month). Each record in this table is for a year's worth of dues for a member (so the fields are MemNum, Year, Jan, Feb, Mar, Apr, May, Jun, etc.). I would like the subform to show the latest record (current year) for the member when I pull up their information. How do I do that?

[pbaldy]

For starters, you normally wouldn't have fields for month like that. Effective dates might be a better alternative. You can base the form on a query that sorts the records in the desired order.

[ceatana]

Dues are tracked by the month for which they are received. The field is a currency field where I indicate the amount that was received for that particular month. I don't see how an effective date would be a better alternative. I just need a field where I can input the amount of dues received for that particular month.

[pbaldy]

What happens when you want to sum up the dues paid by a person or persons? With your design, you have to add 12 fields together. The concept is normalization:

Fundamentals of Relational Database Design -- r937.com

[ceatana]

The only time I need to sum up dues in Access, it's a sum of a particular month's dues for all members, not for an entire year.