UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> How to update complete form all at once    
 
   
covershaker46
post Nov 30 2007, 11:27 PM
Post#1



Posts: 79
Joined: 4-November 07



I have a form(frmInstalls) with a subform(frmMetrics) embedded. Within the main form (frmInstall) there are several combo and text boxes that I will enter information into. Within the Subform(frmMetrics) there are several text and combo boxes that I will enter information into. both form and subform are linked together by a key field. Problem -- After I enter information into the main form and click on a control in subform before update event occurs. I would like to enter data in main form then subform click Save button at which data is save to associated tables. How do I do that?
dc
Go to the top of the page
 
R. Hicks
post Nov 30 2007, 11:32 PM
Post#2


UA Forum Administrator
Posts: 40,467
Joined: 8-February 00
From: Birmingham, Alabama USA


Only way would be to use Unbound forms ....
When using bound forms .. when you navigate to subform from a mainform with a dirty record .. the data in the main form will commit to the bound table. There is no way to stop this from happening (at least that I know of) ....
DH
Go to the top of the page
 
Steve Schapel
post Dec 1 2007, 03:00 AM
Post#3


UtterAccess VIP
Posts: 3,881
Joined: 5-January 07
From: New Zealand


DC,
On view of Ricky's advice... Are you able to explain why you don't want the main form data to be saved when you move to the subform?
Go to the top of the page
 
LPurvis
post Dec 1 2007, 07:26 AM
Post#4


UtterAccess Editor
Posts: 16,085
Joined: 27-June 06
From: England (North East / South Yorks)


Just to run on a parallel line here (as opposed to a complete tangent as normal lol)...
It's important for you to still answer Steve's question of *why* you want this.
FWIW I don't think it's a particularly unusual requirement. Wanting to "confirm" changes before committing the data. The only issue is that you're using both a parent and subform here - hence the prevention of a BeforeUpdate check.
HAs Ricky says - you have Unbound forms as an option. Though of course - a subform is very often displaying more than one record (continuous or datahseet) - which would negate the potential of an unbound form for that (though the main form could remain so).
So - those parallel alternatives.
Local Tables:
The most traditional method would be to use local tables for temporary storage.
Fear the not, there's nothing ground breaking about this - it's as old as the hills.
You just have a local table structure similar to your server tables in question - insert the data for the records you're looking at - bind your form (parent and sub) to those local tables.
Make all the updates you want. Your cancel button does nothing - just closes the form.
Your Save button updates the server table's records to be what your local tables are now displaying. You can do this en masse with a SQL Update statement or iteratively comparing each time using a recordset. (The former will be faster - the latter more precise).
(If you *really* wanted you could create the tables via code when required - rather than maintaining them manually).
Transactions:
It's possible to wrap form usage up into a transaction. (Well - ever since Access 2000 anyway).
You open a recordset within a transaction - and bind your form to it.
Then make the decision to commit or rollback as required at the moment in code using the transaction surrounding your form's recordset.
I have an example of this in the examples page linked to in my Sig below. Umm what's it called again... ah yes "Transactions In Forms " - how very cryptic of me lol).
It offers DAO and ADO methods of doing so.
Data In Memory:
In concept this is similar to the first Local Tables option.
Rather than having to maintain local copies of tables though you use recordsets entirely.
The last two demo applications in that same example page offer methods for binding forms to recordsets that aren't using live data. (The latter of the two would be the most applicable here I'd say "Hierarchical Data in Access with Shaped Recordsets" - catchy title, don't you think? ;-).
Disconnected ADO recordsets have very real applications in areas like yours.
Now - you might be thinking "why not just use a disconnected recordset altogether?"
i.e. open a recordset - disconnect it - bind it to your form, edit away - if you want to save reconnect and update.
Super!
But your (potentially) continuous form would throw a spanner in the works there.
Batchupdates work well in disconnected recordsets. But they don't once bound to a form. The recordset is altered in a non-standard fashion and only one row will be updated.
So what do we do?
You can just open a recordset - disconnect it and bind it to the form.
Update away as required. Then when saving iterate through the recordset - firing updates as required.
The Shaped recordset has the advantage of allowing new fields to be appended. That lets us track which rows have been updated - and then write back only those rows (I apore wasteful data writes ;-)
Actually - thinking about it now - I did a demo of this and offered it in a thread a while back.
Might be directly useful to you - I'll look for it...
Oh man - you know when you get Deja Vu - and then ignore it.
Don't!
Another thread - *directly* related to this. :-(
I've almost entirely duplicated it here.
This thread - and in particular this comment and download.
Enjoy!
<Wanders off muttering to self about well spent time on a Saturday morning...>
:-)
Go to the top of the page
 
