handling a main form event while subform control is in edit mode

  • Thread starter Thread starter Paul Ponzelli
  • Start date Start date
P

Paul Ponzelli

I've run into difficulty when I'm trying to run VBA code from a click event
in the main form while a record in a subform is still in edit mode. That
is, sometimes a user will enter a record in a subform and click a command
button in a main form before they have saved the record they're adding in
the subform.

If the code depends on the data in that record that's in edit mode, it won't
be able to run properly since the new data being entered by the user hasn't
been saved into the table.

For example, in one case the click event runs a report that depends on data
in the subform. In this case, I'd like the code in the click event to save
that record in the subform before opening the report, so the report will
show the new record.

I realize I could hide the Report button until the user clicks a Save Record
button, but that seems like it might be adding an unnecessary step in what
must be a very common occurrence - where a user tries to execute a command
before saving the current record. Can anyone tell me the best way to handle
this situation?

Thanks in advance,

Paul
 
Paul said:
I've run into difficulty when I'm trying to run VBA code from a click event
in the main form while a record in a subform is still in edit mode. That
is, sometimes a user will enter a record in a subform and click a command
button in a main form before they have saved the record they're adding in
the subform.

If the code depends on the data in that record that's in edit mode, it won't
be able to run properly since the new data being entered by the user hasn't
been saved into the table.

For example, in one case the click event runs a report that depends on data
in the subform. In this case, I'd like the code in the click event to save
that record in the subform before opening the report, so the report will
show the new record.

I realize I could hide the Report button until the user clicks a Save Record
button, but that seems like it might be adding an unnecessary step in what
must be a very common occurrence - where a user tries to execute a command
before saving the current record. Can anyone tell me the best way to handle
this situation?
Click to expand...


