auto-numbering records in a sub-table

[Annette]

I am creating a database with a main table that will have records that relate
to many records in another table (one-to-many relationship). The main table
will have an auto-number field as the primary key. I want one record from
that table to relate to many records in the seconadary table. I want the
auto-number field in the secondary table to begin at 1 for the first record
in the main table and I want the second record in the main table to also
begin at 1.

For example,

Table 1 - Main Table
ID=12
ID=13

Table 2 - Secondary Table
Main Table ID=12 Auto-number field=1
Main Table ID=12 Auto-number field=2
Main Table ID=13 Auto-number field=1
Main Table ID=13 Auto-number field=2
Main Table ID=13 Auto-number field=3

Is this possible? If so, how? Thanks in advance.

 

[Douglas J. Steele]

It's not possible using Autonumber fields. You have no control whatsoever
over what value the Autonumber field takes.

If you need that sort of numbering, you'll have to provide your own number,
as opposed to an Autonumber.

 

[Annette]

Thank you for confirming, Doug.

If I provide a table of numbers say 1 - 99, is there a way that I can say to
increment one number for the next record in order to do an auto-number?

The user has a goal of the main table record # being the Report #. Each
report # then will have a revision number and they would like this number to
be automatically assigned.

Thanks!!!!!!!!!!!

 

[strive4peace]

Hi Annette,

make a field in your table:

RptID, long integer

fill existing RptID with sequential numbers

on the form you use to make a new record, you can use the
BeforeUpdate event to generate a unique RptID just before
the record is saved:

me.RptID = dMax("RptID","Tablename") + 1

make sure RptID is on the form and is the controlname

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day

 

[Douglas J Steele]

Actually, that won't quite do it, Crystal. Annette's trying to have the
numbering restart for each value of ID.

She'll have to use something like:

me.RptID = Nz(DMax("RptID","Tablename", "ID = " & CurrentId),0) + 1