covershaker46
post Dec 1 2007, 09:44 AM
Post#5



Posts: 79
Joined: 4-November 07



Steve, don't want main form to update until users are satisfied with information that he / she has entered in both main form and subform. My users tend to change thier minds often. don't want commit to happen until they are ready.
Thanks,
dc
Go to the top of the page
 
vtd
post Dec 1 2007, 06:43 PM
Post#6


Retired Moderator
Posts: 19,667
Joined: 14-July 05



... and the parallelism runs for ever, even for a Saturday. sad.gif
It is too taxing for my concentration and I need to learn your typing skills ...
In a database I worked with one of my friends, we actually created the ADO Recordset out of thin air, i.e. not based on any Table (adding Fields by code) and then use the "thin air" Recordset as the Subform's Recordset.
However, I think your disconnected Recordset should be more efficient, at least in creating the Recordset ...

Cheers
Go to the top of the page
 
LPurvis
post Dec 1 2007, 07:08 PM
Post#7


UtterAccess Editor
Posts: 16,085
Joined: 27-June 06
From: England (North East / South Yorks)


Yep indeed - ADO recordset out of thin air. sad.gif
That would be the second last example on the page.
"Bind Any Data to an Access Form by Generating Recordsets"
:-)
Includes a class for all the recordset creation and form binding - and manipulates the recordset to allow edits and appending on form.

However, as you rightly point out, I did indeed lean toward the final example method - shaped recordsets for this instance though - just for data loading time.
(The only reason for my having bothered with a shaped or created recordset really is to allow another field to Tag the edited status of a row :-)
The example of this is best found in the other thread post I've linked to.

thumbup.gif
Go to the top of the page
 
JVanKirk
post Dec 1 2007, 08:44 PM
Post#8


UtterAccess VIP
Posts: 4,209
Joined: 4-March 04
From: Clarksville, TN


Leigh, I have to admit, I didn't read your entire post above...but the last few lines made me laugh....
If you said this in your post, sorry..
But, couldn't you create a cancel that when clicked would undo changes made in your subform and also delete(using SQL) the main form record? Granted, the record would get saved still during your move the subform, but having a cancel button performing the step I mentioned above would accomplish basically the same thing....
J
Go to the top of the page
 
covershaker46
post Dec 2 2007, 01:30 AM
Post#9



Posts: 79
Joined: 4-November 07



Is it possible to cancel the before update event for both forms based on a boolean. then when user has completed all changes, trigger update event based on changed boolean to update tables. If so are there any negative effects. Also How would I do I code it?
Thanks,
dc
Go to the top of the page
 
LPurvis
post Dec 2 2007, 04:29 AM
Post#10


UtterAccess Editor
Posts: 16,085
Joined: 27-June 06
From: England (North East / South Yorks)


The Cancel button would need to be in the subform of course - and you'd need to fobid the user from navigating away from the subform without having clicked a button.
And deleting the main form record seems extreme. Are we dealing with only new record additions here?
Otherwise that wouldn't be a possible alternative. (And there are possible alternatives - I've described them twice! lol ;-)
Go to the top of the page
 
LPurvis
post Dec 2 2007, 04:32 AM
Post#11


UtterAccess Editor
Posts: 16,085
Joined: 27-June 06
From: England (North East / South Yorks)


No it's not so possible - one update event *will* fire before the other.
Maintaining a boolean variable to decide whether to proceed or not with either would be used to cancel the BeforeUpdate event.
But this would forbid you from leaving the form or even record. So you'd not be able to then edit the other parent/sub form.
I've given you a fairly lengthy text on the subject... and a working demo in the linked to other thread.
The class code isn't something you need to necessarily understand if that's the problem.
Go to the top of the page
 
