Form with subform and record insert problems

[Mark Hollander]

Hi All,

I have a form called supplier whose default view is set to single view and
it has a subform whose default view is set to continuous

Main form's table is supplier
Sub form's table is suppliernotes

The sub form is linked via a single field ID -> Supplier ID.
On the main form I have all the fields that are in the database supplier
(Please see database structures below). The subform has all the fields that
is in it's database
When running the main form, the record that the main form is on is saved to
the database, and I add a record to the subform the autonumber field is
automatically filled in as expected but the ID from the main form does not
populate the supplierid field and I get the following error when I try to
move of the field or save the record
"Index or Primary Key cannot contain a Null value".

I was under the impression that the SupplierId field would have been filled
in automatically by access when a child record is entered because it is the
link between the 2 tables.

Any help and/or suggestions would be appreciated

MS Access 2007
[SUPPLIER TABLE STRUCTURE]
ID AUTONUMBER PRIMARY KEY
COMPANY TEXT
.... LOT OF OTHER FIELDS PERTAINING TO CONTACT INFORMATION AND ADDRESS

[SUPPLIERNOTES TABLE STRUCTURE]
SUPPLIERID NUMBER PRIMARY KEY
LINEID AUTONUMBER PRIMARY KEY
NOTES MEMO
ATTACHMENT ATTACHMENT
ENTRYDATE DATE/TIME

[RELATIONSHIPS]
SUPPLIER -> SUPPLIERNOTES (ONE TO MANY)
ID SUPPLIERID

ENFORCE REFERENCIAL INTEGRITY CHECKED
CASCADE UPDATE RELATED FIELDS CHECKED
CASCADE DELETE RELATED FIELDS UNCHECKED

 

[Mark Hollander]

Ok, I found the problem,

For those who have had a similar issue, check your record source of the form
to make sure that it is not an sql statement but points to the table name
instead

 

[BruceM]

The record source can be a SQL statement, a table, or a named query. That
was not the problem. I'm not sure how you fixed the problem by changing the
record source to a table.

If SupplierID in SUPPLIERNOTES is the linking field to SUPPLIER it cannot be
the primary key in SUPPLIERNOTES. If it is the primary key duplicates are
not allowed, so you could add only one child record.

Another thing is to be sure the Link Child and Link Master properties of the
subform control are set to the linking filed (SupplierID in this case). The
subform control is the "container" on the main form that holds the subform.
It is not the subform itself. Click the subform once to select the subform
control, then view its properties using View >> Properties or whatever
method you prefer.

Ok, I found the problem,

For those who have had a similar issue, check your record source of the
form
to make sure that it is not an sql statement but points to the table name
instead


--
Thank you
Mark Hollander

Hi All,

I have a form called supplier whose default view is set to single view
and
it has a subform whose default view is set to continuous

Main form's table is supplier
Sub form's table is suppliernotes

The sub form is linked via a single field ID -> Supplier ID.
On the main form I have all the fields that are in the database supplier
(Please see database structures below). The subform has all the fields
that
is in it's database
When running the main form, the record that the main form is on is saved
to
the database, and I add a record to the subform the autonumber field is
automatically filled in as expected but the ID from the main form does
not
populate the supplierid field and I get the following error when I try to
move of the field or save the record
"Index or Primary Key cannot contain a Null value".

I was under the impression that the SupplierId field would have been
filled
in automatically by access when a child record is entered because it is
the
link between the 2 tables.

Any help and/or suggestions would be appreciated

MS Access 2007
[SUPPLIER TABLE STRUCTURE]
ID AUTONUMBER PRIMARY KEY
COMPANY TEXT
... LOT OF OTHER FIELDS PERTAINING TO CONTACT INFORMATION AND ADDRESS

[SUPPLIERNOTES TABLE STRUCTURE]
SUPPLIERID NUMBER PRIMARY KEY
LINEID AUTONUMBER PRIMARY KEY
NOTES MEMO
ATTACHMENT ATTACHMENT
ENTRYDATE DATE/TIME

[RELATIONSHIPS]
SUPPLIER -> SUPPLIERNOTES (ONE TO MANY)
ID SUPPLIERID

ENFORCE REFERENCIAL INTEGRITY CHECKED
CASCADE UPDATE RELATED FIELDS CHECKED
CASCADE DELETE RELATED FIELDS UNCHECKED