Creating subform record with no main form one

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

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" wrote in message
...
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

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
-----Original Message-----
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" wrote in
message
...
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


.

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" wrote in message
...
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
-----Original Message-----
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" wrote in
message
...
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


.

Hi Tina. I'll do both as you suggest. Many thanks for the
excellent help. Cheers, JohnB
-----Original Message-----
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" wrote in
message
...
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
-----Original Message-----
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" wrote in
message
...
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


.



.

you're very welcome!


"JohnB" wrote in message
...
Hi Tina. I'll do both as you suggest. Many thanks for the
excellent help. Cheers, JohnB
-----Original Message-----
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" wrote in
message
...
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
-----Original Message-----
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" wrote in
message
...
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


.



.