select highest value from table as default cell value

[sachavdk]

Hi,

In one of my tables, I have a primary key based on two cells:
user_id_year
user_id_num

The user_id_year gets the current year when a record is added, but user_id_num should get the next value for the year.
(Basically an autonumber, but it should be depended of the year)
Eg:

user one has id:
2005 0001
user two has
2005 0002
...
a new user should get 2006 0001, but autonumber will give a new user 2006 0003

I thought of selecting MAX(user_id_num)+1 where the user_id_year=Year(Now()), but it won't work as a default value.
Ideas (or solutions) so solve this are welcome

Thx in advance

 

[KornGeek]

The code in faq700-184 could be modified to do what you seek.

 

[IGPCS]

what you can do is creating a sql should give you only where its 2006 and dcount + 1

hope you got it

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY