Form with subform in transaction

[Mark_]

@OP,

A couple questions that may dictate your approach.
1) Do you need to associate physical items (inventory, vehicles, what not) to a given transaction? If yes, you'll need to work out assignment and roll back if the customer doesn't go forward with the transaction. This pretty much requires you update as you go so you can "Reserve" the inventory or asset as you enter the transaction to prevent another user from committing that asset.
2) Does this involve scheduled blocks of time? Same as above.
3) is a quote of any kind involved? If so you need to keep all records relevant to the quote until after the quote expires. Avoids the issue of "Hey, you quoted me X last week for this job. I want to go ahead with it now even though I didn't then!"
4) Any relationship between the transaction entered (and potentially rolled back) where you want to track employee performance?

Just want to make sure you've noted these even if you won't need them. Hate when you find out after you've implemented something that it won't support your clients needs. I'd still bring this up to the client to make sure they sign off they understand to protect you if they change their mind.

 

[Imb]

My app is meant to replace poorly designed Excel worksheets. Since there is no Ctrl-Z with Access, I must provide an alternative that still feels familiar to end users, otherwise they will stick with Excel or use dedicated commercial software if the project is big enough.

I am not sure, but I think all your goals are already implemented in my applications. But be careful, it is a complete different look at Access. Instead of starting with the data-structures, it is starting with the user-definitions.

Let's start with some simple remarks, that will probably start further discussions.

I use a dedicated "form" to enter new records, and I use a dedicated "form" to edit/modify existing records.
In the single-record-form I save the modified field immediately, but it is possible to postpone that after pressing a save-button. Yes, you are right, these are unbound forms. "Bounding" of the controls is done on the moment that you enter the control. The Bounding is not in the standard way, but to a description how this control should behave, including all restrictions, and including where to find the target field.

I do not use parent-child constructions. Each entity (parent, child) is "equal level", and thus bi-directional. You can add a child to a parent (that is what you are used to), but you can also, starting with a child, add that to a parent.
This last is in standard Access mostly a problem.
If you add or edit a child record, that record will also contain a FK to the parent, and thus a control with information on that FK (e.g. a name). This control is obligatory, the record cannot be saved with an "empty" control.
Clicking on the parent-control opens a "form" where you can select the right parent, by a simple click. If the right parent does not exist in the database, you can add a new record on the spot, and after saving, you can use that parent.

Are these about the things you need?

 

[Azaverte]

@Mark_ The answer to all of your questions is no. The intended purpose of my application is to store and manage the data that is generated as part of the Systems Engineering process of various projects. That is, requirements, product architecture, validation tests, etc. It is an internal app, not for sale. The term "transaction" is used in the SQL sense.

@Imb From your description I am guessing that each record ("entity") has fields for the FK of its parent and child. If this is correct, then your solution will not work for me because each record can have several parents and children, so I need to use a junction table, hence the need for a subform.

 

[Imb]

@Imb From your description I am guessing that each record ("entity") has fields for the FK of its parent and child. If this is correct, then your solution will not work for me because each record can have several parents and children, so I need to use a junction table, hence the need for a subform.

Hi Azaverte,
I use junction tables, but no subforms!
Instead of a subform I open a new main form of the related items as are in the subform. The advantages of this is that you are not limited to the size of the subform control, and, the new form is not a child form but an independant (main) form with all its possibilities, for further linking, so bi-directional. The parent/child relation is mimiced by passing the main form's Item and the main form's Id to the new form.

For example, I have a Document_tbl with some 200,000 documents. Each document is related to "many" Persons, but also to "many" Subjects.
In the Document_form I can click "Person"-button to open a new form of related Document_Person records using Document_id = main_form_ID.
But I can also click "Subject"-button to open a new form of related Document_Subject records.

The same holds also for the other direction.
With the Person_form (based on Person_tbl, with 180,000 persons) I can click on "Document-button" to show the same Document-Person records, now using Person_id = main_form_ID.
And, of course, also from the Subject form to Document_Subject records.

You feel the dynamics? You can hiphop from the one relation to the next one, with always full possibilities of the main forms: dynamical selections, sorting, form properties, ...

 

[DHookom]

@Imb, when are you going to post full documentation or a sample application file since you seem to be pushing your solution on a frequent basis. Maybe set up a zoom call for some of us to actually view this "dynamic" solution. If not, it does absolutely no good to read your posts.