forcing creation of parent before saving child

  • Thread starter Thread starter Stuart McGraw
  • Start date Start date
S

Stuart McGraw

I haven't been able to figure this out and would
appreciate some help...

I have two tables, both with autonumber primary
keys, and linked in a conventional master-child
relationship. I've created forms for both those
tables, and inserted the child table form into the
master table form as a subform. It works just as
it is supposed to, in that I can create a new master
record, and then add detail records.

But... all the columns in the master table have
default values so I don't really have to enter
anything in the master table. (It's only real purpose
is to provide groups of detail records.) But, of course,
it I go to a new record, then try to enter a detail
record, without having created a master
record first, Access complains about no value
in the child table field that links to the master
table. What I want to do, is to be able to go
to the subform table, create a record, and have
Access create the necessary master record (if
none exists already) before trying to save the
detail record.

In the Before_update procedure in the child form
I tried modifying the master record (Me.parent!-
[somefield] = <innocuous-value>, causing it
to be created. Then I try to save it be doing
Me.parent.dirty = false. This causes a 2115 error
from Access ("The macro or function set to the
BeforeUpdate [..] property is preventing Access
from saving the data...") I tried many different
permutations of this without finding the right
way... (Including adding the master record with
dao -- but the form doesn't know anything about
it so doesn't display it. Also tried adding
the record with dao, on the form's recordset but
get an error from that.)

Is there some way I can force the creation of a
parent record (if none exists) before saving the
child record?

I hope this is understandable -- if not please tell
me and I'll try to clarify...
 
At some point before the user enters the subform to begin entering data, the
master form must be made "dirty" so that Access knows it needs to be saved.
You can do this using Me.Dirty = True on the master form.
 
At some point before the user enters the subform to begin entering
data, the master form must be made "dirty" so that Access knows it
needs to be saved. You can do this using Me.Dirty = True on the
master form.
Click to expand...

This is one reason I don't like to use continuous or datasheet
subforms for data entry, especially not for creating records.

If I did do that, however, I would simply disable the ability to
create a new record until there was a parent record already there.
If a parent record has nothing in it other than its PK that is
required data, then I question whether or not the schema might need
some work. That is, even if the parent record *can* be created with
nothing but a PK, the user still must go back and fill in some data
in order for that record to have any use whatsoever. So, you've now
added a condition to the end of the creation of the *child* record,
and raised the problem of how you force the user to go back and fix
the parent record.

This is why I tend to use small unbound dialog forms to collect the
required fields for creating main records, so I don't get mixed up
with issues like this in parent/child form layouts.
 
in message:
Is there some way I can force the creation of a
parent record (if none exists) before saving the
child record?

I hope this is understandable -- if not please tell
me and I'll try to clarify...
Click to expand...

Here is a really good past post by MVP Allen Browne on this very
subject which should help I believe:
The basic idea is that you use a relational design.
Where the primary table has one record, the related table can have many.

The main form is bound to the primary table.
The subform is bound to the related table.

You enter a main form record first. When you enter the subform, the main
form saves to the primary table if it has not already done so.

When you enter something in the subform, it saves to the related table. If
the main form is at a new record, you need to prevent a subform record from
saving. To do that:
1. Open the related table in design view.
2. Select the foreign key field.
3. In the lower pane, set its Required property to Yes.
Now the subform cannot save a record unless the foreign key has inherited a
value from the main form. Since this happens at the end of the subform
record's entry, it is also helpful to cancel the subform's BeforeInsert
event if the main form is at a new record, so the user gets the message as
soon as they start to enter a subform 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

Use the BeforeUpdate event of each form for record-level validation. Do not
try to force the record to save in this event: the event fires because the
record is in the process of being saved. Do not shift focus to the
parent/child in this event: allow it to finish the save. Then use the form's
AfterUpdate event to switch if you have a good reason for doing so.

Note that the controls also have BeforeUpdate and AfterUpdate events, but we
are talking about the events of the Form here.

In summary, use Form_BeforeUpdate for record-level validation, and allow the
event to complete before trying to do something else.

HTH
[/QUOTE][/QUOTE][/QUOTE]

Hope that helps to give you some ideas.
 
One possibility is to add a button to the main form to save the current
record. This button would be controlled from the form's current event. If
the form is on a new record, the button would be enabled. If the form is on
an existing record the button could be hidden. This code should also
control the subform. Hide it if the record is new and unhide it in the save
button. So the code would be something like:
Current event:
If Me.NewRecord Then
Me.YourButton.Visible = True
Me.YourSubform.Visible = False
else
Me.YourButton.Visible = False
Me.YourSubform.Visible = True
End If

Button's Click event:
Me.Dirty = True
DoCmd.RunCommand acCmdSaveRecord
Me.YourButton.Visible = False
Me.YourSubform.Visible = True
 
Thanks for the reference to Allen Browne's post. He describes
how to force the use to create the parent record first, if the user
inadvertently tries to create a child record first.

But I want just the opposite -- I want to let the user create the
child record first, and have Access say, "hmm, there is no parent
record yet, so I will just create one for him/her"

Jeff Conrad said:
in message:


Here is a really good past post by MVP Allen Browne on this very
subject which should help I believe:

The basic idea is that you use a relational design.
Where the primary table has one record, the related table can have many.

The main form is bound to the primary table.
The subform is bound to the related table.

You enter a main form record first. When you enter the subform, the main
form saves to the primary table if it has not already done so.

When you enter something in the subform, it saves to the related table. If
the main form is at a new record, you need to prevent a subform record from
saving. To do that:
1. Open the related table in design view.
2. Select the foreign key field.
3. In the lower pane, set its Required property to Yes.
Now the subform cannot save a record unless the foreign key has inherited a
value from the main form. Since this happens at the end of the subform
record's entry, it is also helpful to cancel the subform's BeforeInsert
event if the main form is at a new record, so the user gets the message as
soon as they start to enter a subform 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

Use the BeforeUpdate event of each form for record-level validation. Do not
try to force the record to save in this event: the event fires because the
record is in the process of being saved. Do not shift focus to the
parent/child in this event: allow it to finish the save. Then use the form's
AfterUpdate event to switch if you have a good reason for doing so.

Note that the controls also have BeforeUpdate and AfterUpdate events, but we
are talking about the events of the Form here.

In summary, use Form_BeforeUpdate for record-level validation, and allow the
event to complete before trying to do something else.

HTH
Click to expand...
[/QUOTE]

Hope that helps to give you some ideas.[/QUOTE]
 
Well, I was hoping I could find a way of keeping the simplicity of
bound forms AND improve the user experience by not imposing
an entry order (that exists primarily for Access' benefit) on the
controls.
Given a form and a subform, why shouldn't I (as a user) be able
fill out the subform, then the main form? Why should the opposite
order be imposed on me? In a case where I have to fill out some
of the the parent form controls anyway, this is not big deal. In the
case where all the parent form controls have default values, it is
annoying to have to fill one out anyway (or click a new record button
or something) just to start entering the detail data. I would like
Access to automatically create the parent record if it doesn't exist
when a detail record is about to be created.

So, yes, using unbound forms (or maybe a form bound to a temp
table) would work, but I hoped i could keep things simpler.

I have found a quasi-working method. In the subform's Before_Update
event I create the parent record "behind Access' back" with DAO, and
set a flag. Then in the After_Update event, if the flag is set, I save the
current position, do a requery, and return to the saved position. A little
ugly (among other things requires the child table's fk field to allow NULLs
I think), and a little visually disturbing, so I would still like to find a better
or simpler solution.

I have used Access for many years but intermittently enough
so that I am not sure of the various plusses and minuses of all
the various design aproaches.

But so far, I find I really like datasheet views. I like being able to
see the data I'm interested in, in context. I like being able to resize,
reorder, and hide columns. I like being able to sort by a column.
They can sometimes also simplify data entry when you can copy and
paste from another row without needing to page back to find the row
with the data you want to copy. But as you point out they come with
many restrictions and problems.
 
Yes, this makes it easier for the user to create a new record, but
why (from a user interface point of view) force a user to take a purely
mechanical action to create a new record? Why shouldn't Access
do it for the user when neccesssary. (That question is retorical --
I guess the answer is because it is difficult to get Access to do this.)
I suppose an easy fix would be to create a new parent record every
time the use goes to a new record, but that would leave a lot of
parent records with no children that would have to be cleaned up.
Would be better to create the parent record just before a child record
was about to be saved, but this seems to be difficult to do (in the sense
that trying the obvious things in the child form's Before_Update
event don't work -- I was hoping I'd overlooked something.)

Pat Hartman said:
One possibility is to add a button to the main form to save the current
record. This button would be controlled from the form's current event. If
the form is on a new record, the button would be enabled. If the form is on
an existing record the button could be hidden. This code should also
control the subform. Hide it if the record is new and unhide it in the save
button. So the code would be something like:
Current event:
If Me.NewRecord Then
Me.YourButton.Visible = True
Me.YourSubform.Visible = False
else
Me.YourButton.Visible = False
Me.YourSubform.Visible = True
End If

Button's Click event:
Me.Dirty = True
DoCmd.RunCommand acCmdSaveRecord
Me.YourButton.Visible = False
Me.YourSubform.Visible = True

Stuart McGraw said:
I haven't been able to figure this out and would
appreciate some help...

I have two tables, both with autonumber primary
keys, and linked in a conventional master-child
relationship. I've created forms for both those
tables, and inserted the child table form into the
master table form as a subform. It works just as
it is supposed to, in that I can create a new master
record, and then add detail records.

But... all the columns in the master table have
default values so I don't really have to enter
anything in the master table. (It's only real purpose
is to provide groups of detail records.) But, of course,
it I go to a new record, then try to enter a detail
record, without having created a master
record first, Access complains about no value
in the child table field that links to the master
table. What I want to do, is to be able to go
to the subform table, create a record, and have
Access create the necessary master record (if
none exists already) before trying to save the
detail record.

In the Before_update procedure in the child form
I tried modifying the master record (Me.parent!-
[somefield] = <innocuous-value>, causing it
to be created. Then I try to save it be doing
Me.parent.dirty = false. This causes a 2115 error
from Access ("The macro or function set to the
BeforeUpdate [..] property is preventing Access
from saving the data...") I tried many different
permutations of this without finding the right
way... (Including adding the master record with
dao -- but the form doesn't know anything about
it so doesn't display it. Also tried adding
the record with dao, on the form's recordset but
get an error from that.)

Is there some way I can force the creation of a
parent record (if none exists) before saving the
child record?

I hope this is understandable -- if not please tell
me and I'll try to clarify...
Click to expand...
Click to expand...
 
[...] At some point before the user enters the subform [...]
This is my problem. It requires precognition for Access to know
when the user is going to enter the subform. I can only react after
he/she has done so (or started to do so).

I have tried to change the parent's dirty flag in the various subform
gotFocus, beforeUpdate beforeInsert, and similar events procedures,
as well as in the Enter event of the subform control in the parent form,
but I always get an error 7768 "In order to change data through this
form, the focus must be in a bound field that can be modified."

And even if I could create the parent record when entering the subform
that is pretty undesireable -- I want to wait until the last minute (just
before a child record is about to be written) to avoid creating parents
with no children when the user changes his/her mind.


Steve Jorgensen said:
At some point before the user enters the subform to begin entering data, the
master form must be made "dirty" so that Access knows it needs to be saved.
You can do this using Me.Dirty = True on the master form.

I haven't been able to figure this out and would
appreciate some help...

I have two tables, both with autonumber primary
keys, and linked in a conventional master-child
relationship. I've created forms for both those
tables, and inserted the child table form into the
master table form as a subform. It works just as
it is supposed to, in that I can create a new master
record, and then add detail records.

But... all the columns in the master table have
default values so I don't really have to enter
anything in the master table. (It's only real purpose
is to provide groups of detail records.) But, of course,
it I go to a new record, then try to enter a detail
record, without having created a master
record first, Access complains about no value
in the child table field that links to the master
table. What I want to do, is to be able to go
to the subform table, create a record, and have
Access create the necessary master record (if
none exists already) before trying to save the
detail record.

In the Before_update procedure in the child form
I tried modifying the master record (Me.parent!-
[somefield] = <innocuous-value>, causing it
to be created. Then I try to save it be doing
Me.parent.dirty = false. This causes a 2115 error
from Access ("The macro or function set to the
BeforeUpdate [..] property is preventing Access
from saving the data...") I tried many different
permutations of this without finding the right
way... (Including adding the master record with
dao -- but the form doesn't know anything about
it so doesn't display it. Also tried adding
the record with dao, on the form's recordset but
get an error from that.)

Is there some way I can force the creation of a
parent record (if none exists) before saving the
child record?

I hope this is understandable -- if not please tell
me and I'll try to clarify...
Click to expand...
Click to expand...
 
Stuart said:
[...] At some point before the user enters the subform [...]
Click to expand...
This is my problem. It requires precognition for Access to know
when the user is going to enter the subform. I can only react after
he/she has done so (or started to do so).
Click to expand...

I just disable or hide the subform on new parent records until the
BeforeInsert event of the parent. Works just fine.
 
Stuart said:
Given a form and a subform, why shouldn't I (as a user) be able
fill out the subform, then the main form? Why should the opposite
order be imposed on me?
Click to expand...

Stuart,

You are discribing the database equivalent of a virgin birth. By saying
that the user could enter the Child record first you imply that the
Child records can exist without the Parent record having any meaningfull
data in it. This violates the whole meaning of a Relational Database.
You should rethink your data structure so that it is relational, then
you will find that your users will not be tempted to put the cart before
the horse.

I'm not trying to be hostile here, just that I've found in the past that
if the order of entry isn't immediately obvious from the layout of the
data then it is usually the fault of the data structures and not Access
or the Forms that deal with the data.
 
So, yes, using unbound forms (or maybe a form bound to a temp
table) would work, but I hoped i could keep things simpler.
Click to expand...

I never suggested unbound forms or temp tables.

And I see nothing wrong with enforcing an order of operations,
because there is real-world logic to it.
 
Bri said:
By saying
that the user could enter the Child record first you imply that the
Child records can exist without the Parent record having any meaningfull
data in it.
Click to expand...

No, I am only saying that if the application has the information it
needs to create a parent record, it should do so, without requiring
a user to manually do it. (Although below I argue for something more
general.)
This violates the whole meaning of a Relational Database.
You should rethink your data structure so that it is relational, then
you will find that your users will not be tempted to put the cart before
the horse.
I'm not trying to be hostile here, just that I've found in the past that
if the order of entry isn't immediately obvious from the layout of the
data then it is usually the fault of the data structures and not Access
or the Forms that deal with the data.
Click to expand...

The form in this case is for editing financial transactions. A transaction
is a date, userid, some status flags, and a set of transaction components
that are each an account id, an amount, and a couple other things.
When creating a new transaction, all the transaction fields will use
default values 99% of the time. This seems to me to be a natural and
accurate way to model the transactions (and the way that most similar
apps do it) so I am not sure how to rethink it.

As to making the order of entry obvious, I think the order of entry
requirement is not a requirement of the relational design. The only
requirement the relational design imposes is that after the user has
entered the data, the relational constrains are satisfied. The order
of entry is an application (Access) imposed requirement.

I am talking only about a user interface issue. You know and I know
there is a relational database behind the form that imposes certain
requirements. Depending on the UI design, the user may, or may not,
need to be aware (on some level) of the requirements imposed by the
database. I do not want to require a user to understand the relational
model behind the form, if it is not necessary.

As a user I see a form with some fields in the top part (the
parent form) and some multiple sets of fields in the bottom part (the
subform). (I don't think it matters if the subform is a datasheet, continuous
view or single form.) My job (as user) is to fill in the neccessary data
and tell the app when I am done. If the values in the top part of the
form already have the (default) values I want, then I will naturally go to
the subform and start entering the data I need to there. Yes, I could
change the forms to "force" the user to do things in the order Access
wants. But if I can avoid that, that is better, yes?

If I have to click something, or do something, in the parent form simply
to make the computer happy, then that is bad UI design (IMO). (Not
horrible, just not quite as good as it should be.)

I also think it would better, from a UI point of view, if the user could also
enter child data before parent data, even in the case when there was
non-defaultable data in the parent form, but that requires more extra
work (at least in Access) than I am willing to do.
 
Stuart,

Comments in-line.

Stuart said:
No, I am only saying that if the application has the information it
needs to create a parent record, it should do so, without requiring
a user to manually do it. (Although below I argue for something more
general.)
Click to expand...

So, for the case of the Parent record having default data that is
sufficient you could create that record on the OnEnter Event of the
subForm control (this control contains the subForm, it is not the
subForm, so the event is in the MainForm and fires before the subForm
has focus).
The form in this case is for editing financial transactions. A transaction
is a date, userid, some status flags, and a set of transaction components
that are each an account id, an amount, and a couple other things.
When creating a new transaction, all the transaction fields will use
default values 99% of the time. This seems to me to be a natural and
accurate way to model the transactions (and the way that most similar
apps do it) so I am not sure how to rethink it.
Click to expand...

I see what you mean now.
As to making the order of entry obvious, I think the order of entry
requirement is not a requirement of the relational design. The only
requirement the relational design imposes is that after the user has
entered the data, the relational constrains are satisfied. The order
of entry is an application (Access) imposed requirement.
Click to expand...

It isn't Access that is making this requirement it is the relational
design. If you have Referential Integrity, then the Child cannot exist
before the Parent (this is the Order I refer to, not the fields). That
is the relational constraint.
I am talking only about a user interface issue. You know and I know
there is a relational database behind the form that imposes certain
requirements. Depending on the UI design, the user may, or may not,
need to be aware (on some level) of the requirements imposed by the
database. I do not want to require a user to understand the relational
model behind the form, if it is not necessary.
Agreed.

As a user I see a form with some fields in the top part (the
parent form) and some multiple sets of fields in the bottom part (the
subform). (I don't think it matters if the subform is a datasheet, continuous
view or single form.) My job (as user) is to fill in the neccessary data
and tell the app when I am done. If the values in the top part of the
form already have the (default) values I want, then I will naturally go to
the subform and start entering the data I need to there. Yes, I could
change the forms to "force" the user to do things in the order Access
wants. But if I can avoid that, that is better, yes?
Click to expand...

Unless you create the Parent record for the user (as I described above)
and you want to treat this as a Batch, then you could bind the forms to
temp tables that do not have a relationship between them and then move
the data into the main tables when the user 'tells the app that they are
done'.

So you have two choices (as I see it); save the Parent record for the
user prior to them entering a Child record or they fill out data into
temp tables that are then written as a batch to the main tables (writing
the Parent record first of course). I would go with the first one myself.
If I have to click something, or do something, in the parent form simply
to make the computer happy, then that is bad UI design (IMO). (Not
horrible, just not quite as good as it should be.)
Click to expand...

If the thing they click (or tab to) is the subForm then they don't do
anything extra as they were going there anyway.
I also think it would better, from a UI point of view, if the user could also
enter child data before parent data, even in the case when there was
non-defaultable data in the parent form, but that requires more extra
work (at least in Access) than I am willing to do.
Click to expand...

This is the scenario where I thought you were in. This is where you
should rethink things if you think that the Parent record doesn't need
any data in it. Again, this is the relational design and not Access that
is imposing this restriction. Or if you mean that the user fills in
Child records and then fills in the Parent record (so it does eventually
have meaningful data in it) then you would pick the second option (temp
tables).
 
Back
Top