Force new record if omitted

[Sheila D]

I have a form with several subforms. 2 of the subforms have a 1 to many
relationship. The 1 side has a field called TVAP_Number - an Autonumber - as
the Primary Key. The problem I am having is that users are going straight to
the many form and adding records then doing the 1 side. This allocates all
the many records to a null field - if they then go back and add the 1 side
that is not related to the many records at all. What is the best way to deal
with this? Thanks for any help

Sheila
ps I'm not great at VB so an easy option if there is 1 would be great

 

[Allen Browne]

1. Open the related table (the one the subform is bound to) in design view.

2. Select the foreign key field (the one that matches something in the main
form's table.)

3. In the lower pane, set the Required property to Yes.

This will prevent the user saving the subform record when the main form is
at a new record. But they don't get the error message until after they have
filled in the subform record (when it fails to save.) It might therefore be
a good idea to cancel the subform's BeforeInsert event as well.

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

 

[Sheila D]

Thanks for your response, I've copied the code and set the required
properties but am now getting the error message 'Ambiguous name detected:
Form_Before Insert'
Do I need to specify which is the parent in some way?
Sheila

 

[Allen Browne]

That would suggest that there was already a Form_BeforeInsert in the form's
module?

If you do have 2 of them, it would be an "ambiguous name."

 

[Sheila D]

Hi Allan

I don't have another Before Insert but wonder if it is because Access
considers I have more than 1 Parent (ha ha - of course I do!!) ? My main form
is Patients with a subform Procedures. Each patient can have many procedures
and other data is stored about each type of procedure so the Procedures form
also has subforms including the TVAP form which also has a 1 to many
relationship with TVAP Items. This is the 1 that is giving me the problem so
I put your code in TVAPItems. So is it possible that Parent could mean TVAP,
Procedure or Patient and if so can I overcome this......
Thanks for your help
Sheila

 

[Allen Browne]

Shouldn't be that.

Did you really have a space in Form_BeforeInsert?

 

[Sheila D]

No I didn't but by going back to check I realised that I had copied your code
in it's entirety and the Sub and End Sub were already automatically created
thus in there twice so that was the problem! Silly me, thanks very much, I'm
most grateful

Sheila