Forms based on table relationship

[aligahk06]

Dear All,



I thanks all member of msaccess forums for their valuable time and professional support.
Now i'm stuck with new assignment hopefully seeking help from our esteemed professional ms access members.
I have design four tables for controlling my procurement assignments.
The database concept i applied is following.
i have design four table and all tables are normalized and in one to many relationship.
Note : four table , tblPR , tblPO, tblCostCode, tblSupplier
1. tblPr :- tblPR has a field prNO i.e unique and unique workorder no in his table.
2. tblPO;- one PR or the unique PR has more than one PO sometimes one PO or two or more than two PO for a single PR. but each PO has details of items unit and unit price then vat amount and then total price with vat sometimes PO has discount then it will gives total price with vat as it is mentioned in tblPO.
3. each PO has a supplier details tblSupplier for follow up.
4. each PR has a cost code for financial purpose. i.e is tblCostCode

I want a form based on this four tables with following model.

first i get PR and once i entered PR details in my form then after approval few days PO will come. supposing PR has 4 items then may be po will come for two items with supplier details and then again after few days for same pr again another PO will come for remaining items with all po details. i need to continuously entered the details for Reports and follow up.
i mean one PR has more than one PO.
Then each PO is associateds with one vendor with tblSupplier.
Please assist me in design a form and a report that records all the details and future report print .

Please find attached database , i don't have much knoeledge about designing forms from four different tables.

Please aadvise if something wrong with table design.

[ssanfu]

I changed some field names and changed the PK fields to Autonumber.

It looks like tblPR and tblPO are your main tables:
1 procurement (tblPR) can have one or more purchase orders (tblPO)
So I created a main form and a sub form. In the sub form I have 2 combo boxes to be able to select the Cost Code and the Supplier.
(tblCostCode and tblSupplier are Look up tables. )


In design view, if you see some labels/fields that have a yellow background, they are hidden fields. PK and some FK fields should NOT be displayed on a form but are necessary for the forms to function properly.

And the PK suffix means Primary Key. The FK suffix means Foreign Key.

Try adding a few records to the attached dB.......

[aligahk06]

Sir,

Thanks for ur valuable time. But one thing that is not clear how can i feed the data for PR andPO.
My model for work contro is following.
1. first i want to enter PR number and then work order number and then work description only and then save the record . once the manual doc for PR signed then the other team will raise PO for corresponding PR. So again if will come to me then i want to go for PR and then PO details should be entered on coreespond PR
The PO contains material description , unit , qty and vat , discount and total price with vendor name and vendor mobile number.
2. then for each PR there is one or more PO . So where to input PR form and then correspond PO itms in form ?
This is not clear.
Thanks once again.
Please assist.

[ssanfu]

Are the "PR number" and "work order number" different numbers? If yes, then need to add a text control to the "tblPR".

Maybe you would post examples what the data looks like for 3 or 4 procurement assignments.
Also, what does the data look like for 3 or 4 PO's for 1 or 2 procurement assignments