Separate Tables per year - one form to view/edit/search possible?

[Guest]

The database I am building originally had one table that recorded the main
information with an auto number as the primary key. I have a field for the
year. My users would like the db to start back at #1 when the year field
goes from 2005 to 2006, etc. I am not sure how to do this with out making
numberous copies of the original table and forms, one for each year with a
switchboard or something where the user selects which year. The problem with
that solution comes when it is this time of year when there are active jobs
for two years (in this case 2005 and 2006). Is there a better way to do this?

Thanks

 

[tina]

the Autonumber data type should not be used for values that "have meaning"
to a user, and it cannot guarantee a sequential progression of numbers.
suggest you add a field to your table to hold the sequential value; i'll
call it SeqValue. since you already have records in your table and
presumably the users have been "seeing" the Autonumber value up to this
point, you can update the SeqValue field in the existing records with the
value of the Autonumber field. then hide the Autonumber field in forms and
reports, and display the SeqValue field instead. you'll need to write code
in the data entry form's BeforeUpdate event to generate the value for the
SeqValue field in each new record. the good thing about this is that now you
have total control, so your code can be written to re-start the sequence of
numbers at the beginning of each new year. this avoids any need to add
additional tables to the database each year, which you absolutely do NOT
want to do.

hth