LPurvis
post Dec 2 2007, 08:28 AM
Post#12


UtterAccess Editor
Posts: 16,085
Joined: 27-June 06
From: England (North East / South Yorks)


Even though you've not looked at that other example yet - I've realized that it's actually a simple, standard disconnected recordset sample - so should be pretty straight forward I'd say.
Certainly worth you at least checking it out.
But what I thought it would contain was infact the attached mdb - which is another example but instead using the Shaped class to open the recordset.
*This* one shows how the UpdateBatch method doesn't work for bound forms to a disconnected recordset - but offers the iterative method instead, which *is* refined using an appended field to mark which rows have been updated.
If you want it simple though - the previously linked demo is the one to look at.
Attached File(s)
Attached File  RecordsetShapeUpdates.zip ( 83.72K )Number of downloads: 86
 
Go to the top of the page
 
covershaker46
post Dec 2 2007, 09:01 AM
Post#13



Posts: 79
Joined: 4-November 07



FYI.... Only one record.
OK, I'll try the local tables method. How do I handle the following.
1. Primary Key variable usually set via autonumber variable from main form / server.
2. What to do with local record after save to server.
Go to the top of the page
 
LPurvis
post Dec 2 2007, 09:30 AM
Post#14


UtterAccess Editor
Posts: 16,085
Joined: 27-June 06
From: England (North East / South Yorks)


The linked to example from earlier includes two methods.
One shows a disconnected recordset - the other using a local table.
(OK - in the example all tables are local - but in a real situation one would be external ;-)
o answer your questions for now...
1. You don't assign live primary key values to the local records.
You keep the existing PK from the parent record - stored in a non-automunber local field.
So it can be written back to the same row later.
I don't think I handled the case of adding new rows to the table in the local table example - only the disconnected rst.
2. Delete them. DELETE * FROM tblLocalCopyTableName
Go to the top of the page
 
covershaker46
post Dec 2 2007, 10:07 AM
Post#15



Posts: 79
Joined: 4-November 07



Thanks for last reply. Not sure about linked to example. I must be missing it. I think that method will work for new records, but what about existing record that needs to be updated / modified?
Thanks,
dc
Go to the top of the page
 
LPurvis
post Dec 2 2007, 10:20 AM
Post#16


UtterAccess Editor
Posts: 16,085
Joined: 27-June 06
From: England (North East / South Yorks)


Have you seen my rather substantial post earlier?
And towards the end of it where I offered a link to this earlier thread example?
Go to the top of the page
 
covershaker46
post Dec 2 2007, 01:42 PM
Post#17



Posts: 79
Joined: 4-November 07



Sorry, overlooked link. I read all post for that thread and downloaded the attachment. I think that I can change my db to be very similar to example. I still have one issue. In my db the Primary key is generated (autonum) from table bounded to main form. Ii is generated when main form becomes dirty. How do I connect to that on server to have it generate a key for me? Once I get the key I will move it into a field for use in updating server table later.
Thanks,
dc
Go to the top of the page
 
LPurvis
post Dec 2 2007, 01:48 PM
Post#18


UtterAccess Editor
Posts: 16,085
Joined: 27-June 06
From: England (North East / South Yorks)


Are you saying you plan to *add* new main form records as well as subform ones through this process - as opposed to edit existing ones?
Go to the top of the page
 
covershaker46
post Dec 2 2007, 02:10 PM
Post#19



Posts: 79
Joined: 4-November 07



I need to be able to add new records as well as edit existing records.
Go to the top of the page
 
LPurvis
post Dec 2 2007, 02:40 PM
Post#20


UtterAccess Editor
Posts: 16,085
Joined: 27-June 06
From: England (North East / South Yorks)


Then you'll need to maintain your Primary Key field in the local copy of the table - but *not* the primary key itself.
HAs you'll need to be able to leave that field Null so you can determine at runtime to insert back to the live table - rather than Update.
When you're updating you'll fill that field with the PK field value from the live table - so you know which row to write back to. ;-)


Edit: I'm assuming you're intending on operating on only one parent record at a time. Writing back or canceling - and then another one. Though perhaps more than one child record per parent.
Edited by: LPurvis on Sun Dec 2 14:41:51 EST 2007.
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 08:26 PM