0

I have a database with a snapshot type continuous form (let's call it frmC) whose record source is a query with multiple tables, plus another dynaset type modal form (let's call it frmE) that allows to edit the part of frmC information that comes from a single table. As a simplified example, imagine frmC contains ProductCost from tblProducts, OrderQty from tblOrders, while frmE allows for editing of ProductCost only and has record source tblProducts only. A button in frmC opens frmE with WHERE = the selected record in frmC.

Inside frmE, I wish to also display information from frmC, and use that information for temporary calculations that do not need to be stored (again, simplifying imagine that when increasing ProductCost I want the user to be able to see what the total impact of the increase would be considering the OrderQty).

At the moment I have come up with two different ways to accomplish this:

  1. Adding an unbound locked control in frmE (e.g. txtOrderID) and using a macro connected to the button in frmC to SetValue frmE!txtOrderID=frmC!OrderQty
  2. Adding a bound locked control in frmE, with source frmC!OrderQty

These both work fine, but I am wondering if either is a better choice or if there is any potential issue with any of these that I have not thought about.

As a note, the reason frmE's record source is not the same query as frmC but rather a single table is because I'm following a suggestion to avoid keeping a dynaset type connection open to multiple tables when, such as this case, it is not strictly necessary for data input. If you disagree, I would love to hear more about it.

LazerBear
  • 1
  • Read this post [VBA for passing a value from one form to another](https://stackoverflow.com/questions/1462876/msaccess-2003-vba-for-passing-a-value-from-one-form-to-another) carefully. Hope this will help. – Harun24hr Jan 08 '21 at 10:57
  • Thanks, I had a read and I think I understand what is being proposed in that thread. However, I am trying to keep as much of my code in macros rather than VBA as I'm the only one in my organization with (very limited) knowledge of VBA and I think that macros will be easier to maintain long-term. In that case, I see that my solution n.2 is also mentioned in the thread that you linked, although I do understand that will tightly bind the two forms together (which is fine by me in this case, I do not plan to reuse it anywhere else). Am I missing anything? – LazerBear Jan 12 '21 at 03:35
  • A potential issue is what happens when the user closes or opens a form that is involved in the calculations. So usually I create properties (getters and setters around a hidden global variable(s)). I put the setters and getters as public functions (must be functions) in a code module. Access intellisense can find public functions in code modules. So we get the benefits of encapsulation. When the user opens a key form we get the property for saved and default values. When the user closes the form we set the property so other forms can still use the value. – mazoula Jan 14 '21 at 05:58

0 Answers0