Question Custom auto numbering records

[josephbupe]

Hi all,

I have two tables:

1. tblFiles = FileID(PK)
2. tblFile_Cases = CaseID(PK), FileID(FK)

The first table is the records source for the mainform (frmFiles) from which i open the details form whose record source is the second table. So then details (frmFile_Cases) opened for the first item on the mainform will be listed sequentially sorted by CaseID (i.e 1, 2, 3), but details for the second item appears like this (4, 5, 6 ...) and equally for rest of items.

What I want is to have another field on the details form (frmFile_Cases) for custom numbering of records always starting from 1.

Thank you.

Joseph

 

[Freshman]

What I want is to have another field on the details form (frmFile_Cases) for custom numbering of records always starting from 1.

Must the number be unique to the particular record, or must it simpy start at 1.
If so, you could have a textbox with source as =[CurrentRecord]

 

[josephbupe]

Hi,

Thanks. I just tried your suggestion but all records getting (1). I want them in sequence 1, 2, 3

 

[Freshman]

It will count, but it depends on where you put the textbox (in which form).

Have a look at the attached db with samples of countboxes in both main and sub forms to see the diffrence.

 

[josephbupe]

Thank you freshman.

Yes, I noticed it worked only when the form property is set to single form view mode, but I am working with the form in continous mode. How else can I make it work?

Joseph

 

[The_Doc_Man]

In the formal sense of the word "autonumber" you can't. But if the record number you want isn't the PK (by itself) you can compute it using either a DMax or DCount of all existing records with the same parent record. DMax needs special handling when you are entering the first record of the group but after that, all you need is to compute the new number as 1+DMax of the selected records. Both DMAx and DCount would re-use a record number if you deleted the last record of the group and then added a record. Dcount would also come to a near fatality if you deleted records in the middle of a group but DMax would handle that case.

You have to "roll your own" for this one because you want the numbers to recycle for each new parent. If this were a true autonumber, it could never recycle because an autonumber field carries "baggage" - in the form of attribute Index:No Duplicates.

 

[josephbupe]

Hi Doc,

Not quite so valuable after the MVP status expired.

No, I don't think so. You're more valuable now.

Thanx for the explanantion. I will now search for DMax and learn from there.

Regards.

Joseph