Cancel changes in form w/subform 
Author Message
 Cancel changes in form w/subform

Hi all -

Grrrrrr..... in case you didn't know, if you cancel changes made to a
form (using "Me.Undo" et al), and the form has a subform associated with
it, changes made to the subform will not be canceled.  Not only that,
you can't use "Me.Dirty" to find out if changes were made to the
subform.  Access saves changes to the subform record as soon as you
return to the master and vice versa.

Microsoft acknowledges this behavior in KB article #Q131535.  They also
provide a workaround in the file FrmSmp97.exe, which works, although it
is pretty dubious and kludgy.  What you have to do is create two
temporary tables in your database which have the same structures as the
main form's source table and the subform's source table.  In the form's
Current event, the temp tables are cleaned out and then loaded with the
data for that record.  If the user presses your Cancel or Undo button,
the source records are then deleted and replaced with the contents of
the temporary table.

This is all fine and dandy (I guess...), as long as you aren't doing
multiuser applications.  If two users open the "temporary" tables at the
same time, the first user's data will be deleted.  For my current
application, each user is running their own copy of the front-end
database on their local drive, and I can put the temp tables in the
front-end rather than the back-end database.  But I have seen situations
where someone accidentally runs the "master" copy of the front-end
database off the server, and there may be times when they need to do
this.  I cringe at the idea of having such volatile code out there.  I
also cringe at the idea of having to write code to figure out which
user's data is whose.

The other idea I had was to simply create a uniquely named table for
each user for the duration of the session and delete it when they close
the form.  This seems like the best solution out of all evils, but on
the other hand it could make the database very cluttered and large if it
wasn't compacted very regularly.  So my question is: is it possible with
DAO to create a recordset based on an existing table structure but which
is only stored in memory, does not actually get written into the Access
database, and goes away when closed or could easily be deleted?  I could
see quite a few potential uses for such a thing, so any suggestions
would be appreciated.

Of course, if anybody has come up with a better way than Microsoft to
cancel form and subform changes, those would be appreciated too!

Thanks,
A very disgruntled
Lee Flier
Principal Consultant
Anchor Systems, Inc.
Atlanta, Georgia, USA



Sun, 24 Jun 2001 03:00:00 GMT  
 Cancel changes in form w/subform
Why don't you try to use transactions and rollback all changes when the user
cancels?

--

Woody


Quote:
>Hi all -

>Grrrrrr..... in case you didn't know, if you cancel changes made to a
>form (using "Me.Undo" et al), and the form has a subform associated with
>it, changes made to the subform will not be canceled.  Not only that,
>you can't use "Me.Dirty" to find out if changes were made to the
>subform.  Access saves changes to the subform record as soon as you
>return to the master and vice versa.

>Microsoft acknowledges this behavior in KB article #Q131535.  They also
>provide a workaround in the file FrmSmp97.exe, which works, although it
>is pretty dubious and kludgy.  What you have to do is create two
>temporary tables in your database which have the same structures as the
>main form's source table and the subform's source table.  In the form's
>Current event, the temp tables are cleaned out and then loaded with the
>data for that record.  If the user presses your Cancel or Undo button,
>the source records are then deleted and replaced with the contents of
>the temporary table.

>This is all fine and dandy (I guess...), as long as you aren't doing
>multiuser applications.  If two users open the "temporary" tables at the
>same time, the first user's data will be deleted.  For my current
>application, each user is running their own copy of the front-end
>database on their local drive, and I can put the temp tables in the
>front-end rather than the back-end database.  But I have seen situations
>where someone accidentally runs the "master" copy of the front-end
>database off the server, and there may be times when they need to do
>this.  I cringe at the idea of having such volatile code out there.  I
>also cringe at the idea of having to write code to figure out which
>user's data is whose.

>The other idea I had was to simply create a uniquely named table for
>each user for the duration of the session and delete it when they close
>the form.  This seems like the best solution out of all evils, but on
>the other hand it could make the database very cluttered and large if it
>wasn't compacted very regularly.  So my question is: is it possible with
>DAO to create a recordset based on an existing table structure but which
>is only stored in memory, does not actually get written into the Access
>database, and goes away when closed or could easily be deleted?  I could
>see quite a few potential uses for such a thing, so any suggestions
>would be appreciated.

>Of course, if anybody has come up with a better way than Microsoft to
>cancel form and subform changes, those would be appreciated too!

>Thanks,
>A very disgruntled
>Lee Flier
>Principal Consultant
>Anchor Systems, Inc.
>Atlanta, Georgia, USA




