Autonumber foreign keys and subforms

Hi,

Hope someone can help. Here's the scenario:

I have two MSAccess tables MainTab and SubTab. MainTab has an autonumber
'id' column as primary key. Subtab has this id column as a foreign key.

There is a one-to-many relationship defined on MainTab and Subtab using the
id column. The join type is include all from MainTab and only rows with
matching value from SubTab. Referential integrity enforced; cascading
updates and deletes.

I have a form MainForm based on MainTab. It has a subform based on SubTab.
Linked child and master fields are 'id'.

Open the form.
(a) Put some data into MainForm, and its id gets set to 1, and the default
value for new rows in the subform displays as 1, as desired.
(b) I press the |* button to create a second new record in MainForm, but
don't put any data in it. The default value for id displays as
"(AutoNumber)". I go straight to the subform and enter some data. The id
field in the subform stays blank, presumably because the linked master row
doesn't exist. If I now go back to the MainForm and enter some data, the
new row in the subform disappears because its id value doesn't match the one
that now gets generated from the AutoNumber.

So my question is, is there any way to get insertion of a new row in the
subform to force generation of a matching row in the master MainForm, and
get the autonumber id from that MainForm back to the subform data?

- Ed.

I think that if you put

if Me.NewRecord then Me.Update

into the Current event of the main form, the autonumber will be
generated immediately after the new record is created. BUT, you can
create a lot of empty records in the main table that way.
Pavel

Ed Havelaar wrote:

Hi,

Hope someone can help. Here's the scenario:

I have two MSAccess tables MainTab and SubTab. MainTab has an autonumber
'id' column as primary key. Subtab has this id column as a foreign key.

There is a one-to-many relationship defined on MainTab and Subtab using the
id column. The join type is include all from MainTab and only rows with
matching value from SubTab. Referential integrity enforced; cascading
updates and deletes.

I have a form MainForm based on MainTab. It has a subform based on SubTab.
Linked child and master fields are 'id'.

Open the form.
(a) Put some data into MainForm, and its id gets set to 1, and the default
value for new rows in the subform displays as 1, as desired.
(b) I press the |* button to create a second new record in MainForm, but
don't put any data in it. The default value for id displays as
"(AutoNumber)". I go straight to the subform and enter some data. The id
field in the subform stays blank, presumably because the linked master row
doesn't exist. If I now go back to the MainForm and enter some data, the
new row in the subform disappears because its id value doesn't match the one
that now gets generated from the AutoNumber.

So my question is, is there any way to get insertion of a new row in the
subform to force generation of a matching row in the master MainForm, and
get the autonumber id from that MainForm back to the subform data?

- Ed.

Ed, this is a very important question.

As you found, Access permits a Null value in a foreign key, even if you have
Referential Integrity enforced. Occassionally that is very useful, but in
most tables you need to prevent that from happening.

The solution is very simple:
1. Open your related table (SubTab) in design view.
2. Select the foreign key field.
3. In the lower pane, set its Required property to Yes.
There is now no way that Access can save a record into this table if the
foreign key field is null.

At this point, the user gets the message that the record cannot be saved
after they have gone to the trouble of filling it out in the subform. To
give them the message when they begin filling in the subform record, cancel
the subform's BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.Parent.NewRecord Then
Cancel = True
MsgBox "Fill in the main form first."
End If
End Sub


This issue is one of six discussed in article:
Common Errors with Null
at:
http://allenbrowne.com/casu-12.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ed Havelaar" wrote in message
s.com...

Hope someone can help. Here's the scenario:

I have two MSAccess tables MainTab and SubTab. MainTab has an autonumber
'id' column as primary key. Subtab has this id column as a foreign key.

There is a one-to-many relationship defined on MainTab and Subtab using
the
id column. The join type is include all from MainTab and only rows with
matching value from SubTab. Referential integrity enforced; cascading
updates and deletes.

I have a form MainForm based on MainTab. It has a subform based on
SubTab.
Linked child and master fields are 'id'.

Open the form.
(a) Put some data into MainForm, and its id gets set to 1, and the default
value for new rows in the subform displays as 1, as desired.
(b) I press the |* button to create a second new record in MainForm, but
don't put any data in it. The default value for id displays as
"(AutoNumber)". I go straight to the subform and enter some data. The id
field in the subform stays blank, presumably because the linked master row
doesn't exist. If I now go back to the MainForm and enter some data, the
new row in the subform disappears because its id value doesn't match the
one
that now gets generated from the AutoNumber.

So my question is, is there any way to get insertion of a new row in the
subform to force generation of a matching row in the master MainForm, and
get the autonumber id from that MainForm back to the subform data?

Just to be clear - you're not using an Autonumber *AS A FOREIGN KEY*, are you? That will not work; the foreign key to an autonumber primary key must be a Long Integer (or a GUID, if it's a GUID autonumber).
--
John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public




"Ed Havelaar" wrote:

Hi,

I have two MSAccess tables MainTab and SubTab. MainTab has an autonumber
'id' column as primary key. Subtab has this id column as a foreign key.