By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,786 Members | 2,241 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,786 IT Pros & Developers. It's quick & easy.

Autonumber foreign keys and subforms

P: n/a
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.
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.

Nov 12 '05 #2

P: n/a
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" <e.********@it.canterbury.ac.nz> wrote in message
news:99******************************@news.teranew 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?

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.