Format [AutoNumber] & Restart at particular condition

[mfaisal.ce]

Dear All,

I have a Parent/child tables...

Parent Table: Sales (SNo)
Child Table : Sales_Detail (SNo)
Child Table : Sales_Payment (SNo)
Child Table : Sales_Return (SNo)

Each Parent Record has multiple Child records in each table....
Each Child Table has column SrNo column with AutoNumber format.

1. what i need is; SrNo [AutoNumber] restarts with each SNo and does not continue as new record in Child Table
2. I need to Format SrNo [AutoNumber] as SNo-SrNo

Example,

If SNo is 1, then SrNo for all child records in Sales_Detail Table should be like
1-1
1-2
1-3
1-4
and if SNo is 2, then it should be like this
2-1
2-2
2-3
2-4

Database File is attached, plz find any solution if possible...

regards,

 

[theDBguy]

Unfortunately, I don't think Autonumber fields can restart. So, if that's what you want, you may have to stop using Autonumber fields.

 

[arnelgp]

you can use a Query for your tables.
adding a Calculated Column that will concat the "parent" sno and the
"child" sno.

use the query as rowsource of your form/subform.

 

[The_Doc_Man]

An autonumber is wrong for this application. Autonumbers must remain unique AND there is the problem that an autonumber can skip a number sometimes based on failed or retracted update attempts. You want a "custom autonumber" so perhaps this article will help:

Autogenerate incremented number based on number of records and add a prefix

I'd like to improve the data entry for a form so that fewer mistakes are made when entering this number. I have two tables. One is Projects the other is ProjectActivites One Project can have multiple activities. I have a form that shows a Project and in that form I have a subform that shows...

www.access-programmers.co.uk

 

[Pat Hartman]

Leave the autonumber as the PK for the table and use it in any relationships. The following is a couple of examples of how to make your own "intelligent" sequence numbers.

For the use you suggested, I would NOT create a custom sequence number but here's the code anyway.