Controlling Autonumber field - Start autonumber from 1 everyday (Composite Key)

[imran688]

Hello,


I have two tables linked to each other in one to many relationship. Instead of auto number, the date and shift (Text) is being used as the primary keys (Composite Primary Key). Here is the tables structures,

Payouts Table:
Date: Primary Key
Shift (Day or Night) : Primary Key

Bills Table:
Date: Primary Key
Shift (Day or Night): Primary Key
Autonumber: Primary Key

The tables Payouts and Bills has one to many relationship. One payout row can have many bills. The problem is that I want to start the Autonumber in bills table everyday from 1. As date and shift are different for every day so even if i start bills from 1 everyday, it wont make same primary key. I can do it manually but I want to make it automatically. Please help me out how to do it.

Thanks,
Imran

[June7]

I don't think an autonumber field can be forced to work that way. Use a number field and programatically manage the sequencing.

[imran688]

How to make manage it programatically. Can you post some code?? thanks

[June7]

This requires code that will look up the most recent sequence number for given criteria and then increment. I do this to create a unique identifier. The code creates the ID then immediately saves it to a record to minimize possibility of multiple users generating the same number. It's only happened once.

Review https://www.accessforums.net/forms/a...ing-23329.html

[ssanfu]

Might also see
http://baldyweb.com/CustomAutonumber.htm