Sun, 24 Jun 2001 03:00:00 GMT  
 Cancel changes in form w/subform
You can't do what you want with DAO, but you can with ADO, which has a
much more versatile recordset object that allows you to add data to an
"unbound" recordset in code (and even save it to a persistent file
whence it can be reloaded).

As for alternative strategies, you could have the subform be bound to
a temp table that you create in the front end and then save it to the
"real" table if OK'd.

Also, Access 2000 will offer some new options if you can wait and like
being a pioneer. Beta copies are available from Microsoft.

 -- Andy

Quote:

>Hi all -

>Grrrrrr..... in case you didn't know, if you cancel changes made to a
>form (using "Me.Undo" et al), and the form has a subform associated with
>it, changes made to the subform will not be canceled.  Not only that,
>you can't use "Me.Dirty" to find out if changes were made to the
>subform.  Access saves changes to the subform record as soon as you
>return to the master and vice versa.

>Microsoft acknowledges this behavior in KB article #Q131535.  They also
>provide a workaround in the file FrmSmp97.exe, which works, although it
>is pretty dubious and kludgy.  What you have to do is create two
>temporary tables in your database which have the same structures as the
>main form's source table and the subform's source table.  In the form's
>Current event, the temp tables are cleaned out and then loaded with the
>data for that record.  If the user presses your Cancel or Undo button,
>the source records are then deleted and replaced with the contents of
>the temporary table.

>This is all fine and dandy (I guess...), as long as you aren't doing
>multiuser applications.  If two users open the "temporary" tables at the
>same time, the first user's data will be deleted.  For my current
>application, each user is running their own copy of the front-end
>database on their local drive, and I can put the temp tables in the
>front-end rather than the back-end database.  But I have seen situations
>where someone accidentally runs the "master" copy of the front-end
>database off the server, and there may be times when they need to do
>this.  I cringe at the idea of having such volatile code out there.  I
>also cringe at the idea of having to write code to figure out which
>user's data is whose.

>The other idea I had was to simply create a uniquely named table for
>each user for the duration of the session and delete it when they close
>the form.  This seems like the best solution out of all evils, but on
>the other hand it could make the database very cluttered and large if it
>wasn't compacted very regularly.  So my question is: is it possible with
>DAO to create a recordset based on an existing table structure but which
>is only stored in memory, does not actually get written into the Access
>database, and goes away when closed or could easily be deleted?  I could
>see quite a few potential uses for such a thing, so any suggestions
>would be appreciated.

>Of course, if anybody has come up with a better way than Microsoft to
>cancel form and subform changes, those would be appreciated too!

>Thanks,
>A very disgruntled
>Lee Flier
>Principal Consultant
>Anchor Systems, Inc.
>Atlanta, Georgia, USA




Sun, 24 Jun 2001 03:00:00 GMT  
 Cancel changes in form w/subform

Quote:

> Why don't you try to use transactions and rollback all changes when the user
> cancels?

Wish I could, but that's just my problem - I can't.  You can't use a transaction
to roll back all changes on a form and subform.  Lame, huh?

Lee



Sun, 24 Jun 2001 03:00:00 GMT  
 Cancel changes in form w/subform

Quote:

> You can't do what you want with DAO, but you can with ADO, which has a
> much more versatile recordset object that allows you to add data to an
> "unbound" recordset in code (and even save it to a persistent file
> whence it can be reloaded).

Sounds good, but I don't feel like taking apart all my code (which is already
in "live" use) just to recode it using ADO.  Blah. <G>

Quote:
> As for alternative strategies, you could have the subform be bound to
> a temp table that you create in the front end and then save it to the
> "real" table if OK'd.

