By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,494 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

What's the best method to 'batch process' records from a data entry session?

stonward
100+
P: 145
Hi Guys,

I have a system that includes a Purchase form and a Purchase Details subform, one to many relationship etc.

What is the best way to 'hold' the records entered into the subform, before committing them to disc. The customer needs to be able to check the entered data before it has been saved in the table.

I've thought of make table queries and have been experimenting with recordset clones, but am certainly not an expert with DAO/ADO programming. However, if i knew the best/preferred method then I would narrow down how much new I have to learn.

The fields I need to save are called, PurchaseDetailsID, PurchaseID, ProductID, UnitCost and Quantity - the fields of the PurchaseDetails table. Usually there are not more than 6 rows of such data entered each time.


Many thanks for your time,

RPE.
May 4 '12 #1

✓ answered by TheSmileyCoder

You cannot use transactions covering both the main form and the subform as far as I know. Thus the only viable solution is to use a table with temporary values, and then tie the saving/updating of subform records into the main forms navigation (I.e. update child records in live table when main form navigates to another record, or closes)

Share this Question
Share on Google+
10 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
You cannot use transactions covering both the main form and the subform as far as I know. Thus the only viable solution is to use a table with temporary values, and then tie the saving/updating of subform records into the main forms navigation (I.e. update child records in live table when main form navigates to another record, or closes)
May 7 '12 #2

NeoPa
Expert Mod 15k+
P: 31,186
Check out BeginTrans in the Context-Sensitive Help. It would have to be understood well before using, but it may allow you to achieve your aim.

FYI: DBEngine.Workspaces(0).Name == "#Default Workspace#"
May 7 '12 #3

stonward
100+
P: 145
Thanks for your help SmileyCoder.

I figured that temporary tables would be a likely method, but have found it 'clunky' at best so far.

I can't believe this problem has become so difficult to solve...but generally when I have come across such an impasse in Access before, it simply means I have started with faulty fundamentals!

Thanks again,

Stonward.
May 9 '12 #4

NeoPa
Expert Mod 15k+
P: 31,186
Had to reset the Best Answer I'm afraid as it's factually incorrect. Transactions work at the WorkSpace level and, as such, can cover multiple disparate tables and even separate databases, as long as they are within the same WorkSpace.
May 9 '12 #5

TheSmileyCoder
Expert Mod 100+
P: 2,321
I still stand by my answer. If it were as simple as you imply, then the following should work fine. Create a main form with a subform. In the main form, add 2 buttons btn_Cancel, and btn_SaveAndExit, and the following code:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Private ws As DAO.Workspace
  4.  
  5. Private Sub Btn_Cancel_Click()
  6.    ws.Rollback
  7.    DoCmd.Close
  8. End Sub
  9.  
  10. Private Sub btn_SaveAndExit_Click()
  11.    ws.CommitTrans
  12.    DoCmd.Close
  13. End Sub
  14.  
  15. Private Sub Form_Close()
  16.    Set ws = Nothing
  17. End Sub
  18.  
  19. Private Sub Form_Open(Cancel As Integer)
  20.    Set ws = DAO.DBEngine.Workspaces(0)
  21.    Debug.Print "Workspaces:" & DBEngine.Workspaces.Count
  22.    ws.BeginTrans
  23. End Sub
However, when testing it seems all changes are commited regardless of which button is pushed.

I believe that Access handles the data changes in a seperate hidden workspace, and that is why the above does not work. If you (NeoPa) or anyone else has suggestions I am all eyes to look at it.
May 9 '12 #6

TheSmileyCoder
Expert Mod 100+
P: 2,321
You can expand on the above by getting the db object from the workspace, and base the forms recordsets off said db like so:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Close()
  2.    Set Me.Recordset = Nothing
  3.    Set Me.frm_Sub.Form.Recordset = Nothing
  4.    Set ws = Nothing
  5. End Sub
  6.  
  7. Private Sub Form_Open(Cancel As Integer)
  8.    Set ws = DAO.DBEngine.Workspaces(0)
  9.    Set curDb = ws.Databases(0)
  10.    Set Me.Recordset = curDb.OpenRecordset(Me.RecordSource, dbOpenDynaset)
  11.    Set Me.frm_Sub.Form.Recordset = curDb.OpenRecordset(Me.frm_Sub.Form.RecordSource, dbOpenDynaset)
  12.    ws.BeginTrans
  13. End Sub
  14.  
However you loose alot of the built in access funtionality. For instance the parent/child relation is no longer automatically enforced (All child records for all parent records are shown in subform, new child records do not automatically get the correct foreign key assigned.)

Im sure its possible, but I am also sure it will involve alot of extra coding (But PLEASE prove me wrong, and provide an elegant solution, I know of several places I could use it)
May 9 '12 #7

NeoPa
Expert Mod 15k+
P: 31,186
I'm afraid I can't do that now Smiley as that would take more time than I have.

I will say that I expect you may be right as I was only going on the information available in the Help System, and I know that has led me astray in the past. I will add it to my list of things to try, but why not send me a sample db to play with and test out. I will see if I can find a way to implement the logic as explained by the Help System. You have my Skype details. NB. I still only use 2003.
May 9 '12 #8

NeoPa
Expert Mod 15k+
P: 31,186
I did a test and found very similar results to you Smiley. IE. Nothing in a form seems to be managed within a transaction. I too, assumed this might be due to another Workspace object managing all Forms / Reports, but I ran the following code (between updating a control value and moving from the record to trigger changes to be saved) in the Immediate Pane and got the results shown (only) :
Expand|Select|Wrap|Line Numbers
  1. for each ws in DBEngine.Workspaces:?ws.Name:Next ws
  2. #Default Workspace#
The code I used was very basic and inside a simple Form that allows updating of records in a local table (The Help System indicates it won't work with ODBC connections anyway).
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private blnTran As Boolean
  5.  
  6. Private Sub Form_Open(Cancel As Integer)
  7.     Call DBEngine.BeginTrans
  8.     blnTran = True
  9. End Sub
  10.  
  11. Private Sub cmdCommit_Click()
  12.     Call CommitTrans(dbForceOSFlush)
  13.     blnTran = False
  14.     Call DoCmd.Close
  15. End Sub
  16.  
  17. Private Sub Form_Close()
  18.     If blnTran Then Call Rollback
  19. End Sub
Line #18 consistently called Rollback, but never with any effect.

I'd better go back and set post #2 back to Best Answer.
May 9 '12 #9

TheSmileyCoder
Expert Mod 100+
P: 2,321
From the access 2010 context sensitive help on workspaces:
Access 2010 Help
Access Developer Reference
DBEngine.Workspaces Property
Returns a Workspaces collection that contains all of the active, unhidden Workspace objects. Read-only.
Syntax

expression.Workspaces

expression A variable that represents a DBEngine object.
This would seem to indicate that there can be hidden workspaces, and I am guessing thats why you are not seing the workspace used, when looping through the collection.
May 9 '12 #10

NeoPa
Expert Mod 15k+
P: 31,186
Ah. Well spotted young man :-) That would tend to indicate that it may well be, as you say, contained within a hidden workspace.

All we need now is to identify the hidden workspace used, and to determine if all forms are opened within that single workspace, or whether each object is opened within its own, separate, workspace.

I tried to determine the workspace associated with a form but was unsuccessful at finding anything remotely helpful.
May 9 '12 #11

Post your reply

Sign in to post your reply or Sign up for a free account.