How to do transaction processing across two different unbound forms

[Paul1]

I need to perform a transaction involving two operations (depending on which parameters the user selects at the point of save in the form “frmEditReport”).

If the User selects a certain parameter then I need to perform these 2 operations at the point of save:
1. Save the current form Edit operation in “frmEditReport”.
2. Insert a new record in another table handled by another form, “frmSelectWorkspaceToAdd” in the database, which opens automatically when a certain parameter is selected in the Edit Report form.

Therefore, the entire transaction will need to be wrapped across two different forms in the database.

Also, please note that the form handing the new record insertion also contains its own transaction processing because in addition to inserting a new record, the form also updates another field in the newly inserted record.

More Detailed Outline of the Problem


The user will double click a form record in a listbox to edit a record in this form. This causes the “Edit Report” form to be displayed as shown below in which the user will make changes to the record and then click Save Record to save the record.

The problem to be solved is when the form opens with a Report Status different from “Live” and the User changes the Report Status selection to “Live“.

When this happens then an additional operation needs to be performed in addition to saving the current “Edited” record. This additional operation involves the user choosing a “Workspace” value and inserting this value in an entirely different table. Also these 2 operations need to be wrapped in a transaction such that they either both succeed or are rollbacked accordingly.

The form “frmEditReport” is an unbound form and the additional operation to be performed when report status is changed to “Live” in the form is to add a Workspace to another table in the database. In my database adding a workspace is handled by another unbound form called “frmSelectWorkspaceToAdd”.

Some code outlining the problem is shown in the attached PDF document below. Please note that it is partly pseudo code and demonstrates what needs to be done. I could bypass the problem entirely to insert a new workspace record in “frmSelectWorkspaceToAdd” by duplicating that code in the form “frmEditReport” but of course I understand that this wouldn’t be good programming practice at all.

I am sorry if this is unclear but I am not looking for a fully coded solution to this problem but only some guidance on how to wrap transactions in 2 different forms if this is possible (in particular utilizing an already created 2nd form in the database and where this 2nd form already has its own transactions nested in the save procedure). Also I can provide more details about the 2nd form and the data to be inserted there but I don't want to make this post excessively long and complicated.

Many thanks for all your help and assistance.

[Micron]

this code is behind the form(s)? You'd need to write the subs or functions in a standard module and call them via the form event in order to do transaction processing AFAIK. Only one form event can initiate this, since you cannot start a transaction, leave it, process another transaction elsewhere and return to the transaction in progress all the while having continuity. Research Transactions for the Execute method (especially nested ones), and how to deal with recordsets. You have FindFirst right after creating the object. If it contains no records, you will get an error. There might be other potential pitfalls, but...

When posting code, please do so in code tags (# button on toolbar). Having to flip back and forth between your attachment view and this response window is difficult, plus it robs us of the ability to copy code for making suggestions. I can't copy from your attachment.

[orange]

Paul,

Use this search criteria with Google ms access transaction processing example
You will get some info to further explore your options.

Examples at these links:
https://msdn.microsoft.com/en-us/lib...ffice.12).aspx
http://www.geeksengine.com/article/a...ansaction.html

[Paul1]

Thanks for your reply. Yes the code that I provided was behind the forms. I understand your point about the need to put the code into a standard module and only being able to call this function once from the form.

I have 2 further questions.

1. A quote from Microsoft website:

"When you use transactions, all databases and Recordset objects in the specified Workspace object are affected; transactions are global to the workspace, not to a specific database or Recordset. If you perform operations on more than one database or within a workspace transaction, the Commit and Rollback methods affect all the objects changed within that workspace during the transaction".

Does this mean that I can modularize items from the two separate forms in my database into separate functions and procedures in a standard module and call these functions and procedures from other procedures in the standard module as long as I am using the following workspace object?

Set ws = DBEngine.Workspaces(0)
Set db = ws.Databases(0)

2. Can you explain why I might need to use nested transactions? I don't fully understand this.

[Micron]

1 - what you said fits the M$ explanation wrt to transactions being a 'method' of the workspace object. To be honest, I have limited experience with transactions that are governed by events outside of any given form or procedure they're running in; i.e. their scope has been limited to one form, or one functional procedure. I have nested them, however, which simply means that one transaction starts, code does some stuff or takes input, then another transaction begins. AFIK, the second must terminate before the first, whether that means complete it or roll back. Likely if the inner transaction is rolled back, the outer one would be required to do the same.

I checked to see what AB has on his site http://allenbrowne.com/ser-37.html which isn't a lot (there may be more there but buried in some example or other). Also, there are threads like this one https://www.pcreview.co.uk/threads/q...rowne.3991174/ which may help. You might catch that a db object can be set to the current db, or a workspace. From what I recall, it is also possible to define more than one work space.

Does this mean that I can modularize items from the two separate forms in my database into separate functions and procedures in a standard module and call these functions and procedures from other procedures in the standard module as long as I am using the following workspace object?

Not sure what you mean by this. Yes you can call procedures in standard modules from a form (just don't declare them as Private) and yes you can call them from other procedures in the module (even if they're Private). I don't believe you can call a form event if the event code is in a standard module (probably not what you're asking), but you can call a form event FROM a standard module. However, I don't believe any of that relates to a workspace object, so maybe I've misunderstood.