Well that's essentially the same thing as what I'm doing, in terms of the
concern I have about it, which is that IF two users end up using the same
front end file (which they aren't supposed to, but I have no faith in their
IS people to enforce that), there would be a big problem.  The workaround I'm
considering is to create a unique name for each temp table per user, but I've
never done a zillion creates and deletes of tables in an Access database
before.  Will it eventually cause the front end database to grow to monstrous
size unless the users compact it regularly? (they won't).  This is why I'd
been hoping for the temporary cursor that doesn't get written to the
database, but I'd be interested to hear from others who have done apps that
require lots of creating and deleting of temporary tables.

Quote:
> Also, Access 2000 will offer some new options if you can wait and like
> being a pioneer. Beta copies are available from Microsoft.

I'll look forward to it.  Unfortunately this is for a client who, of course,
needed it yesterday. <G>  I really don't blame them actually, it seemed like
a reasonable request at the time.

Thanks for your help,
Lee

Lee A. Flier
Principal Consultant
Anchor Systems, Inc.
Atlanta, Georgia, USA



Sun, 24 Jun 2001 03:00:00 GMT  
 Cancel changes in form w/subform
Access creates its only transactions for bound forms, which we have no
ability to roll back. In Access 2000, this may be possible.

 -- Andy

Quote:

>Why don't you try to use transactions and rollback all changes when the user
>cancels?

>--

>Woody



>>Hi all -

>>Grrrrrr..... in case you didn't know, if you cancel changes made to a
>>form (using "Me.Undo" et al), and the form has a subform associated with
>>it, changes made to the subform will not be canceled.  Not only that,
>>you can't use "Me.Dirty" to find out if changes were made to the
>>subform.  Access saves changes to the subform record as soon as you
>>return to the master and vice versa.

>>Microsoft acknowledges this behavior in KB article #Q131535.  They also
>>provide a workaround in the file FrmSmp97.exe, which works, although it
>>is pretty dubious and kludgy.  What you have to do is create two
>>temporary tables in your database which have the same structures as the
>>main form's source table and the subform's source table.  In the form's
>>Current event, the temp tables are cleaned out and then loaded with the
>>data for that record.  If the user presses your Cancel or Undo button,
>>the source records are then deleted and replaced with the contents of
>>the temporary table.

>>This is all fine and dandy (I guess...), as long as you aren't doing
>>multiuser applications.  If two users open the "temporary" tables at the
>>same time, the first user's data will be deleted.  For my current
>>application, each user is running their own copy of the front-end
>>database on their local drive, and I can put the temp tables in the
>>front-end rather than the back-end database.  But I have seen situations
>>where someone accidentally runs the "master" copy of the front-end
>>database off the server, and there may be times when they need to do
>>this.  I cringe at the idea of having such volatile code out there.  I
>>also cringe at the idea of having to write code to figure out which
>>user's data is whose.

>>The other idea I had was to simply create a uniquely named table for
>>each user for the duration of the session and delete it when they close
>>the form.  This seems like the best solution out of all evils, but on
>>the other hand it could make the database very cluttered and large if it
>>wasn't compacted very regularly.  So my question is: is it possible with
>>DAO to create a recordset based on an existing table structure but which
>>is only stored in memory, does not actually get written into the Access
>>database, and goes away when closed or could easily be deleted?  I could
>>see quite a few potential uses for such a thing, so any suggestions
>>would be appreciated.

>>Of course, if anybody has come up with a better way than Microsoft to
>>cancel form and subform changes, those would be appreciated too!

>>Thanks,
>>A very disgruntled
>>Lee Flier
>>Principal Consultant
>>Anchor Systems, Inc.
>>Atlanta, Georgia, USA




Mon, 25 Jun 2001 03:00:00 GMT  
 Cancel changes in form w/subform
You could add a field for the unique user id, rather than a new table,
and adjust the recordsource at runtime. Adding/deleting records is
probably better than adding/deleting tables. Overall, if you are
worried about it, I think it would be easier to come up with a scheme
that checks the value of currentdb.name and prevents them from opening
the "front-end" off the network.

 -- Andy

Quote:


>> You can't do what you want with DAO, but you can with ADO, which has a
>> much more versatile recordset object that allows you to add data to an
>> "unbound" recordset in code (and even save it to a persistent file
>> whence it can be reloaded).

>Sounds good, but I don't feel like taking apart all my code (which is already
>in "live" use) just to recode it using ADO.  Blah. <G>

>> As for alternative strategies, you could have the subform be bound to
>> a temp table that you create in the front end and then save it to the
>> "real" table if OK'd.

