Sub records?

[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

 

[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!!

 

[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!

 

[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

 

[cheekybuddha]

See here

 

[cheekybuddha]

And here

 

[MajP]

I think people are suggesting this could be a possible bug with a new update, since the error message is not likely appropriate. However, it appears to me that the Source Object of the subform control is a table. Maybe you can work around this by creating a datasheet subform instead of directly using the table.