Cancel changes in form w/subform
Author |
Message |
Lee Flie #1 / 25
|
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 |
|
|
Wood #2 / 25
|
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 |
|
|
Andy Bar #3 / 25
|
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 |
|
|
Lee Flie #4 / 25
|
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 |
|
|
Lee Flie #5 / 25
|
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 |
|
|
Andy Bar #6 / 25
|
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 |
|
|
Andy Bar #7 / 25
|
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 |
|
|
Lee Flie #8 / 25
|
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 |
|
|
John Ruf #9 / 25
|
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 |
|
|
Rodney M. Gabrie #10 / 25
|
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 |
|
|
\ #11 / 25
|
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 |
|
|
Page 1 of 2
|
[ 25 post ] |
|
Go to page:
[1]
[2] |
|