>Well that's essentially the same thing as what I'm doing, in terms of the
>concern I have about it, which is that IF two users end up using the same
>front end file (which they aren't supposed to, but I have no faith in their
>IS people to enforce that), there would be a big problem.  The workaround I'm
>considering is to create a unique name for each temp table per user, but I've
>never done a zillion creates and deletes of tables in an Access database
>before.  Will it eventually cause the front end database to grow to monstrous
>size unless the users compact it regularly? (they won't).  This is why I'd
>been hoping for the temporary cursor that doesn't get written to the
>database, but I'd be interested to hear from others who have done apps that
>require lots of creating and deleting of temporary tables.

>> Also, Access 2000 will offer some new options if you can wait and like
>> being a pioneer. Beta copies are available from Microsoft.

>I'll look forward to it.  Unfortunately this is for a client who, of course,
>needed it yesterday. <G>  I really don't blame them actually, it seemed like
>a reasonable request at the time.

>Thanks for your help,
>Lee

>Lee A. Flier
>Principal Consultant
>Anchor Systems, Inc.
>Atlanta, Georgia, USA




Mon, 25 Jun 2001 03:00:00 GMT  
 Cancel changes in form w/subform

Quote:

> You could add a field for the unique user id, rather than a new table,
> and adjust the recordsource at runtime. Adding/deleting records is
> probably better than adding/deleting tables. Overall, if you are
> worried about it, I think it would be easier to come up with a scheme
> that checks the value of currentdb.name and prevents them from opening
> the "front-end" off the network.

There are several workstations whose network connections are less than reliable
(yes, my client is getting a better network, but they are moving in a few months
and it's not worth it to them to fix it till then) and it seems that the back end
database has a tendency to get corrupted frequently when these workstations run
local copies of the front end.  So if they are having an especially bad day
they're likely to run off the network.

I'm thinking that I could allow them to make multiple copies of the front end on
the server and run one of those if they want.  Is there a way to ensure that the
front end is always opened exclusively?  This shouldn't affect sharing of the
back-end data I assume.

--Lee



Mon, 25 Jun 2001 03:00:00 GMT  
 Cancel changes in form w/subform
Go to Woody's Office Watch and check out V3 N48,
http://www.wopr.com/wow/wowv3n48.html scroll down to The Access Archon.
This is an excellent article on using the tag property of controls for
performing an undo function.

Woody's Office Watch is an excellent weekly periodical on Microsoft Office.
I have found it invaluable, especially for Access.

John Ruff  - The Eternal Optimist :-)

(remove the x in pxapparuff when sending email)

Quote:

>Hi all -

>Grrrrrr..... in case you didn't know, if you cancel changes made to a
>form (using "Me.Undo" et al), and the form has a subform associated with
>it, changes made to the subform will not be canceled.  Not only that,
>you can't use "Me.Dirty" to find out if changes were made to the
>subform.  Access saves changes to the subform record as soon as you
>return to the master and vice versa.

>Microsoft acknowledges this behavior in KB article #Q131535.  They also
>provide a workaround in the file FrmSmp97.exe, which works, although it
>is pretty dubious and kludgy.  What you have to do is create two
>temporary tables in your database which have the same structures as the
>main form's source table and the subform's source table.  In the form's
>Current event, the temp tables are cleaned out and then loaded with the
>data for that record.  If the user presses your Cancel or Undo button,
>the source records are then deleted and replaced with the contents of
>the temporary table.

>This is all fine and dandy (I guess...), as long as you aren't doing
>multiuser applications.  If two users open the "temporary" tables at the
>same time, the first user's data will be deleted.  For my current
>application, each user is running their own copy of the front-end
>database on their local drive, and I can put the temp tables in the
>front-end rather than the back-end database.  But I have seen situations
>where someone accidentally runs the "master" copy of the front-end
>database off the server, and there may be times when they need to do
>this.  I cringe at the idea of having such volatile code out there.  I
>also cringe at the idea of having to write code to figure out which
>user's data is whose.

>The other idea I had was to simply create a uniquely named table for
>each user for the duration of the session and delete it when they close
>the form.  This seems like the best solution out of all evils, but on
>the other hand it could make the database very cluttered and large if it
>wasn't compacted very regularly.  So my question is: is it possible with
>DAO to create a recordset based on an existing table structure but which
>is only stored in memory, does not actually get written into the Access
>database, and goes away when closed or could easily be deleted?  I could
>see quite a few potential uses for such a thing, so any suggestions
>would be appreciated.

>Of course, if anybody has come up with a better way than Microsoft to
>cancel form and subform changes, those would be appreciated too!

>Thanks,
>A very disgruntled
>Lee Flier
>Principal Consultant
>Anchor Systems, Inc.
>Atlanta, Georgia, USA




Mon, 25 Jun 2001 03:00:00 GMT  
 Cancel changes in form w/subform
Regarding creating and deleting tables: You could run a little test program which
creates a bunch of tables and fields and deletes them to see what happens to the
size of the database.  For example, you could use the following functions to
create and then delete 15001 tables (by the way, I wrote these as a test to check
if Access would allow that many tables, which it did).

Public Function funCreateTables()

  Rem ***** Declare local variables *****

  Dim tdfTableName As TableDef
  Dim fldKey As Field
  Dim dbCurrent As Database
  Dim i As Integer
  Dim strTableName As String

  Set dbCurrent = CurrentDb

  For i = 1 To 15001

    strTableName = "tblLimitTest" & CStr(i)
    Set tdfTableName = dbCurrent.CreateTableDef(strTableName)
    Set fldKey = tdfTableName.CreateField("ctrID", dbLong)
    tdfTableName.Fields.Append fldKey
    dbCurrent.TableDefs.Append tdfTableName

  Next i

  dbCurrent.Close

End Function

Public Function funDeleteTables()

  Rem ***** Declare local variables *****

  Dim rsTableName As Recordset
  Dim dbCurrent As Database
  Dim i As Integer
  Dim strTableName As String

  For i = 1 To 15001

    strTableName = "tblLimitTest" & CStr(i)
    DoCmd.DeleteObject acTable, strTableName

  Next i

End Function

Rodney M. Gabriel
**************************

Quote:


> > You can't do what you want with DAO, but you can with ADO, which has a
> > much more versatile recordset object that allows you to add data to an
> > "unbound" recordset in code (and even save it to a persistent file
> > whence it can be reloaded).

> Sounds good, but I don't feel like taking apart all my code (which is already
> in "live" use) just to recode it using ADO.  Blah. <G>

