put autonumber in another field

[johnboy7676]

Not sure which newsgroup to post this question, but will probably be
an append query, althought might be an INSERT statement also.

Have an autonumber field to generate unique PK's, but also need
another Sequence field for sorting. What I want to do is: whenever
make a new record, whether through append query or INSERT or even just
entering in a Form, is to make the Sequence field a default value of
the autonumber field. (this allows for later changing of the Sequence
number). But, I don't have a clue as to how to do this. Even just
inputting a record in a form, I don't think the default value can be
the autonumber field, because until you start editing, there isn't an
autonumber yet?

Any help would be appreciated.

 

[Jeff Boyce]

You did say "any help..."<g>

Don't try to use Autonumbers as "Sequence Numbers". Access Autonumbers are
intended to provide unique row identifiers, and are generally unfit for
human consumption.

Instead, take a look at mvps.org/access for ideas about "Custom Autonumber".
While this is a misnomer, the basic idea is that you'd use a short routine
to identify the largest [SequenceNumber] value already used, then add one to
get the next one. Of course, you'd need to have a field dedicated to
holding your sequence number.

Good Luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[johnboy7676]

I've used DMAX + 1 before to get a custom PK, but thought I had to use
autonumber in this case.

I'm using a RunningSum query to get a checkbook like result. The
RunningSum query looks to a autonumber field in the example I used
(author's name escapes me at the moment), so I did the same.

End user wants to be able to go back and change a seq number,
basically in order to correct an incorrect date. (I've explained data
validation, and how they should use an 'adjustment' entry, not try and
change an old entry, but to no avail.) Therefore, because the seq
number must be changeable, can't be autonumber. But I thought maybe
using an autonumber to generate custom seq number would be do-able. So
would end up with autonumber as PK, and just using the custom Seq
number for sorting, and......hmmm, but then it wouldn't work in the
Running Sum query, would it?

Back to the drawing board, I guess.

Thanks

You did say "any help..."<g>

Don't try to use Autonumbers as "Sequence Numbers". Access Autonumbers are
intended to provide unique row identifiers, and are generally unfit for
human consumption.

Instead, take a look at mvps.org/access for ideas about "Custom Autonumber".
While this is a misnomer, the basic idea is that you'd use a short routine
to identify the largest [SequenceNumber] value already used, then add one to
get the next one. Of course, you'd need to have a field dedicated to
holding your sequence number.

Good Luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Not sure which newsgroup to post this question, but will probably be
an append query, althought might be an INSERT statement also.

Have an autonumber field to generate unique PK's, but also need
another Sequence field for sorting. What I want to do is: whenever
make a new record, whether through append query or INSERT or even just
entering in a Form, is to make the Sequence field a default value of
the autonumber field. (this allows for later changing of the Sequence
number). But, I don't have a clue as to how to do this. Even just
inputting a record in a form, I don't think the default value can be
the autonumber field, because until you start editing, there isn't an
autonumber yet?

Any help would be appreciated.