Duplicating Forms/Tables

I have a form and corresponding table that I want to duplicate to keep
records separated for each fiscal year. In the past I have done this by
exporting the table (structure only) and then creating the form from scratch,
painstakingly duplicating the formatting from last year's version because I
want them to match visually. Shouldn't there be an easier way to do this?

Also, is there any way to edit system id numbers? I like to keep my record
numbers matching my system id numbers, but if I delete a record anytime
during the year the system won't reuse that number. Thus my record counter no
longer matches my system id.

Any help with these is appreciated.

On Tue, 17 May 2005 07:06:01 -0700, "Charlie"
wrote:

I have a form and corresponding table that I want to duplicate to keep
records separated for each fiscal year.

This is almost certainly a VERY BAD IDEA. Storing data - dates - in a
tablename is *not* good design! Typically one would store a date field
in the table, and then use a Query to select records for a particular
year.

In the past I have done this by
exporting the table (structure only) and then creating the form from scratch,
painstakingly duplicating the formatting from last year's version because I
want them to match visually. Shouldn't there be an easier way to do this?

There is; simply change the recordsource property of the Form to the
new table. Open the Form in design view; view its properties; change
the Recordsource property on the data tab.

Also, is there any way to edit system id numbers? I like to keep my record
numbers matching my system id numbers, but if I delete a record anytime
during the year the system won't reuse that number. Thus my record counter no
longer matches my system id.

An Autonumber has one purpose, and one purpose ONLY: to create a
unique, meaningless identifier for a record. They are not designed to
be "record counts". Most developers therefore conceal autonumber
values from users' sight. If you want a gapless, sequential number
you'll have to use a Long Integer and program it yourself.

Question: what would you want to happen when you delete the 42nd
record of 3559 records? Renumber records 43 through 3559? in this
table, and in all related tables? How about on the Post-It notes and
cerebral cortexes and other places that these numbers might have been
recorded?

John W. Vinson[MVP]

Thanks for the solution of switching the record source....I knew I was
overlooking the obvious.

As for the date issue, the table does contain a date field. I realize I
could just use queries and have all the records in one table. However, since
everything at the university where I work is compartmentalized into the
almighty fiscal year, and the records in the table are of purchase orders
being written against a specific annual budget, there is some simplicity in
just keepinga separate set of records for each year.

With regard to the system id numbers, you are quite right in your points.
It's just that in my particular little one-man database a bit of flexibility
would be good. I frequently have to go between the hard copy and electronic
records. I track them quickly by jotting the system id on the corner of the
paper record. When I pick up a paper record and want to navigate rapidly to
that record in the database, it is very easy to highlight the record number
at the bottom of the form, type in the id number written on the page, hit
enter, and I'm there. It is several seconds quicker than using the "find"
dialog box. Normally I don't delete any records....I just indicate that it
has been voided. This time I deleted one by mistake and threw off my system.

Of course, in all the time I've devoted to this, I could have easily
reverted to my last back-up, re-entered a few records, and been done with it!

"John Vinson" wrote:

On Tue, 17 May 2005 07:06:01 -0700, "Charlie"
wrote:

I have a form and corresponding table that I want to duplicate to keep
records separated for each fiscal year.

This is almost certainly a VERY BAD IDEA. Storing data - dates - in a
tablename is *not* good design! Typically one would store a date field
in the table, and then use a Query to select records for a particular
year.

In the past I have done this by
exporting the table (structure only) and then creating the form from scratch,
painstakingly duplicating the formatting from last year's version because I
want them to match visually. Shouldn't there be an easier way to do this?

There is; simply change the recordsource property of the Form to the
new table. Open the Form in design view; view its properties; change
the Recordsource property on the data tab.

Also, is there any way to edit system id numbers? I like to keep my record
numbers matching my system id numbers, but if I delete a record anytime
during the year the system won't reuse that number. Thus my record counter no
longer matches my system id.

An Autonumber has one purpose, and one purpose ONLY: to create a
unique, meaningless identifier for a record. They are not designed to
be "record counts". Most developers therefore conceal autonumber
values from users' sight. If you want a gapless, sequential number
you'll have to use a Long Integer and program it yourself.

Question: what would you want to happen when you delete the 42nd
record of 3559 records? Renumber records 43 through 3559? in this
table, and in all related tables? How about on the Post-It notes and
cerebral cortexes and other places that these numbers might have been
recorded?

John W. Vinson[MVP]