Best way to create Header / Detail records

[tagteam]

I am building a new application that will have a header record and detail records.



I do this currently with one of my processes and the way I do it is:

• Create a new header record with some unique info
• Create new Detail records
• Update all the detail records with the header record ID

I don't really like this process because it seems I should be able to create the header and then use the info in that header record to populate the ide of the detail records on creation.

However, I am not sure how to do that.

[Welshgasman]

You could have a main form for the header record, and subform for the detail records, linked by HeaderID

[Micron]

Are you creating these records with a main form/sub form setup? The 'linked master' and 'linked child' fields should be able to take care of this automatically.

[tagteam]

That will work for about 50% of the time with this app because the user will enter one header record and start working on the detail.

However many of the time it will be more like in my first app. In my first app I am running a query that creates over 100 header records and over 5,000 detail records at one time. I would need some vba to loop through and create the header, then detail and then move to the next one and I am not good enough with coding so I create all the headers, then create all the detail, then using criteria that exist in both the header and the detail records I update the detail records with the ID from the header record.

In this app I will do something like that about half the time. The user will select a couple of parameters and then it will create one header record and between 1 and 200 detail records depending on the scenario.

I will have a couple of ways for the user to do it in this new app. One will be a main form / subform but the other will require a similar setup where I create the header and then add x number of records to the detail.
Could I do something where I add the header, then use VBA to get the last record Id ( or most recently added) and then somehow pass that through to the query that adds the detail records so it is all done at once?
Thanks

[CarlettoFed]

If you have created something in Access, attach an example file so that we can understand better.
Otherwise, give us at least a list, even a partial one, of the possible Headings and Details so that we can see how you could do it.

[Minty]

If it's in Access you could use a data macro to automatically insert some detail records?
Not sure how flexible it would be, as I don't know how you would disable it in normal use.

[Micron]

Agree with suggestion to upload a db copy because not much of that can be understood. Perhaps it comes down to your terminology, or something is missing from the picture. A header is a portion of a form or report. A main form is for parent records, subform for child records. If you already have records with such a relationship then the pk value from the parent record should already be in the child records as a foreign key. When creating new, this setup can handle the pk and fk linking automatically. So either way there should be nothing else you need to do. If you're working with data that has been fed into the system, then maybe that's another thing but it would be part of the missing puzzle piece.

[tagteam]

Sorry, I cant upload the database, there is too much other stuff in it and my bosses wont let me. So I will try to be more clear.
This is basically like a regular order invoice.

Header Table - Contains Invoice number, date, company name, etc - The general details of the invoice - you could call this the underlying table of the main form
Detail table - contains multiple line items with information such as car number, cost, adjustments etc. - You could call this the underlying table of the subform

I have no problem creating an new record on the main form and then it creating a single new subform record. And then I can add subform records as needed.

My main question is what is the best when when I am adding 100+ main form records where each main form record has 1-300 subform records.

Currently I use an append query to create all the main form records, then an append query to create all the subform records. Then when they are done I run an update query to add the main form ID to each subform record it should be tied with. I do this by linking a customer name and accounting period to the customer name and accounting period in the subform table. However, that is not good data normalization because it requires duplicate data in each table.

I would like to be able to have a loop or something that creates the first main form record, uses the id from that record in the main form iD field in the subform and creates the 1-300 records I need and then move on to the next customer and do the same thing. This would of course loop through all my customers and I would not need to do the update query at the end.

[CarlettoFed]

You could attach a db where you delete all the tables that do not participate in the project part you would like to manage.
From what you say here "Detail table - contains multiple line items with information such as car number, cost, adjustments etc. - You could call this the underlying table of the subform" strong doubts arise about the correct structuring of the database.

[mike60smart]

Hi

Can you explain the scenario where you are "adding 100+ main form records where each main form record has 1-300 subform records."

Where do these 100+ main form records come from?

[tagteam]

Ok, I will start working on a stripped down version because I am interested in the best way to do this.

The scenario is we lease cars. Each customer has x number of cars. So for each month I run a query in the main database and add to the invoicing tables for each customers and every car they still have in their fleet.

[Micron]

You could make this about tool rentals or anything else if it helps. Or there is a utility here for randomizing data in a way that it can't be unscrambled. I get the paranoia though. Speaking for myself, I couldn't care less about what your db contains since I'm retired, don't do Access freelancing and don't profit from helping out here.

EDIT - the 100+ main records represents what, 100+ customers? And the 1 to x hundred sub records represent 1 to x number of vehicles leased by any one customer?

[tagteam]

Thanks for the offer of help. I have created a very simple database that kind of emulates my problem.


When you open the database it will open a form.
Choose an Accounting period and a different billing period and click create monthly
Next click the View Monthly and you will see the details in spreadsheet view.
In the spreadsheet (q_ViewMonthly) you will see that the InvoiceID is 0. That is the one I am trying to fill with the header InvID.


Thanks

[pbaldy]

I'd look at adapting this:

http://allenbrowne.com/ser-57.html

Instead of your append query I'd open a recordset on the same query as a select query. Looping the recordset, add a header record for each, grabbing the ID and then adding the detail records.

[CarlettoFed]

What you are doing seems useless to me since what you would like to have would seem to be a summary of the car rental, which you can do via query without creating further tables. If this is not the case you should explain better, furthermore I believe that a certain relationship is necessary between Accounting Period and Billing Period otherwise you could account for the Billing Period 01/12/2023 with the Accounting Period 01/12/2022.
Furthermore there is a repetition of:

• The CustomeID in tbl_Invoicing_Detail would be enough to relate to the tbl_Customers
• CustomerName, CarLeaseBegin, CarLeaseEnd in the tbl_CarTable, those in the tbl_Invoicing_Detail are enough

​