> > As for alternative strategies, you could have the subform be bound to
> > a temp table that you create in the front end and then save it to the
> > "real" table if OK'd.

> Well that's essentially the same thing as what I'm doing, in terms of the
> concern I have about it, which is that IF two users end up using the same
> front end file (which they aren't supposed to, but I have no faith in their
> IS people to enforce that), there would be a big problem.  The workaround I'm
> considering is to create a unique name for each temp table per user, but I've
> never done a zillion creates and deletes of tables in an Access database
> before.  Will it eventually cause the front end database to grow to monstrous
> size unless the users compact it regularly? (they won't).  This is why I'd
> been hoping for the temporary cursor that doesn't get written to the
> database, but I'd be interested to hear from others who have done apps that
> require lots of creating and deleting of temporary tables.

> > Also, Access 2000 will offer some new options if you can wait and like
> > being a pioneer. Beta copies are available from Microsoft.

> I'll look forward to it.  Unfortunately this is for a client who, of course,
> needed it yesterday. <G>  I really don't blame them actually, it seemed like
> a reasonable request at the time.

> Thanks for your help,
> Lee

> Lee A. Flier
> Principal Consultant
> Anchor Systems, Inc.
> Atlanta, Georgia, USA




Mon, 25 Jun 2001 03:00:00 GMT  
 Cancel changes in form w/subform
Design your tables in a way that you allow for multiple occurences,
like 'current' and 'previous', of a 'row'.
Then make a view-query that only gets the lastest occurence of each row.

Some DBMS nowadays allow you to run a transaction against the status of the database
at the time the transaction started, even when the total transaction takes many hours
and other users are updating the database.
--
Dr.Ruud

Quote:


>> You could add a field for the unique user id, rather than a new table,
>> and adjust the recordsource at runtime. Adding/deleting records is
>> probably better than adding/deleting tables. Overall, if you are
>> worried about it, I think it would be easier to come up with a scheme
>> that checks the value of currentdb.name and prevents them from opening
>> the "front-end" off the network.

>There are several workstations whose network connections are less than reliable
>(yes, my client is getting a better network, but they are moving in a few months
>and it's not worth it to them to fix it till then) and it seems that the back end
>database has a tendency to get corrupted frequently when these workstations run
>local copies of the front end.  So if they are having an especially bad day
>they're likely to run off the network.

>I'm thinking that I could allow them to make multiple copies of the front end on
>the server and run one of those if they want.  Is there a way to ensure that the
>front end is always opened exclusively?  This shouldn't affect sharing of the
>back-end data I assume.

>--Lee



Mon, 25 Jun 2001 03:00:00 GMT  
 
 [ 25 post ]  Go to page: [1] [2]

 Relevant Pages 

1. changing the sourceobject of subform when the form and subform are loaded

2. cancel changes to row in subform.

3. cancel subform=cancel mainform

4. Want to add record with form when subform field is changed

5. navigating for keyboard users - subfrom to form - subform to subform

6. ADO WillChangeField event: can't cancel change by changing adStatus

7. Form Question - Stepping Through Subform Records on Master Form

8. nodeclick not changing subform

9. Changing subform load order

10. Change Value in field of subform?

11. Change recordsource of a subform with a VBA-Module

12. Change recordsource of subform at runtime

 

 
Powered by phpBB® Forum Software