form/subform Before Update events

[mscertified]

I have a main form containing a subform.
All of the data used to be on the main form but because some of the data was
separated out into a separate table, it is now in the subform.
The user used to be able to tab thru the entire form and also hit escape to
undo everything - now these things seem problematical.
Do I understand correctly that before the user can click in the subform, the
main forms 'before update' event must finish. And also before the user can
exit from the subdform back to the main form, the subforms 'before update'
event must finish?
I guess this means that hitting escaope to undo everything becomes much more
complex?

 

[Albert D. Kallal]

Your evaluation of the situation these very much the spot on.

For the most part the parent record has to be written to disk before you can
start editing into adding child records. That is pretty much how most
relational databases work anyway (the parent record MUST be written to
disk).

What this means is thus your ESC, or "undo" from the menu option is going to
be limited to the CURRENT record you are editing. In the case of a sub form,
your undo (esc key) is thus only going to be able to undo the current record
in the sub form that you actually working on.

About the only way around this limitation is to perhaps pull both the main
record, and the child the records into a temporary table(s), and bind the
form(s) to that. This approach can becomes quite a difficult thing to
manage, because in many cases you'll have to keep track of which of the
child tables actually got updated, did you add records to that child form,
or simply edit some child records, or even just delete a few of the child
records. (your code when you write all this mess back to the main database
will have to manage ALL of these scenarios. If you delete a record in a
sub-form, your code would then have to figure out that the child record is
not supposed to exist in the main table anymore..

I suppose it would be really really nice if we could simply wrap the whole
form into a transaction of some type, but it's still quite messy to do so.
(especially if you allow navigation in the forms).

I would be really interested to how other database products in the
marketplace handle this interface problem. (or how developers approach this
problem), but it not usually very clean at all.

I have to say that this un-do problem is simply a limitation of the bound
forms approach that ms-access has. The upside of bound forms is you can
build a one to many interface with forms and not have to write one line of
code....

 

[Marshall Barton]

I have a main form containing a subform.
All of the data used to be on the main form but because some of the data was
separated out into a separate table, it is now in the subform.
The user used to be able to tab thru the entire form and also hit escape to
undo everything - now these things seem problematical.
Do I understand correctly that before the user can click in the subform, the
main forms 'before update' event must finish. And also before the user can
exit from the subdform back to the main form, the subforms 'before update'
event must finish?
I guess this means that hitting escaope to undo everything becomes much more
complex?

You are correct about the before update events. And you are
right about an Undo operation for both forms being very
complex.

Essentially, the main form would have to be unbound. Of
course, that in turn means that you would have to code a
main form record save button and any other benefits (record
navigation, OldValue, Undo, etc) that you get automatically
with a bound form. Most of these operations can be done by
binding the form to a work table and having your undo
reintializing that tables record. The save operation would
just be a query to update the real table from the work
table.

 

[mscertified]

Thanks for the response.
In my situation I have a many to one relationship rather than a one to many.
Therefore there can only be one record in the subform. When no subform record
exists, the area appears as all gray with no controls evident. In order to
add a record, I have a 'new record' button which inserts a dummy record (with
all fields blank) to the related table and places its primary key in the
hidden field on the main form. This 'opens up' the subform for data entry.
The problem I am having is that the subform area is midway down the form and
previously the validations on the main form were always done in sequence top
to bottom. Now when the user clicks in the subform, the validations are done
for the controls in the main form underneath the subform. This will probably
be confusing to my users as it is not the way it worked before.
Now it appears that whenever the subform is used, the before update event
for the main form will be executed twice, once upon entering the subform and
once upon moving to a new record.
I wonder if there is any way to detect that the before update event was
triggered by entering the subform and thus postpone some validations until
the before update event is execeuted for the second time.

-Dorian

 

[Marshall Barton]

Thanks for the response.
In my situation I have a many to one relationship rather than a one to many.
Therefore there can only be one record in the subform. When no subform record
exists, the area appears as all gray with no controls evident. In order to
add a record, I have a 'new record' button which inserts a dummy record (with
all fields blank) to the related table and places its primary key in the
hidden field on the main form. This 'opens up' the subform for data entry.
The problem I am having is that the subform area is midway down the form and
previously the validations on the main form were always done in sequence top
to bottom. Now when the user clicks in the subform, the validations are done
for the controls in the main form underneath the subform. This will probably
be confusing to my users as it is not the way it worked before.
Now it appears that whenever the subform is used, the before update event
for the main form will be executed twice, once upon entering the subform and
once upon moving to a new record.
I wonder if there is any way to detect that the before update event was
triggered by entering the subform and thus postpone some validations until
the before update event is execeuted for the second time.

All this is because you have the parent (one side) record in
the subform and the child (many side) records in the main
form. The entire problem would go away if you did it the
other way around.

 

[mscertified]

Thanks for the response.
Yes, that might solve the problem but it is not the application I am writing.
This is a Ticket Writer application and this screen is maintaing a ticket.
The subform represents the Client.
There is always one client for a ticket but a client may have many tickets
associated with them.
It would not make sense to have a list of Clients and then all the tickets
for them in a subform. The purpose of this application is to create and
maintain tickets not to create and maintain clients.
I am, however, open to all suggestions on how to arrange this better. This
is my third attempt at coding this having given up on the unbound controls
approach and the approach with a joined table for the record source. Neither
worked or was practical.

 

[Marshall Barton]

The point is that both you and Access need to identify the
client **before** you can allocate a ticket to them.

How your form looks is up to you. It certainly is not
necessary to display a list of clients, but it does sound
like a convenient way to indentfy a particular client.

I suspect that Albert can explain this more clearly than I
have and I hope he chimes in with his thoughts.

 

[mscertified]

I see your point but that is a fundamentally different way of working.
The form is bound to the Ticket table so whether the user presses the New
Ticket button or uses Access built-in navigation, the user is positioned on a
new Ticket record. Only after that is done, do they enter the ticket
information and enter or select the client.
I'm trying to keep the processing as close to previously as possible. The
only difference is that now the client data is in a related table rather than
in each ticket.
You are saying that once a new ticket is requested they must either select
an existing client or enter a new client before I position to the new ticket
record. I'm not sure how I could change the application to do that.

The point is that both you and Access need to identify the
client **before** you can allocate a ticket to them.

How your form looks is up to you. It certainly is not
necessary to display a list of clients, but it does sound
like a convenient way to indentfy a particular client.

I suspect that Albert can explain this more clearly than I
have and I hope he chimes in with his thoughts.
--
Marsh
MVP [MS Access]

Thanks for the response.
Yes, that might solve the problem but it is not the application I am writing.
This is a Ticket Writer application and this screen is maintaing a ticket.
The subform represents the Client.
There is always one client for a ticket but a client may have many tickets
associated with them.
It would not make sense to have a list of Clients and then all the tickets
for them in a subform. The purpose of this application is to create and
maintain tickets not to create and maintain clients.
I am, however, open to all suggestions on how to arrange this better. This
is my third attempt at coding this having given up on the unbound controls
approach and the approach with a joined table for the record source. Neither
worked or was practical.

 

[John W. Vinson]

I see your point but that is a fundamentally different way of working.
The form is bound to the Ticket table so whether the user presses the New
Ticket button or uses Access built-in navigation, the user is positioned on a
new Ticket record. Only after that is done, do they enter the ticket
information and enter or select the client.

You might want to consider a simpler approach! Just put a Combo Box on the
form to select existing clients. Set its Limit to List property to Yes, and
use its NotInList event to pop up a client-entry form. That would let the user
insert a new client as needed.

See the online help for NotInList, and/or
http://www.mvps.org/access/forms/frm0015.htm

 

[mscertified]

I do have a combo box right now to select the clients.
So I would get rid of the subform and have all the client data in read-only
unbound controls (it all has to be visible).
When moving from ticket to ticket I would have to populate these controls
manually in the OnCurrent event..
There would be a separate form to create new clients or modify existing
client data.

Sounds like a reasonable approach. Thanks.

 

[John W. Vinson]

I do have a combo box right now to select the clients.
So I would get rid of the subform and have all the client data in read-only
unbound controls (it all has to be visible).
When moving from ticket to ticket I would have to populate these controls
manually in the OnCurrent event..
There would be a separate form to create new clients or modify existing
client data.

You don't actually need to put any code to display the other client data. You
could instead have unbound textboxes pulling from the combo's Rowsource using
the Column() property:

=comboboxname.Column(n)

where n is the zero based index of the field you want to display.

 

[DawnTreader]

Hello

it may be a moot point by now, but you could also use a main unbound form
with 2 subforms, one for the client, and one for thier tickets. this would
allow you to arrange them anyway you liked.

additionally you could use a list box to show the client information and
when selected it would load the tickets for that client accordingly.