How does one protect against subform adding record before main form has record?

[Chips]

Using A2K, I have a main form [FrmContacts] with people's names.
I have a subform [FrmSbPhoneNo] that will make any number of phone numbers
for each name record.

What is a good way to make it so a person will be alerted (or prevented from
doing so) if they try to add a phone number to a new "name" record that
hasn't been created yet.

As in, they move the main form to "new record", and before they enter any
info in the new record, they go straight to the phone number subform.

This then creates an orphaned phone number. This is what I want to prevent.

Thanks,

GC

 

[Allen Browne]

Two things.

The most important is to open the subform's table in design view.
Select the foreign key field (the one that connects to the main form
record).
In the lower pane, set this field's Required property to Yes.
There is no way they can save a subform record with nothing in the main form
now.

However, the user does not get the error message until they have finished
entering the subform record, and it tries to save. To give the message as
soon as they *start* to enter a subform record, cancel its BeforeInsert
event procedure:

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Using A2K, I have a main form [FrmContacts] with people's names.
I have a subform [FrmSbPhoneNo] that will make any number of phone numbers
for each name record.

What is a good way to make it so a person will be alerted (or prevented from
doing so) if they try to add a phone number to a new "name" record that
hasn't been created yet.

As in, they move the main form to "new record", and before they enter any
info in the new record, they go straight to the phone number subform.

This then creates an orphaned phone number. This is what I want to

prevent.

 

[Chips]

Thank you very much.

If I just change the Required property, you get stuck and have to close the
form. Not elegant.

But with the code added, it's smooth.

GC

Two things.

The most important is to open the subform's table in design view.
Select the foreign key field (the one that connects to the main form
record).
In the lower pane, set this field's Required property to Yes.
There is no way they can save a subform record with nothing in the main form
now.

However, the user does not get the error message until they have finished
entering the subform record, and it tries to save. To give the message as
soon as they *start* to enter a subform record, cancel its BeforeInsert
event procedure:

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Using A2K, I have a main form [FrmContacts] with people's names.
I have a subform [FrmSbPhoneNo] that will make any number of phone numbers
for each name record.

What is a good way to make it so a person will be alerted (or prevented from
doing so) if they try to add a phone number to a new "name" record that
hasn't been created yet.

As in, they move the main form to "new record", and before they enter any
info in the new record, they go straight to the phone number subform.

This then creates an orphaned phone number. This is what I want to

prevent.

 

[Allen Browne]

Great.

BTW, you can also train your users to press <Esc> twice to undo an entry if
they need to do that.