Sub records? (1 Viewer)

[mounty76]

Good Afternoon All,

I'm wanting to make an addition to an already existing DB, the addition is for purchase orders, I know it's not possible but I'm trying to achieve some like a sub record, whereby the PO name is entered and then there can be up to 20-30 line items for that purchase order. The main record of the PO would have date, supplier (from another table), PO title, total quote, and some checkboxes to say when received, paid, etc... Each line item would then have a description, budget code (from another table), quantity, price, VAT, and total price.

I would then want to be able to see an overview of the PO's current outstanding which is easy enough from a check box, but just the titles of the main PO record, then to click a button to have a look at the 'sub records' associated with that PO...

Any help on this would be greatly appreciated.

Cheers

 

[Pat Hartman]

A PO table is the same concept as an Order table. It is just the opposite side of the transaction. With a PO, you are buying something from someone else. With an Order someone is buying something from you. Both have related child tables to hold the items being ordered/sold and the Items table is related to a product table that defines all the products you buy or sell. I haven't seen a sample with just a PO process. So you might look for a Order Entry sample like Northwinds. To see the structure. You will need at least four tables.

tblSupplier
SupplierID (autonumber, PK)
SupplierName
etc.

tblPO
POID (autonumber, PK)
SupplierID (FK to tblVendor)
PODate
etc.

tblPOItems
POItemID (autonumber, PK)
POID (FK to tblPO)
ItemID (FK to tblProducts)
UnitPrice
Quantity
etc.

tblProducts
ProductID (autonumber, PK)
SupplierID (FK to tblSupplier)
Desc
UnitPrice
etc.

The tblProducts might need a child table if you order any products from multiple suppliers. That would be used so you can keep multiple standard prices, one for each supplier.

 

[LarryE]

You say it's not possible and then go on to describe exactly how to do it. So what is the question?

1. Create the PO table with a PurchaseOrderID as a Primary key and the necessary fields as you described
2. Create a PODetail table with a PODetailID as a Primary key and PurchaseOrderID as a foreign key and the necessary fields as you described.
3. Connect the two tables with PurchaseOrderID.

Or what Pat said.

 

[mounty76]

HI Gents, this is what I was thinking, but I'm not sure where to go after the following sequence:

1. Create new record in tblPO (POnumberID is autonumber - primary key) - Enter order heading, supplier, etc....
2. Click on button to add line item - this creates a new record in tblPODetail

......When creating a new record in tblPODetail how would I make it take the primary key from the tblPO record I've just created and put that as a number in POnumberID in the tblDetail record I'm creating? This would be the foreign key that links the two tables together but not sure how to take this ID number from one table and put it into another table when creating a new record?

Thanks for your help!!

 

[Pat Hartman]

Have you looked at how the order entry samples work? Here's a m-m sample (which is what you have at the Item level. tblPOItems is the junction table between tblPO and tblProducts). The sample is different data so you need to think about the structure and translate it in your head.

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk

Start by creating the tables and use the Relationships window to create the relationships. ONLY if you define specific relationships will Access know what field you are using as a foreign key. Once it knows that, it will help you by automatically setting the master/child links when you add a subform to a main form.

 

[LarryE]

HI Gents, this is what I was thinking, but I'm not sure where to go after the following sequence:

1. Create new record in tblPO (POnumberID is autonumber - primary key) - Enter order heading, supplier, etc....
2. Click on button to add line item - this creates a new record in tblPODetail

......When creating a new record in tblPODetail how would I make it take the primary key from the tblPO record I've just created and put that as a number in POnumberID in the tblDetail record I'm creating? This would be the foreign key that links the two tables together but not sure how to take this ID number from one table and put it into another table when creating a new record?

Thanks for your help!!

I know it can be a little complicated, but here are some step-by-step instructions how I might do this:

1. Create a Single form called FrmPODetail
2. Create a Continuous form called FrmPOForm
3. Create a query using the TblPO table and TblPODetail table and use that as the Record Source for the FrmPODetail form
4. Create a query using the TblPO table and use that as the Record Source for the FrmPOForm
5. Open the FrmPOForm in design mode and insert a SubForm control using the FrmPODetail as the SubForm in the Detail section of the FrmPOform.
6. Link the Master/Child field to PONumberID on the SubForm
7. Make sure you have a Textbox in the SubForm using the PONumberID as a Control Source and Textbox Name. It can be hidden later if you wish but keep it visible for now.
8. Set the PONumberID textbox Default Value to: =Forms![FrmPOForm]![PONumberID]. This will automatically add the ACTIVE PONumberID on the FrmPOForm to any new detail record, so you can add as many new details as you wish and the PONumberID will be added to the TblPODetail table USING the FrmPODetail form.

 

[mounty76]

Amazing, thanks guys. I'm flat out at the moment but will work on this tomorrow, thanks again and I'll keep you posted!

 

[Pat Hartman]

1. Create a Single form called FrmPODetail
2. Create a Continuous form called FrmPOForm

1 is continuous and 2 is single. The detail is a subform and so I name it sfrmPODetail and you drag it onto the frmPO. Access automatically sets the master/child links if you have created the relationship.

 

[LarryE]

Sorry sorry....yes. A continuous form can be a SubForm of a Single form but not the opposite. My mistake.

 

[mounty76]

OK thanks, so should I create the relationship before making the forms if Access automatically then sets the links in the forms? Presume I do this in the relationship control after creating the tables?

 

[LarryE]

1. Tables and relationships and fields
2. Form input queries to be used as Record Sources for forms based upon your tables.
3. Forms for input (and/or viewing records) and reports for reporting.
4. If you go back to add fields to tables later, make sure you add those new fields to the queries you already created. ACCESS will not automatically add them.

 

[mounty76]

HI Gents,

Thanks for all of this, I've got it working but I'm getting an error message "This object does not contain the Automation object 'tblPODetail'"

This appears when I try to enter a new line in the PODetail subform section, I acknoledge the error and it works good and copies over the POID and puts it into the POID for each record in the detail.

 

[mounty76]

Not sure what this error is for as I've not added any VB to the forms (not that I'm aware of anyway lol)

 

[mike60smart]

Hi
Can you upload a copy of the database?

 

[mounty76]

Its a split DB that's quite big with lots of relationships, would be a big file and awkward to do?

 

[cheekybuddha]

Most likely this has nothing to do with your database, but is caused by a bug in a recent update to Access. Check the recent threads to see multiple instances of such weirdness. I can't link to them because I am answering from my phone.
Apparently another update to fix it is available

 

[LarryE]

HI Gents,

Thanks for all of this, I've got it working but I'm getting an error message "This object does not contain the Automation object 'tblPODetail'"

This appears when I try to enter a new line in the PODetail subform section, I acknoledge the error and it works good and copies over the POID and puts it into the POID for each record in the detail.

1. Make sure the PONumberID field is a field on your subform
2. Make the subforms PONumberID default property value: =Forms![FrmPOForm]![PONumberID] ASSUMING FrmPOForm is in fact the main form name.

 

[mounty76]

1. Make sure the PONumberID field is a field on your subform
2. Make the subforms PONumberID default property value: =Forms![FrmPOForm]![PONumberID] ASSUMING FrmPOForm is in fact the main form name.

This is what I've done, still appearing?

 

[mounty76]

 

[mounty76]

I click on OK then it works as it should