UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Subform Linked To Another Continuous Subform, Access 2016    
 
   
cjl678
post May 23 2020, 01:21 AM
Post#1



Posts: 4
Joined: 23-May 20



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.
Go to the top of the page
 
June7
post May 23 2020, 01:28 AM
Post#2



Posts: 1,512
Joined: 25-January 16
From: The Great Land


Review https://www.fmsinc.com/MicrosoftAccess/Form...kedSubforms.asp

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
projecttoday
post May 23 2020, 01:54 AM
Post#3


UtterAccess VIP
Posts: 12,375
Joined: 10-February 04
From: South Charleston, WV


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

--------------------
Robert Crouser
Go to the top of the page
 
Gustav
post May 23 2020, 03:13 AM
Post#4


UtterAccess VIP
Posts: 2,257
Joined: 21-February 07
From: Copenhagen


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: Synchronizing Multiple Subforms in Access


--------------------
Microsoft Office 365 (Access) MVP 2017 ->
Go to the top of the page
 
cjl678
post May 23 2020, 10:34 AM
Post#5



Posts: 4
Joined: 23-May 20



The relationship between line items and contracts in one to one, although not all line items have contracts.
Go to the top of the page
 
projecttoday
post May 23 2020, 11:03 AM
Post#6


UtterAccess VIP
Posts: 12,375
Joined: 10-February 04
From: South Charleston, WV


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.

--------------------
Robert Crouser
Go to the top of the page
 
June7
post May 23 2020, 12:16 PM
Post#7



Posts: 1,512
Joined: 25-January 16
From: The Great Land


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?

This post has been edited by June7: May 23 2020, 12:18 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
projecttoday
post May 23 2020, 01:11 PM
Post#8


UtterAccess VIP
Posts: 12,375
Joined: 10-February 04
From: South Charleston, WV


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.

--------------------
Robert Crouser
Go to the top of the page
 
cjl678
post May 23 2020, 01:16 PM
Post#9



Posts: 4
Joined: 23-May 20



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?
Go to the top of the page
 
projecttoday
post May 23 2020, 01:28 PM
Post#10


UtterAccess VIP
Posts: 12,375
Joined: 10-February 04
From: South Charleston, WV


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?

--------------------
Robert Crouser
Go to the top of the page
 
tina t
post May 23 2020, 01:31 PM
Post#11



Posts: 6,671
Joined: 11-November 10
From: SoCal, USA


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

--------------------
"the wheel never stops turning"
Go to the top of the page
 
tina t
post May 23 2020, 01:35 PM
Post#12



Posts: 6,671
Joined: 11-November 10
From: SoCal, USA


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

--------------------
"the wheel never stops turning"
Go to the top of the page
 
cjl678
post May 23 2020, 02:06 PM
Post#13



Posts: 4
Joined: 23-May 20



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.
Go to the top of the page
 
June7
post May 23 2020, 02:20 PM
Post#14



Posts: 1,512
Joined: 25-January 16
From: The Great Land


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).


--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
projecttoday
post May 23 2020, 02:20 PM
Post#15


UtterAccess VIP
Posts: 12,375
Joined: 10-February 04
From: South Charleston, WV


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.

--------------------
Robert Crouser
Go to the top of the page
 
projecttoday
post May 23 2020, 02:29 PM
Post#16


UtterAccess VIP
Posts: 12,375
Joined: 10-February 04
From: South Charleston, WV


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

--------------------
Robert Crouser
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    4th July 2020 - 06:09 AM