Form and SubForm Problem

[iamnu]

I have a table tblPhones with a PDkey as the Primary Key.

I have another table tblPB with four fields as follows:
DDID as the Primary Key
PDkey, a lookup field to tblPhones.
DirectoryID, a lookup field to a table tblDirectory
DeptID, a lookup field to a table tblDepartment

The Main Form uses tblPhones as it's record source
The Sub Form has tblPB as it's record source

Link Child Fields = PDkey
Link Master Fields = PDkey

I can create a new record in tblPhones from the Main Form, WITHOUT
entering data in the SubForm.

How do I make sure data is entered into the SubForm when adding a new
record in the Main form?

Same Question (a different way):
How do I make sure tblPhones is not updated until data is entered in
tblPB?

 

[Ken Snell \(MVP\)]

You could use VBA code to save the main form's data and to move the cursor
into the subform as soon as the new record is created in the main form. Then
you could prevent the user from leaving the subform until data have been
entered into the subform.

Post back if you would like more details, and tell us more information about
the names of the form and the subform control, and of the controls in the
subform, etc.

 

[iamnu]

You could use VBA code to save the main form's data and to move the cursor
into the subform as soon as the new record is created in the main form. Then
you could prevent the user from leaving the subform until data have been
entered into the subform.

Post back if you would like more details, and tell us more information about
the names of the form and the subform control, and of the controls in the
subform, etc.

Yes Ken, more details please.

I have changed the name of many of the tables and fields, so now here
is the current information.

Main Form: PhoneBook, which has PBKey as the Primary Key
SubForm: PhoneBookSubForm which has the following Record Source:

SELECT tblPBlink.PBlinkKey, tblPBlink.PBkey, tblPBlink.DirectoryID,
tblPBlink.DeptID, zDepartments.DeptName
FROM zDepartments INNER JOIN tblPBlink ON zDepartments.DeptID =
tblPBlink.DeptID;

Link Child Field = PBkey
Link Master Field = PBkey

Everything works as I want, except that I am able to create a record
in the Main Form without a matching record in the SubForm. When I DO
create a record in the SubForm, everything is linked properly.

If you can show me how to save the main record, then force me to enter
data into the SubForm (I suppose when the PBkey in the SubForm is
null), I would be quite happy. Thanks for your help.

If you need additional information, I'll provide whatever I can.

Bernie

 

[Ken Snell \(MVP\)]

Yes Ken, more details please.

I have changed the name of many of the tables and fields, so now here
is the current information.

Main Form: PhoneBook, which has PBKey as the Primary Key
SubForm: PhoneBookSubForm which has the following Record Source:

SELECT tblPBlink.PBlinkKey, tblPBlink.PBkey, tblPBlink.DirectoryID,
tblPBlink.DeptID, zDepartments.DeptName
FROM zDepartments INNER JOIN tblPBlink ON zDepartments.DeptID =
tblPBlink.DeptID;

Link Child Field = PBkey
Link Master Field = PBkey

Everything works as I want, except that I am able to create a record
in the Main Form without a matching record in the SubForm. When I DO
create a record in the SubForm, everything is linked properly.

If you can show me how to save the main record, then force me to enter
data into the SubForm (I suppose when the PBkey in the SubForm is
null), I would be quite happy. Thanks for your help.

If you need additional information, I'll provide whatever I can.

Bernie

To ensure that the user does not "move away" from a just-created record in
the main form, turn off the Navigation Buttons in the main form. Put a
command button on the main form that the user will click to save the main
form's new record. Use code like this for that button's Click event:

Private Sub ButtonName_Click()
Me.Dirty = False
Me.NameOfSubformControl.SetFocus
Me.NameOfSubformControl.Form.NameOfControlOnSubform.SetFocus
End Sub

Then put this code in the Exit event of the subform control (the control
that actually holds the subform):

Private Sub NameOfSubformControl_Exit(Cancel As Integer)
If Me.NameOfSubformControl.Form.RecordsetClone.RecordCount = 0 Then
Cancel = True
MsgBox "You must enter data in the subform!", vbExclamation, _
"Enter Data!"
End If
End Sub