keys and subforms

[Guest]

Hey everyone,

I have the following problem: I have a form with a primary key and a subform
linked on that primary key. I also have a default value for the Primary key
(=Date()) defined on the form.
The problem that I now have is if the user does not enter anything on the
parent form and skips straight to the subform, the parent record is not
saved, resulting in a "Index of PK cannot contain a null value" error. What
is the most elegant way of working around this?

Thanks in advance

Chris

 

[Allen Browne]

The first step is to cancel the subform's BeforeInsert event if the main
form is still at a new record:

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

However, that may not solve the problem fully. If the subform is based on a
query that includes the main form's table, then Access will (wrongly)
attempt to assign the Default Value to the main form's table when you
actually just want to create the record in the related table. To prevent
that, you need to either remove the main form's table from the subform's
query, or else remove the default value from the fields in the main table.
(You can set the Default Value of the text box on the main form instead.)

 

[Guest]

Hey, and thanks for the help. I trie dthat and it works, but I was curious if
instead there was a way to instead just save the default value, fill out the
subform, and then come back to the parent form. Something maybe along the
lines of

child_OnEnter()
if me.NewRecord then Key.Value = Key.DefaultValue
doCmd.RunCommand acCmdSaveRecord

Oh, btw, parent and child forms are based on two different tables without
any linkage except the primarty key/foreign key combination

Thanks a lot

Chris

 

[Allen Browne]

No. Access saves the main form record as soon as you move into the subform.