Sync field from one tabbed form to another tabbed form.

[HillTJ]

Hi,

I am receipting in raw materials. I have a tabbed form. On a tab called purchases I list the materials I wish to purchase & their quantities. On the next tab I plan to receipt in the materials which may be spread over multiple deliveries. I would like to carry over the "Material" from the "Purchasing" tab to be used to complete the "receipting" tab. I have the following;

Form called FRMPO which filters raw materials so that I can only have one supplier & their respective multiple materials per PO. Into this I have a tabbed sub form "Tabctl23" it has 2 Tabs, namely; "Purchasing" where the materials are selected from the filtered list & "Receipt" where the deliveries are recorded. I'd like to select the item to receipt in by clicking that line on the "Purchasing" tab, then automatically switch to the "Receipt" tab (which is clearly 1-many) to prefill the fields, receipt in and record the quantity etc. Format is;

-TBLRawMaterials
POUniqueID - PK
{Raw Material Stuff}

- TBLPOReceipt
ReceiptID - FK
{Receipting in stuff}

I'd appreciate any assistance. In particular is this a practically workable method of entry of Purchases? Will it work in the real world?

How do I automate this?

As usual, appreciate any assistance, but feel guilty as all I'm doing is asking for assistance!

Cheers Terry

 

[Ranman256]

with this schema:

tPurch Tbl
PurchID, PurchDate, VendorID
1 4/15/2019 123


tPurchDtl table
PurchID, Item Qty, Price ,etc
1 Pens 3
1 Pencils 5


tRecpt table
RecptID, RecptDate, Item, QtyRcvd, QtyPurch, PurchID
1 4/16/2019 Pens 3 3 1
1 4/17/2019 Pencils 1 5 1
1 4/18/2019 Pencils 4 5 1



tPuchased table, is the master record of the order. The 2 child tables:
tPurchDtl table to hold all items ordered
tRecpt table to say what was received, which may be different days and different Qtys

the tRecpt table would fill automatically (append query) from the tPurchDtl table but QtyRcvd & RecptDate are blank.

once items are received, they are given the date/Qty.
if Qty is less than QtyPurch, then another append query adds another record for the difference.

 

[Minty]

I think I would go with the initial tables, and simply calculate what was missing from any particular order, no need for keeping track of an outstanding amount as it can always be calculated.

With regard to your forms, you can easily add a hidden control(s) to your main form that hold the current parent ID(s) and use that to link your second sub form automatically, or you can use VBA, the first method is far easier.

 

[HillTJ]

Minty, An update. I added a control to the main form that holds the "link" between tabbed forms. I then use that value in a select query to filter results in the second tab which I display as a datasheet. Works fine. But, how can I add a new record into this table without having to manually add the "link"? Also what's the best way of adding row totals?

Cheers Terry

 

[Minty]

Use the datasheet as a sub form and set the master / child fields to the hidden value(s) , you can use more than one field in this set up, if required.

That should also remove the need to filter the results.

 

[HillTJ]

Minty, thanks but i don't follow you. I have the subform, containing 2 tabs linked master/child, then the link between the first tabbed form & the second tab, that is another master like/child like relationship copied to a control on the main form. I then use this as the basis for a parameter query in the second tab. I think you get my drift but i cannot grasp yours. If you have the time i'd appreciate it if you could try to explain in simpler terms for a numby.

Cheers Terry

 

[Minty]

I'll try. Although it might be easier if you could upload a stripped down version of your DB to demonstrate.

I notice your table description doesn't appear to have a LineID in the order details, you should have one as this will simplify the process greatly.

Assuming you did, you would simply set the Master / Child Relationship to PurchLineID and problem solved.

 

[HillTJ]

Minty, yes i do. It's the line id on the "one" side of the tab that i've copied to the mmain form.
And, this is the link that i need to form on the many side of the next tab I cannot see how i set this up. I do not see any options in the setting of the parent/child relationship all i get is the option of setting the parent/child between the main form and the tabbed subdatasheet. Thanks for the prompt response & the thought.

 

[Minty]

Okay - You need to get the Line id onto your main form in a hidden control, you do that using the on current event of the datasheet sub form. So each move on the datasheet updates the value on the parent form.

You then use that as the link. Does that make sense?

 

[HillTJ]

Minty, have copied the link to the mainform ok. But, cannot see the option of selecting this control a master.Access does not give me the option of selecting g this control!

 

[HillTJ]

Minty, got your suggestion working within a few minutes. Thanks foe standing by. I'm still passing the link value to a query which sorts records in order. How can I automatically enter the link when entering a new record within that tab?. Cheers

 

[Minty]

You can link by two fields in both sides of the master / child properties, and Access should do all the hard work by automatically inserting the parent values on a new record.

So it would be something like HeaderID; LineID in the settings.

 

[HillTJ]

Minty, thanks but i don't quite get it. For fear of boring you, i'll reiterate what i currently have. Main form and a tabbed control. Control is linked 1 to many to main form. That is one supplier many Purchase Orders. Then on tab1 i have the Purchase Order line items. Each unique line item number is copied to the main form for reference for tab2 which is the one to many receipt of stock against the line item selected in tab one. It passes this value to a parameter select query which selects those records that equal the unique line item, sorts them in date order then returns the lust to tab2. All this works well, but my dilema is upon entering a new record into tab2 where it does not know the link value. Is my set-up wrong. I seem so close to achieving my goal. Sorry to be a pain.

Had another look today. Am unable to select the "one" side of Tab1, but could select the "Many" side of Tab2. Access does not allow me to select "POUniqueID" to which I have many "ReceiptID"s.

 

[HillTJ]

Got it!. What i did was to set the default value foir the fk of the second tab to the hidden "link" (PK) of the first tab. That way access autofills the fk in anticipation of a new entry. I then hide this column on the datasheet form. I Saw it on Youtube!. Seems to work. Are there any considerations i need to include? Is this an OK thing to do?. Thanks to those who helped. I'm slowly learning.