Numbering sub records within each record

[rhouston08]

Here is my record set:

Item No	Vin No	Make	Model	Year
1
2
3
4
5

<colgroup><col width="64" style="width:48pt" span="5"> </colgroup><tbody>
</tbody>

I have a separate table that captures the details for each Item No, however I want the Detail No column within the Details tbl, to start back at "1" whenever I input details for a new Item No. Is there a way this can be done? If so, will you please help? Thanks in advance!

 

[Joe4]

Sure. It will probably require VBA.
Please provide the following though:
- the exact names of your fields in the "Details tbl"
- explanation of how data is being populated in this table (especially the "Item No") field
(is it through a form, an Import, etc)?

 

[rhouston08]

Sure. It will probably require VBA.
Please provide the following though:
- the exact names of your fields in the "Details tbl"
- explanation of how data is being populated in this table (especially the "Item No") field
(is it through a form, an Import, etc)?

Below is the Invoice tbl that shows invoices for the Account Holder. In this case, there are 3 invoices at different dates for the Account Holder named Houston.

InvoiceNo	InvoiceDate	VehicleVinNo	Account Name	Unit Price	Total	ApplyTax	Grand Total	Invoice Status	Comments
TWNZ-001	1/18/2017	456782DW2Z789	Houston
TWNZ-003	1/19/2017	456782DW2Z789	Houston
TWNZ-005	1/20/2017	456782DW2Z789	Houston

<colgroup><col width="86" style="width:65pt" span="2"> <col width="222" style="width:167pt"> <col width="122" style="width:92pt"> <col width="66" style="width:50pt" span="2"> <col width="64" style="width:48pt"> <col width="83" style="width:62pt"> <col width="97" style="width:73pt" span="2"> </colgroup><tbody>
</tbody>

Below is the Details tbl which displays the line items for invoice TWNZ-001.

InvoiceNoID	LineNo	Description of Service or Product	Service Type	Quantity	Unit Price	Total	ApplyTax	Grand Total	Invoice Status	Comments
001	1	Engine Failure - Replaced Engine	Vehicle Maintenance	1	$5,300.00	$5,300.00	FALSE	$5,300.00	Submitted
001	2	Tire Blowout - Front 2 tires	Product Purchase	2	$150.00	$300.00	TRUE	$324.75	Submitted

<colgroup><col width="86" style="width:65pt" span="2"> <col width="222" style="width:167pt"> <col width="122" style="width:92pt"> <col width="66" style="width:50pt" span="2"> <col width="64" style="width:48pt"> <col width="83" style="width:62pt"> <col width="97" style="width:73pt" span="2"> <col width="79" style="width:59pt"> </colgroup><tbody>
</tbody>

Both the Invoice and Details tbls are populated manually by the user. What I would like to see, is whenever, I open up the Details tbl, for each Invoice that is entered, the LineNo column starts at 1 til whenever, until the user has completed inputting line items into the database. So when I input the line items for InvoiceNOID 003, the LineNo column does not start a 3 til whenever. I hope I've answered your questions...if not please let me know. Thanks!

 

[Joe4]

I think we would need to have a full understanding of how the data is manually entered.
Is it a Form with a SubForm?
There should also be a common field between the two tables to join them on (should be exactly the same).
I don't see any in your example.

 

[rhouston08]

At the moment, I don't have any subforms; eventually I will. I just want to manually populate the tables. I went ahead and changed the InvoiceNoID to InvoiceNo, so the two tables can link. Below is the new Details tbl...I was just wondering if I there was a way within a tbl that I could restart the numbering sequence over again. In this case for Invoice TWNZ-003, the LineNo continues the sequence with 3 instead of starting at 1. Is this possible or can this only be done in a subform?

InvoiceNoID	LineNo	Description of Service or Product	Service Type	Quantity	Unit Price	Total	ApplyTax	Grand Total	Invoice Status	Comments
TWNZ-001	1	Engine Failure - Replaced Engine	Vehicle Maintenance	1	$5,300.00	$5,300.00	FALSE	$5,300.00	Submitted
TWNZ-001	2	Tire Blowout - Front 2 tires	Product Purchase	2	$150.00	$300.00	TRUE	$324.75	Submitted
TWNZ-003	3	Engine Failure - Replaced Engine	Vehicle Maintenance	1	$8,500.00	$8,500.00	FALSE	$8,500.00	Submitted
TWNZ-003	4	Oil Change	Vehicle Maintenance	1	$650.00	$650.00	FALSE	$650.00	Submitted
TWNZ-003	5	Replaced Tires	Vehicle Maintenance	6	$5,000.00	$5,000.00	FALSE	$5,000.00	Submitted

<colgroup><col width="86" style="width:65pt" span="2"> <col width="222" style="width:167pt"> <col width="122" style="width:92pt"> <col width="66" style="width:50pt"> <col width="72" style="width:54pt" span="2"> <col width="83" style="width:62pt"> <col width="97" style="width:73pt" span="2"> <col width="79" style="width:59pt"> </colgroup><tbody>
</tbody>

 

[Joe4]

No, you cannot run scripts against a table directly. You would want to use an entry Form for user's to enter the data, where you could attach VBA code to the Form to do what you want.

Note the following best practice when developing Access applications:
You should NEVER allow users to access/enter data in your Tables directly! I cannot stress that enough. There is too much potential for bad things to happen, and you cannot control what they do or what they may change. All data entry/manipulation should be done through Forms.

A good Access application will be Form-driven, where users make selections from Menu that take them to Forms and Reports (and maybe Queries).

Basically, what you would do is to have VBA code that runs on your Form, that whenever the InvoiceNoID field is populated, it will automatically populate the LineNo value. What it will do is to search and find all records with the same InvoiceNoID value, take the maximum LineNo value, and add 1 to it. If it does not find any, it will populate one. So, since this field is being derived automatically, you would want to "lock" it on your Form so that the user cannot populate or change it manually (note if they had access to the Table, they can do whatever they want to this field, which would be problematic for what you are trying to do).

 

[rhouston08]

Joe4,

I am still in the framework stage, so I'm creating the tables for the data and populating the tables with mock data to make sure the formatting is working. So In the process of doing this, I was wondering whether or not this could be done via the tables. I will definitely be using forms and subforms; I just haven't gotten to that point yet. However when I do, I will need your assistance with the VBA code (which will be in another week). Thank you for answering my question and assisting me in the future!