MS ACCESS: Form and subform to fully link? or Form and unbound continous subform?

Question

I am trying to design a Purchase Order form called "Master PO", that contains a continuous subform called POContent. For this, I am using 2 tables called tblMasterPO and tblPOsContent. Here is a screenshot of it:

The key points are that the Master PO form is a split view with a datasheet view on the top, and a combination of text and combo boxes at the bottom. The "PO Content" subform is a continuous form in the lower part of the Master PO form.

At my company, we are trying to keep record of every change we do to a Purchase Order for future analysis, hence the need of a PO revision number. The idea is every time we make a change to the Master PO or PO Content, I will create a new record with the updated data when the user will click on the "save/update PO" button, using (a lot of) VBA code. The Form / Sub-form combination is not really the best way of calling it, as it should actually be 2 fully linked table in a way (if PO content changes, then Master PO will be updated as well with a new ID#).

I have multiple questions to reach this goal of making this Form work:

• What would be the best way/practice to make sure that I can switch to form and subform fields freely? By, for example, dirtying both tables at the same time? Or have an unbound continuous subform? or a Temporary table?
• If I use the unbound continuous subform, how can I have the user add lines when adding a new item to the PO? For example, I would like the user to click on the datasheet form to view a PO, which would then populate the PO content items in the unbound subform. But when the user click on "New PO" button, he would then have no data in any of the Master PO AND PO Content fields, so user could then add items in the form and subform, and finally save everything by clicking on the "Save/Update PO" button.

Do you have any tips or ideas to make this work on access?

I know there will be a lot of VBA, but I am expecting it; I am just trying to find workarounds/solutions from experienced access programmers!

Thank you!

EDIT 1:

Basically, when creating a PO, the workflow is to first add general information such as, who is creating the PO, what date, what project is it related to etc; These go to the "master PO" table. Then, you want to actually add items to this PO, so you have multiple lines (for example you want 1x pack of screws, then 2x packs of nuts etc). When you are done doing the PO, you can finally save it using the "Save/Update PO" button, that will save everything in the dedicated tables.

Parent form is indeed automatically updated if I start adding items to the subform, but this is what I am trying to "pause", while I add parts in the subform. The "easiest" would probably be to have one table combining both, but the database will grow extremely fast if I have to save the same general information for each "PO content" line (meaning every time I add an item to the purchase order, I will save the vendor, the username, the date ordered...); I would have something like 100+ columns in my table for each item I order, for each revision, for each PO!

Set a flag or Public variable is something I looked into, but I am not sure how I could stop the entry of the main form while I am adding items into the subform.

Dirtying the parent file is also something I thought about; but it means that we will have 2 instances of the "general data" when creating a PO, because you will first add items into the master PO fields, then move onto the PO content subform that will automatically save the Master PO data under an ID#. And then, when actually adding items to the subform, it will them redirty the parent form, creating another ID# for the same data;

Can we combine both entry at the same time? Some kind of 2 table full link because one does not exist without the other?

Answer 1

I'm not sure much VBA is required. The save of the parent table (parent form) is automatic in Access; the instant you change focus to sub form, the parent form record is saved (and it REALLY is good it works this way).

As for a change/mod in the sub form, and you need to update the parents form PO number?

The before change event in the sub form does not fire unless you made changes to that one row. So you can go:

me.Parent.form!PONum = "some new number".

It is not clear if you only want ONE change for multiple change(s) in the sub form, or you want to do this for "many" changes in one given go.

I'm betting that this new PO update is to only occur once, even if you change several sub-form rows. So, that can be a challenge. In that case, I would probably set a form level flag in the sub form (Public var - start of the code module). Thus once any before change event fires, it can set this flag to say that one, or many, records have been changed. Thus, at this point, then we need a "point" in time in which we update the parent form as a result of the one, or several rows having been changed. Note that the parent form can look at, test if that "flag" in the sub form shows changes. So, then the question is how does the user "act" when they are done? Since they might decide that a few more changes are required in that sub form - and do some more editing. But, as noted, that just means our sub form changed flag we have will STILL show true.

So, the key issue then becomes at what point in time and how we decide they are done? Once we determine this (maybe closing the form?), then we have a point in time at which we know the sub form rows (1, or many) have been changed. And thus we need to check this changed flag in the main form when we leave that record, or say close the form.

What I would do in this case is actually make sure the sub for editing causes the main record to become dirty. That way no matter HOW the user leaves this current main record, the before change event will fire, and at that point in time you can check if the sub form had changes (with that flag). And thus at at point in time is when you can update the new PO number, and copy out the record to the change history. (which should only be a few lines of VBA - 2-3 inserts perhaps?

Edit

OK, one of the pressing issues of course is how to complete the whole thing, but not necessary save it. Well, with bound forms, we save HUGE amounts of code. But then how do we deal with something that may not be finished, not necessary done, and perhaps requires more edits, and THEN we are done?

Well, no question the end user does NOT care about relational database theory, and when they see a form, they just think of "save". But that's not how access works WHEN we introduce related data (such as sub forms).

However, we don't have the ability to wrap the main form (table) and the child form (table(s)) in a single transaction concept. You can do this in other systems, but then again, they are using dis-connected recordsets, and don't enjoy the HUGE savings in wiring up the code for editing like we do in Access.

So, for example, I had a system for collection of donations. The problem of course is that during data entry, you would have to enter all of the donations. And like a classic Quickbooks screen, you would enter say $100, but THEN have to split that amount into multiple accounts. And I don't allow saving of the data unless the split amounts match the main record donation amount. But, since we don't have that "save" that is the result of the form + sub form?

Well, I introduced what they did in the old days! - (batch processing). So, I allow the user to enter many contains for an event. And they can do the split out of the accounts for each amount. They can edit, can add, can change their mind - go away, go for coffee, and come back and edit some more. But I added a POST job button. So, after they enter the data, 1 donation, maybe 20, when they hit post to finish their work, I THEN check if the split amounts match the donati0n amounts. If they do not, then I don't allow them to post. but, the post is not some big computer run of posting data. It simply a field in the main post run. So you can't post unless the amounts match. And once they have posted, then I don't allow them to edit or change that data. The screen looks like this:

So, on the top? That is the event (date, time, additional information). And it also has the batch run, and posting status. On the left side is the person + donation amount, and on the right side is the split of that given amount. (and the total box goes green if the amounts split out match the donation amount. But I STILL allow them to continue working - even if the amounts don't match. But until that posting flag is set? Then I don't include that data in the reports and system. So, the posting does not move records or anything - but I only change the one batch flag that the all the records belong to. and I included that posted flag in the queries that pull that data - if not post flag, then that record is not considered valid until such time the batch run is posted! And once posted, then they can't edit that data.

In your case, you thus could have a edit button. You don't' allow any editing of the form + sub form until they hit edit. Once they do, you flip allow edits = true, and then they can freely edit main + sub form. But only have they had the button "done" editing (which could be the same button as edit - you hide one show the other). That way, you have a work cycle in which they start editing, and have to hit done editing. when done. You could even prompt when they leave or close the form to post or not.