Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Forms _ Subform Linked To Another Continuous Subform

Posted by: cjl678 May 23 2020, 01:21 AM

I have a purchase order system, pretty standard, main form, and a continuous subform for displaying line items. Some line items are more complicated than others. They have a "contract" associated with them. Right now these are in their own table and have their own form for editing them. They receive sequential ID numbers.

My ideal scenario would be that there would be a checkbox on each PO line item that would allow me to turn it into a contract and display these additional fields. It seems like this can't be done with continuous forms, since they can't contain subforms, and any changes you make in formatting apply to every record instead of just one.

I think it could work to have another subform on the main form that is linked to the currently selected line item. With a button for adding a new contract? I'm wondering if there is some built in way to achieve this, only displaying the subform when there is a contract, adding a contract and getting a sequential ID, etc.

I'm a total beginner with Access so I'm just kind of muddling through, I was hoping someone with a better overview of the landscape could see what I'm trying to achieve and point me in the right direction.

Posted by: June7 May 23 2020, 01:28 AM

Review https://www.fmsinc.com/MicrosoftAccess/Forms/Synchronize/LinkedSubforms.asp

Posted by: projecttoday May 23 2020, 01:54 AM

welcome2UA.gif
What is the relationship between the line items and the associated contract items?

Posted by: Gustav May 23 2020, 03:13 AM

Twenty years ago, I and Susan Harkins published an article describing a simple method in "Access Advisor" - a paper magazine you probably can't find anymore.

A brushed-up version from 2015 that will sync multiple tabbed subforms can be found here: https://www.experts-exchange.com/articles/18107/Synchronizing-Multiple-Subforms-in-Access.html


Posted by: cjl678 May 23 2020, 10:34 AM

The relationship between line items and contracts in one to one, although not all line items have contracts.

Posted by: projecttoday May 23 2020, 11:03 AM

If you add the contract fields to the line items table that makes it easier. You could put the contract fields on the same form. You could hide the fields until the user clicks on a button or something. I realize that could leave a big blank space on every line that doesn't have a contract. I'm not sure if you can shrink that dynamically or not. If not, you could use a popup for the contract info, like you're doing now, only there is no linkage issue.

Posted by: June7 May 23 2020, 12:16 PM

As projectToday suggests, alternative is to accept a little non-normalization. Combine tables and tolerate empty fields. Use a Tab control and place contract fields on separate page.

Did you review link in post #2?

Posted by: projecttoday May 23 2020, 01:11 PM

If these contract fields pertain to the line item, which would seem to be the case since there is a one-to-one relationship, then keeping them together is normalized, so I don't think there is any design compromise at all. True, there could be many empty fields in the table.

Some more information might be helpful.

Posted by: cjl678 May 23 2020, 01:16 PM

Yes, I tried that method of putting a control in the main form that references the current record selected in the continuous form. When I went to link them, I did not see that control as an option for the master field. I'll have to go through it again to see if I missed something.

Putting it all in one table was an option I considered. The drawback that gave me pause is that then I would need code to generate the sequential contract number, right?

Posted by: projecttoday May 23 2020, 01:28 PM

Each line item has an ID field, right? What do you need another number for?
This is for a purchase order. The actual contract is created by the vendor, right? This is basically a request?

Posted by: tina t May 23 2020, 01:31 PM

QUOTE
The drawback that gave me pause is that then I would need code to generate the sequential contract number, right?

well, how are you generating the contract number now? if you're using a field set to AutoNumber data type, bad idea. you're not guaranteed to get consecutive numbers, and not necessarily even sequential numbers, from AutoNumber. and if you get a number out of the sequence that you want/expect, you'll play hob trying to replace it with the number you want - maybe you'll be able to mickey mouse it, maybe not.

so if that's all that made you split those fields into a separate table with a one-to-one relationship with the main data table, i'd say put them back. but whether you do or not, i'd really recommend that you generate the contract number via code, so you'll always get exactly what you want. as for the issue of having a child table with a one-to-one relationship to the parent table, i understand if you did that because contract fields only apply to a portion (less than half, maybe?) of the parent records. that sounds like sub-classing to me, which is an accepted design alternative AFAIK.

hth
tina

Posted by: tina t May 23 2020, 01:35 PM

QUOTE
Yes, I tried that method of putting a control in the main form that references the current record selected in the continuous form. When I went to link them, I did not see that control as an option for the master field. I'll have to go through it again to see if I missed something.

the link dialog in form Design view won't show an unbound text box's name in the list of fields. you have to enter the LinkMasterFields and LinkChildFields values manually; the textbox control's name in the LinkMasterFields property, and the related foreign key fieldname in the LinkChildFields property.

hth
tina

Posted by: cjl678 May 23 2020, 02:06 PM

The contract is created by us, it's a request to produce product for us, along with the specifications for that product. Most of our line items are not like this, but a significant chunk are.

I'm working around an already existing system, which is the main reason why it's set up this way, but I think it still makes sense as a sort of subclassing as you say. Yes, it's using Autonumber to generate the IDs, if that's a bad idea then maybe I should just set them in code.

Thanks for the tips, that's very helpful, I'll take another look at getting that linkage set up. I'm thinking now that I should have a checkbox on the line item form, that when checked will generate a new contract record and set the contract number in code.

Posted by: June7 May 23 2020, 02:20 PM

Autonumber key is not intended to have meaning to users, just establish relationship between tables. If your contract number must be sequential and increasing (no gaps), then probably should not rely on autonumber. Managing one to prevent gaps is not simple (I have done it). I have never seen autonumber not be increasing.

Generating a custom unique identifier is a common topic (I also have done this).


Posted by: projecttoday May 23 2020, 02:20 PM

QUOTE
I should have a checkbox on the line item form, that when checked will generate a new contract record and set the contract number in code.

That sounds good to me. I think click a button on the line item and the contract form pops up. For new contract entries store the ID of the new contract in the contractID field in the line items table (a foreign key). If you have to have a number for the contract that the users see, generate that with code and store it in the contracts table. Obviously, if there's an existing contract you would display that when the button is clicked.

Posted by: projecttoday May 23 2020, 02:29 PM

Well, you don't really need a button or a checkbox. Just double click on the line to show the contract.