cancel update on movement between form and subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a bound form with 3 text fields and a subform on a Tab control. When
the user moves from one of the text fields to the subform, I trap the
BeforeUpdate event and cancel it because I do not want the data updated
until the user either presses the Save button, exits the form or Tabs to
another page. When I cancel the event, however, the focus doesn't move to
the field in the subform that the user clicked. How do I get around this?
Is there some property that will tell me which field the user selected so I
can set focus after canceling the update event?
 
Lisa,

I think you are asking the impossible! You can't move the focus to a
subform and leave the main form dirty. You might need to reconsider
your basic design. What is the relationship between the main form and
the subform? What is your purpose in not wanting to update the changes
to the main form record?
 
I was afraid of that! I haven't done programming with databases in nearly 10
years and I've forgotten too much! My gut feeling was that I couldn't do
this, but the environment is so different from the 80's and early 90's that I
wanted to be sure. I sound pathetic but I'm really not a bad programmer,
just out of touch! Ah well...back to my problem.

This is only a problem on a couple of forms, but essentially, the main form
contains data from 1 table and the sub form contains data from another table.
These are responses to a questionnaire, so I was tasked with making the
forms resemble the questionnaire as much as possible, grouping responses into
categories. Some categories are small and so they are combined on the form,
but still in seperate tables. This causes some pages on my form to have
multiple sub-forms or, in the case of the first page/main form, the bound
query and sub-form. I probably could have designed this several ways, but I
had to complete this project in a very short 3 week time frame so I picked
something and went with it. For now, I let the DB save automatically, as it
normally would, but the "undo" option they want is what is causing me grief.

The users see a form and expect to be able to undo anything on the form,
anytime they want to, whether they move between forms or not. After thinking
about this, the only way I see to provide them this option with my current
form design is to create a temporary table to save the original data to when
the form is loaded. Then, if the user doesn't want to save their changes
when they exit the form (or tab to the next page), I can "undo" by rewriting
the original data back to the table.

Does this make sense?
Lisa
 
Lisa,

A couple of ideas that may or may not be relevant:

1. Obviously I don't know the details of your project, but it seems
strange to have the responses for the different categories in separate
tables. Wouldn't it work to have them all in the one table? Or else to
base your form on a query that combines all the tables?

2. If the user wants to cancel their entry, you could consider a Delete
Query.

3. Each control has an OldValue property which you could use to revert
to if the user wants to Undo the entries on the form before the record
is saved.
 
Steve,
Here's a quick design overview:
The quesionnaire is essentially interviews with 11 different types of
personnel. Within the interviews, there are categories of questions we are
asking. If I had 1 table for each type of personnel (ie. 11 tables), they
would be very large, as we anticipate collecting a lot of data over time.
Instead, the tables are organized first by type of personnel, then by
category of questions. I could have made seperate forms for each
personnel/category combination, but that would have been 37 forms. Given the
3 week time frame I had to creat tables and forms, and the requirement to
make them essentially mirror the questionnaire, I decided to create a form to
select the customer, then separate froms for each of the personnel types we
interview. On each personnel main form is a tab control, which lets the user
tab through the categories of questions. I want the user to either save or
undo the data in each page of the form before going to another page or
exiting the form. Make sense?

At this point I see 2 options:
1. I could alter the forms for the personnel types to be an unbound main
form, then add more tabs for each of the categories instead of combining
them on pages.

2. Keep my current form layout, but use a join query (I think that is
correct) to combine the tables. (This will let me update without having to
go through hoops-right?)

(Your suggestion about the OldValue property doesn't help because the tables
are already updated when the use goes between the form/subform)

Right now, I need to figure out which option will be more effective, yet not
take lots of time. I needed to have this finished Friday, as I am traveling
this week to begin the interview process and I don't want data piling up!

Thanks for your thoughts - they are appreciated!
 
Lisa,

If I understand you correctly, I guess another way to do this would be
to put some code on the Before Update event of all the forms which are
used as subforms on your questionnaire system. This would be activated
whenever the focus moved off the subform, including where the user tries
to change to another page on the Tab Control. An example would be...
If MsgBox("Save this page?", vbYesNo) = vbYes Then
' do nothing
Else
Me.Undo
End If
 
Back
Top