You need to reanalyze your problem. The edited record in
the subform (if you're using that word properly) is
automatically saved as soon as the focus returns to a
control on the main form.
 
The edited record in
the subform (if you're using that word properly) is
automatically saved as soon as the focus returns to a
control on the main form.
Click to expand...

The situation I'm describing here is this:

1. User is editing a record in a (datasheet view) subform, but has not yet
saved the record (the pencil icon still appears in the row header of the
record).
2. While the pencil icon is still visible in the row header, indicating the
record has not been saved, user clicks a command button on the main form
with the mouse.
3. The report appears without the edits in the record (or the new record)
and the subform record is still in edit mode.

I realize that if I could get the user to click a control in the main form
before clicking the command button there would be no problem. But I don't
want to ask the user to go through the extra step - that's why I'm trying to
do it in code.

Any idea how to solve this?

TAIA
 
Paul said:
The situation I'm describing here is this:

1. User is editing a record in a (datasheet view) subform, but has not yet
saved the record (the pencil icon still appears in the row header of the
record).
2. While the pencil icon is still visible in the row header, indicating the
record has not been saved, user clicks a command button on the main form
with the mouse.
3. The report appears without the edits in the record (or the new record)
and the subform record is still in edit mode.

I realize that if I could get the user to click a control in the main form
before clicking the command button there would be no problem. But I don't
want to ask the user to go through the extra step - that's why I'm trying to
do it in code.
Click to expand...


But a button on the main form is a control on the main form.
If you watch closely, the pencil icon dissapears as soon as
you click the button.

The only way you can have multiple records in an edited
state is if they are displayed in completely separate forms
(not a main form - subform situation).

I say again, there must be something else causing your
report to fail to display the edited record. If you're
unconvinced, add some code the button's click event:

If Me.subform.Form.Dirty Then 'Is record edited
Me.subform.Form.Dirty = False 'Save record
End If

Then place a breakpoint on the second line. You will find
that the If never succeeds because the record was already
saved and will never be dirty when the main form button is
clicked.
 
Ok, Marsh, I just figured out why you were so skeptical about my original
question. I neglected to mention that the "control" I was using in my main
form is a label whose Click event runs a procedure that opens a report. I
sometimes use labels instead of command buttons for design purposes. And I
just realized that even though clicking on a label will raise an event, a
label can't take the focus, and that's why the record in the subform remains
in edit mode. Sorry about not mentioning that in my original question, but
I just figured out that distinction between command buttons and labels, in
that they behave differently with respect to Focus.

So if I'm using the click event of a label on a main form to open a report,
is there any VBA code I can run that will
1. Save the record currently being edited in a subform, or
2. Cancel the edit under way in the subform, equivalent to what a user would
do by pressing Esc?

I've tried different variations of DoCmd.Save to save the record being
edited in the subform and SendKeys "{esc}" to cancel the edit, but I can't
get any of them to work.

Any suggestions on how to accomplish these two tasks?

Thanks again in advance,

Paul
 
Paul said:
Ok, Marsh, I just figured out why you were so skeptical about my original
question. I neglected to mention that the "control" I was using in my main
form is a label whose Click event runs a procedure that opens a report. I
sometimes use labels instead of command buttons for design purposes. And I
just realized that even though clicking on a label will raise an event, a
label can't take the focus, and that's why the record in the subform remains
in edit mode. Sorry about not mentioning that in my original question, but
I just figured out that distinction between command buttons and labels, in
that they behave differently with respect to Focus.

So if I'm using the click event of a label on a main form to open a report,
is there any VBA code I can run that will
1. Save the record currently being edited in a subform, or
2. Cancel the edit under way in the subform, equivalent to what a user would
do by pressing Esc?

I've tried different variations of DoCmd.Save to save the record being
edited in the subform and SendKeys "{esc}" to cancel the edit, but I can't
get any of them to work.
Click to expand...


Aahhh, you put your finger on the real issue now. The code
I posted a little while ago to check the subform's Dirty
property should take care of the issue.

If you want to undo the changes (why?) then you can use:
Me.subform.Form.Undo
Don't even think about using SendKeys, it's too buggy to use
for anything real.
 
I'm afraid I may have caused even more confusion because you and I
essentially sent our last two messages off at the same time, with yours
arriving a few minutes ahead of mine. I'm reproducing my last message here
below in the hopes that will clarify the conversation.

Thanks for your patience.

Ok, Marsh, I just figured out why you were so skeptical about my original
question. I neglected to mention that the "control" I was using in my main
form is a label whose Click event runs a procedure that opens a report. I
sometimes use labels instead of command buttons for design purposes. And I
just realized that even though clicking on a label will raise an event, a
label can't take the focus, and that's why the record in the subform remains
in edit mode. Sorry about not mentioning that in my original question, but
I just figured out that distinction between command buttons and labels, in
that they behave differently with respect to Focus.

So if I'm using the click event of a label on a main form to open a report,
is there any VBA code I can run that will
1. Save the record currently being edited in a subform, or
2. Cancel the edit under way in the subform, equivalent to what a user would
do by pressing Esc?

I've tried different variations of DoCmd.Save to save the record being
edited in the subform and SendKeys "{esc}" to cancel the edit, but I can't
get any of them to work.

Any suggestions on how to accomplish these two tasks?

Thanks again in advance,

Paul
 
The code
I posted a little while ago to check the subform's Dirty
property should take care of the issue.
Click to expand...

Thanks for that code, and also the Me.subform.Form.Undo
If you want to undo the changes (why?)
Click to expand...

Well, I know you'll probably tell me this is an amateurish way to do it, but
it does work - it has to do with the way I've been handling a Not in List
event of a combo box. What I've done is to place a command label adjacent
to a combo box that opens a form bound to the underlying data for the combo
box. Users can enter new values into the form, and the form's Close event
requerys the combo box. It works fine as long as the user doesn't leave the
combo box in edit mode when they click on the command label, and hence my
original question to the newsgroup.

As I said, I know this isn't the best way to do it. As I write this, I'm
looking at a way to handle this with some DAO code in Evan Callahan's book,
and I'll probably end up using the method in his book. I've been avoiding
this solution because I've haven't quite grasped DAO and ADO, and I keep
hoping they would go away. (I do realize that's not going to happen,
especially ADO). I've never found a source that could explain either of
those in a way I can understand. I once saw a copy of the manual that came
with Access 2.0 which had an excellent explanation of DAO, but I don't have
a copy of my own.

Any suggestions on a source that gives good introduction DAO and ADO?

Thanks for your consideration,

Paul
 
Paul said:
Thanks for that code, and also the Me.subform.Form.Undo


Well, I know you'll probably tell me this is an amateurish way to do it, but
it does work - it has to do with the way I've been handling a Not in List
event of a combo box. What I've done is to place a command label adjacent
to a combo box that opens a form bound to the underlying data for the combo
box. Users can enter new values into the form, and the form's Close event
requerys the combo box. It works fine as long as the user doesn't leave the
combo box in edit mode when they click on the command label, and hence my
original question to the newsgroup.

As I said, I know this isn't the best way to do it. As I write this, I'm
looking at a way to handle this with some DAO code in Evan Callahan's book,
and I'll probably end up using the method in his book. I've been avoiding
this solution because I've haven't quite grasped DAO and ADO, and I keep
hoping they would go away. (I do realize that's not going to happen,
especially ADO). I've never found a source that could explain either of
those in a way I can understand. I once saw a copy of the manual that came
with Access 2.0 which had an excellent explanation of DAO, but I don't have
a copy of my own.

Any suggestions on a source that gives good introduction DAO and ADO?
Click to expand...


Actually, that way of managing a combo box's data sounds
fine to me. The only odd part is the use of a label instead
of a command button (but now you've got that working too ).

As for ADO/DAO, I never use ADO because it's totally(?)
unnecessay for Jet databases. From what others have said,
I've gotten the impression that there doesn't seem to be
much use for ADO, except when used in an ADP.

I know what you mean about the A2 manuals (and Help files),
<sigh>
 
I sent an email to MS Press asking them for permission to reprint that Sub
from Evan Callahan's book, in case anyone reading this might be interested.
It's a nice solution to the problem. If they grant permission, I'll reprint
it here.
 
Paul said:
I sent an email to MS Press asking them for permission to reprint that Sub
from Evan Callahan's book, in case anyone reading this might be interested.
It's a nice solution to the problem. If they grant permission, I'll reprint
it here.
Click to expand...


Rriiiggghhhhttttt ;-)

Lots of luck
 
Back
Top