Creating subform record with no main form one

[JohnB]

Hi. I will supply more info on this if needed but to ask
just a general question first. Is it normal for Access to
allow users to create subform records without them also
creating a main form record? I ask because I have many
main and subform set ups and recently a new user started
doing just that and was not stopped. This caused problems
opening other linked forms - the link could not identify
records correctly because they did not have the main forms
Key ID field entry, which only gets added to the subform
record when the main form record is created.

Obviously I will tell the users that its best to create
the main form record first, then the subform record but is
it normal that Access allows only subform records to be
created, or have I designed my db incorrectly?

Thanks, JohnB

 

[tina]

to require a record in the parent table before one can be created in the
child table, do the following:

in the child (subform) table's design view, set the foreign key field's
Required property to Yes. now the record can't be created in the child table
unless the foreign key field has a value in it.
in the database Relationships window, double click on the "line" linking the
two tables, to open the Edit Relationships window. put a checkmark in the
box next to Enforce Referential Integrity. now the value entered in the
child table's foreign key field must match a value in the parent (mainform)
table's primary key field.

in your form, the above changes will result in fairly ugly error messages
when a user attempts to add a subform record without a matching mainform
record. you can trap the errors with VBA code, but instead i usually prefer
to use VBA to lock the subform when there's no record in the mainform. you
can do this by opening the mainform in design view, and adding the following
code to the OnEnter event of the subform *control*, as

Private Sub SubformControlName_Enter()

Me!SubformControlName.Locked = IsNull(Me!MainFormPrimaryKeyField)

End Sub

the above "equals" expression should be all on one line, of course. if the
main form's primary key field is null, IsNull() = True, so Locked will be
changed to True and the user cannot enter a record in the subform. if there
is a value in the main form's primary key field, IsNull() = False, so Locked
will be changed to False and the user can now enter a record in the subform.

hth

 

[JohnB]

Thank you for such a clear and comprehensive reply. Just
one follow up - is your second piece of advice, the use of
VBA OnEnter code, an alternative to the first, setting the
foreign Key required to Yes etc? Or should I do both of
the things you suggest. Thanks again. Im sorry that I wont
be able to reply again until around 10.30 GMT tomorrow.
Cheers, JohnB

 

[tina]

well, personally, i would either do both, or skip setting the Required
property and just use the VBA code on the form. you do need to make sure you
enforce referential integrity on the table relationships, though.

you might want to go ahead and set the Required property. that way, if you
create a opportunity for data entry in the child table and forget to handle
the issue of ensuring a value is entered in the foreign key field - the
system will definitely remind you with an error message.

hth

 

[JohnB]

Hi Tina. I'll do both as you suggest. Many thanks for the
excellent help. Cheers, JohnB

 

[tina]

you're very welcome!

Hi Tina. I'll do both as you suggest. Many thanks for the
excellent help. Cheers, JohnB