Data Entry Form - Can't enter/save more than one record in a subform

[Mike Webb]

Using Access 2K2; experience level is Novice.

I am trying to design my first data-entry form. The main form is linked to
a table of Contacts (i.e., name, address, etc.) and the subform is linked to
the table of categories they are assigned to (i.e., donor, volunteer, US
Representative, National Audubon Society, Sent Christmas Card, etc.) Each
Contact may be assigned to one or more categories.

I just finished my subform design and tried a test by typing "test", "test1"
and "test2" in the entry blank, pressing Enter after each, but only "test"
showed up in the table when I looked. I checked my form properties and have
it set to Continuous Forms. I looked over every property for the form and
the field to see what else I should change, but found nothing. What am I
missing?

TIA,
Mike Webb
Platte River Whooping Crane Maintenance Trust, Inc.
a 501(c)(3) company

 

[TC]

Mike, it's really not possible to answer that question on the basis of the
information provided. For example, we'd need to know the primary key of each
of the two tables.

However: check the LinkMasterFields and LinkChildFields properties of the
main form's subform control. Those properties tell Access how to link the
subform records, to the main form one. If you haven't set those properties
correctly, the linkage will not work properly - when records are displayed,
or when they are saved. There are descriptions of those properties in online
help.

HTH,
TC

 

[Mike Webb]

Sorry I didn't include enough information.
My tables are as follows:
tblAddressList, ID is the PK; this table has all our contacts in it
tblAddressListCategories, SubCategory_Id is the PK; this table contains all
the categories a contact can be locally assigned, such as "donor" -- each
contact may have more than one category they are assigned.
tblJoinContactsAndCategories, Table_ID is the PK and the other PK's are in
it as FK's -- note: ID from the first table is called ContactAddress_ID in
this one.

The Form and subform were created using the Access Wizard. I included all 3
tables with tblAddressList being the main form. I hid all fields on the
main form except Subcategory_ID, SubCategory, and Description (all from
tblAddressListCategories).

One-to-Many relationships from the first to tables (the One part) to the
last table (the Many part)

The subform properties from the main form are:
Link Child Fields -- ContactAddress_ID
Link Master Fields -- ID

The record source for the subform is:
SELECT tblJoinContactsAndCategories.Table_ID,
tblJoinContactsAndCategories.ContactAddress_ID,
tblAddressListCategories.SubCategory_ID,
tblAddressListCategories.SubCategory, tblAddressListCategories.Description,
tblAddressListCategories.ID
FROM tblAddressListCategories
LEFT JOIN tblJoinContactsAndCategories ON
tblAddressListCategories.SubCategory_ID=tblJoinContactsAndCategories.SubCate
gory_ID;

Does this help?

Cordially,
Mike

 

[TC]

Hi Mike. See my replies below. Some of them relate to your table design &
naming choices. Those suggestions are probably not the cause of your current
problem, but you might consider them anyway.

Sorry I didn't include enough information.
My tables are as follows:

tblAddressList, ID is the PK; this table has all our contacts in it

Seems fair. ID is fairly vague as a fieldname. Maybe call it Address_ID, or
whatever.

tblAddressListCategories, SubCategory_Id is the PK; this table contains all
the categories a contact can be locally assigned, such as "donor" -- each
contact may have more than one category they are assigned.

Seems fair. Would SubCategory_Id be better named, CategoryId? Is this a
table of categories, or SUBcategories?

tblJoinContactsAndCategories, Table_ID is the PK and the other PK's are in
it as FK's -- note: ID from the first table is called ContactAddress_ID in
this one.

You clearly understand the concept of junction tables (with one small
proviso, noted below). However:

(1) I strongly recommend that you give the same data item, the same name in
every table where it it used. Do not call it ID in one table, &
ContactAddress_ID in another. You'll get increasingly confused, as your
database design gets more complex. It is waaaay easier to write new code
"off the top of your head", if the same field has the same name in every
relevant table. Otherwise, you're continually saying, "wtf did I call it in

that< table?"

(2) Generally the PK of a "joining" table, is the so-called "composite"
(multi-field) PK comprising the two PKs of the joined tables. That is,
select the two foreign keys simulateously, then hit the PK button. You do
not need a seperate PK field for the joining table, except in certain cases
which I suspect do not apply here. So, in summary, the joining table has a
composite PK comprising the PKs of the two other tables.

(3) Make sure you have defined a 1:M enforced relationship from
tblAddressList to tblJoinContactsAndCategories, and anther one from
tblAddressListCategories to tblJoinContactsAndCategories.

The Form and subform were created using the Access Wizard. I included all 3
tables with tblAddressList being the main form. I hid all fields on the
main form except Subcategory_ID, SubCategory, and Description (all from
tblAddressListCategories).

One-to-Many relationships from the first to tables (the One part) to the
last table (the Many part)


The subform properties from the main form are:
Link Child Fields -- ContactAddress_ID
Link Master Fields -- ID

That does look correct to me.

The record source for the subform is:
SELECT tblJoinContactsAndCategories.Table_ID,
tblJoinContactsAndCategories.ContactAddress_ID,
tblAddressListCategories.SubCategory_ID,
tblAddressListCategories.SubCategory, tblAddressListCategories.Description,
tblAddressListCategories.ID
FROM tblAddressListCategories
LEFT JOIN tblJoinContactsAndCategories ON
tblAddressListCategories.SubCategory_ID=tblJoinContactsAndCategories.SubCate
gory_ID;

I must say, that looks ok also (but I don't have Access here to check it for
real). The main form is viewing an Address, whose PK is ID. The subform is
viewing the corresponding Categories, for the Address whose PK is (here)
called ContactAddress_ID. Those names correspond to your subform linkage
settings.

So, 3 suggestions.

(1) Rename any fields with different names in different tables. Each field
should have the same name in each table where it appears.

(2) Redefine the PK of the joining table.

(3) Check you have the two defined relationshis.

See if that helps.

TC