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.havelaar@it.canterbury.ac.nz> wrote in message
news:99261a755131a041b67071687bd032f2@news.teranew s.com...[color=blue]
>
> 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[/color]
the[color=blue]
> 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[/color]
SubTab.[color=blue]
> 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[/color]
one[color=blue]
> 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?[/color]
