Restart autonumber based on value in other field

[Maggie]

I'm trying to create one table (only) where the combination of the state
field (IL or FL) and and autonumber field work together as the primary key.
I can set them both, but I need the autonumber to start over and be
sequential per state. Is that possible in one field? I assume there's some
chunk of code I need...any thoughts? I'd like to avoid multiple tables per
state.

 

[Jeff Boyce]

Maggie

You've described "how" you are trying to do something (autonumbers, [State],
?multiple tables, ...).

Now how 'bout describing "what" and "why" (that is, what business need are
you trying to solve?)?

I ask, not out of prurient interest, but because the folks who respond in
the newsgroup can offer more specific suggestions when we have more specific
descriptions of the need.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Maggie]

Thanks Jeff...I shall elaborate.
In my tables we are tracking land/properties we own and/or are considreing
purchasing. It's important for us to know how many properties we have per
state. Unfortunately, a count on a query or report won't cut it. We use
forms for our primary means of communication. I have established a
concatenated field that displays on all our forms which brings together a
state code number (1-50), a 2 digit yr, and the autonumber of any given
record (w/2 preceding zeros). For example:
"11-07001"
Our goal is that next time a record is entered for state "11", the code
would read:
"11-07002"
Of course land/properties from ALL states are entered randomly into this one
table when they come along so from a table perspective, I'm going for
something like this:
AutoNum State
1 FL
2 FL
1 IL
1 OH
3 FL
2 OH

Can the autonumber function automatically resequence or pick up where it
left off based on another value like [state]? My thought was to index them
both, which I did, but beyond that I assume I need some code to count records
based on state value and then add 1 to that count??? But I'm no VBA expert.
Maybe I'm making it harder than it is, but any help/advice would be greatly
appreciated.
Thanks so much,
Maggie

Maggie

You've described "how" you are trying to do something (autonumbers, [State],
?multiple tables, ...).

Now how 'bout describing "what" and "why" (that is, what business need are
you trying to solve?)?

I ask, not out of prurient interest, but because the folks who respond in
the newsgroup can offer more specific suggestions when we have more specific
descriptions of the need.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm trying to create one table (only) where the combination of the state
field (IL or FL) and and autonumber field work together as the primary
key.
I can set them both, but I need the autonumber to start over and be
sequential per state. Is that possible in one field? I assume there's
some
chunk of code I need...any thoughts? I'd like to avoid multiple tables per
state.

 

[Jeff Boyce]

Autonumbers are unfit for human consumption, and are not guaranteed to be
sequential.

If your application requires a "sequence number", you'll need to "roll your
own" routine for generating/maintaining that (search on-line for "Custom
Autonumber" - a misnomer, but ...).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks Jeff...I shall elaborate.
In my tables we are tracking land/properties we own and/or are considreing
purchasing. It's important for us to know how many properties we have per
state. Unfortunately, a count on a query or report won't cut it. We use
forms for our primary means of communication. I have established a
concatenated field that displays on all our forms which brings together a
state code number (1-50), a 2 digit yr, and the autonumber of any given
record (w/2 preceding zeros). For example:
"11-07001"
Our goal is that next time a record is entered for state "11", the code
would read:
"11-07002"
Of course land/properties from ALL states are entered randomly into this
one
table when they come along so from a table perspective, I'm going for
something like this:
AutoNum State
1 FL
2 FL
1 IL
1 OH
3 FL
2 OH

Can the autonumber function automatically resequence or pick up where it
left off based on another value like [state]? My thought was to index them
both, which I did, but beyond that I assume I need some code to count
records
based on state value and then add 1 to that count??? But I'm no VBA
expert.
Maybe I'm making it harder than it is, but any help/advice would be
greatly
appreciated.
Thanks so much,
Maggie

Maggie

You've described "how" you are trying to do something (autonumbers,
[State],
?multiple tables, ...).

Now how 'bout describing "what" and "why" (that is, what business need
are
you trying to solve?)?

I ask, not out of prurient interest, but because the folks who respond in
the newsgroup can offer more specific suggestions when we have more
specific
descriptions of the need.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm trying to create one table (only) where the combination of the
state
field (IL or FL) and and autonumber field work together as the primary
key.
I can set them both, but I need the autonumber to start over and be
sequential per state. Is that possible in one field? I assume there's
some
chunk of code I need...any thoughts? I'd like to avoid multiple tables
per
state.