Creating a Form and SubForm

[Jen]

Hello All

Please could I have some help with my database - I realise this is probably
very basic but I just can't get it to work!

I want to set up a database for our staff, and for each member of staff I
want to list various items.

I have created a table and form for STAFF, and another table and form for
ITEMS (each item will include several description fields).

I have included a STAFF field in the ITEMS table and related them. In the
STAFF form I have placed the subform ITEMS.

What I want to do is this: I want to type the STAFF name in once, and then
put the ITEMS data in the subform. However, when I come out of the form all
the ITEMS data is in the ITEMS table but without any names attached. I don't
know what I'm doing wrong!

I've obviously not quite got the hang of relating tables and forms. I have
tried using queries, but still can't get it to work.

When I get this sorted I would like to be able to create a report which
shows NAMES and then items listed, and another one with ITEMS and all the
names which correspond.

I apologise if this question is too basic for this group, but if it is I
would be very grateful if anyone could point me in the right direction to
getting the answer! I have looked on the internet, but can't find a site
that helps that I can actually understand.

Many thanks

 

[Ed Robichaud]

Good start, but you need to set the parent-child link between your main and
sub forms. You can do this through the properties box of the subform
control -set the link to "StaffID" on both, OR use the subform wizard to
recreate your subform and let it walk you through the process. Once there
are linked, only the related Items for each Staff record will show. You may
want to add a combobox locator control on the main form to help users find
and navigate to a particular staff record.
-Ed

 

[Joan Wild]

I assume that you have opened the relationships window (Tools,
Relationships) and created a relationship between the Staff field in the
Staff table and the Staff field in the Items table, and enforced referential
integrity.

If you have done this, the Access form wizard will do everything for you.
Start a form based based on Staff, adding the fields you want. Then before
clicking on Next, choose the Items table in the drop down and add the fields
you want. When you click next, the wizard will assume you want a
form/subform and make the link for you.

If you want to fix your current form, open it in design view, and select the
control that contains the subform. Click on View, Properties, and on the
Data tab, ensure that the Link Master and Link Child properties have Staff
in them.

 

[Al Campagna]

Jen,
Let's start with your tables design. tblStaff and tblItems
Staff is the ONE side of the realtionship, Items the MANY.
Using very abbreviated tables examples with sample values...
tblSTAFF
StaffID(autonum) FirstName Last Name
43 Bob Smith
22 Mary Jones
|
|
One to Many Relationship
|
V
tblITEMS
StaffID(Long) ItemID(autonum) Item
43 17 Desk
43 41 Bookshelf
22 31 Desk
43 12 Table
22 16 File Cabinet

This is not how they'll look on the form... this is the basic table design and the raw
data.
We can see that (tables related by StaffID) that Bob has a Desk, Bookshelf, and Table.

Use Relationships to set up a One to Many realtionship between tblStaff and tblItems
via the StaffID field.
(Show all in Staff and those that match in Items)
Select Enforce Referential Integrity.

The Main form (Single form) will be based on tblStaff, the Subform (Continuous) based
on tblItems.
The Main and Sub will be connected via the Subform's Parent/Child being set to StaffID.
StaffID on the Main relates to StaffID in the Subform.

If you were on BobSmith's record, and added an Item to his subform, that new Item
record would auto-magically have his StaffID entered, because of the realtionship we
established.

That's the basics... Hit the books, get that worked out, and return if you still have
specific problems.