How does a primary key become a negative number?

[Ron]

Hi all

I'm deciding whether to use the PK also as an account number, invoice
number, transaction number, etc that the user will see for the respective
files. I understand that sometimes a number will be missing, which is not a
problem for my purposes I don't think, but how would it be negative? Can
randomly created PK autonumber fields be negative? Any way for a PK,
autonumbered, *incremented* to be negative?

TIA
ron

[Bas Cost Budde]

Ron wrote:

Hi all

I'm deciding whether to use the PK also as an account number, invoice
number, transaction number, etc that the user will see for the respective
files. I understand that sometimes a number will be missing, which is not a
problem for my purposes I don't think, but how would it be negative? Can
randomly created PK autonumber fields be negative? Any way for a PK,
autonumbered, *incremented* to be negative?

With Jet sure it is possible. There is even a technical reason for this:
the high order bit of the binary representation is interpreted as the
number being negative.

Autonumbers are known to make frantic jumps now and then. You cannot
rely on them climbing steadily.

Using your own incremential number is not too difficult. You must
confine all actions on such a table to routes via your code (either via
function calls or through forms that do the calculation of the new number)

Suppose you have a table Invoice, PK=invID (long). Have this in the
BeforeInsert handler of a form based on this table:

me!invID=nz(dma x("invID","Invo ice"),0)+1

This will yield 1 if there is no record yet, and the next free number if
there are records.
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Tim Marshall]

Ron wrote:

Hi all

I'm deciding whether to use the PK also as an account number

Don't. Just don't. Never use the PK as a tracking number the user
sees. Always keep the PK/FK as numbers in the background that nobody
sees. You will always eventually run into a situation where someone
wants to apply a schema for account numbers, enter a new invoice number
system based on the direction his small toe points, whatever. If you're
using as a joining column/field data tht is being read, it becomes a
difficult process to apply such changes to previous records. If you use
a properly normalized approach, you change an account number....once.

--
Tim
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[tombsy]

i agree, I never use the autonumbers, it always ends in tears.
"Tim Marshall" <TI****@antarct ic.flowerpots> wrote in message
news:cn******** **@coranto.ucs. mun.ca...

Ron wrote:

Hi all

I'm deciding whether to use the PK also as an account number

Don't. Just don't. Never use the PK as a tracking number the user sees.
Always keep the PK/FK as numbers in the background that nobody sees. You
will always eventually run into a situation where someone wants to apply a
schema for account numbers, enter a new invoice number system based on the
direction his small toe points, whatever. If you're using as a joining
column/field data tht is being read, it becomes a difficult process to
apply such changes to previous records. If you use a properly normalized
approach, you change an account number....once.

--
Tim
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[Paul]

BE CAREFUL IF YOU CHOOSE TO USE me!invID=nz(dma x("invID","Invo ice"),0)+1.
If 2 users try to make an invoice at the same time, they will both get the
same ref number because their record has not yet been saved. Its better to
assign the number when you leave the record. This way it will look up the
number, assign it, save it and exit all at once. You could use a
docmd.runcomman d accmdsaverecord if you want, but I have seen some issues
with hard drive traffic that stops this command from occuring.

HTH
Paul

"Bas Cost Budde" <b.*********@he uvelqop.nl> wrote in message
news:cn******** **@news2.solcon .nl...

Ron wrote:

Hi all

I'm deciding whether to use the PK also as an account number, invoice
number, transaction number, etc that the user will see for the respective
files. I understand that sometimes a number will be missing, which is
not a
problem for my purposes I don't think, but how would it be negative? Can
randomly created PK autonumber fields be negative? Any way for a PK,
autonumbered, *incremented* to be negative?

With Jet sure it is possible. There is even a technical reason for this:
the high order bit of the binary representation is interpreted as the
number being negative.

Autonumbers are known to make frantic jumps now and then. You cannot rely
on them climbing steadily.

Using your own incremential number is not too difficult. You must confine
all actions on such a table to routes via your code (either via function
calls or through forms that do the calculation of the new number)

Suppose you have a table Invoice, PK=invID (long). Have this in the
BeforeInsert handler of a form based on this table:

me!invID=nz(dma x("invID","Invo ice"),0)+1

This will yield 1 if there is no record yet, and the next free number if
there are records.
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Ron]

Well, now I'm even *more* confused.

The first responder tells me the incremented autonumber field shouldn't be
used as a PK so gave me a nice routine to come up with my own PK. No prob.
However, you seem to be saying I shouldn't use the PK (I'd assume even the
one I come up with using this nice routine) as a user-visible account
number, invoice number, etc. So, then, I now need 2 numbers I come up with,
one to show the user and one to be the actual PK?

I feel like one of those lil' ducks at the arcade shooting gallery that
reverses directions with each direct hit. Boing, I'm going left, boing I'm
going right. Help! Somebody stop me!

ron

"Tim Marshall" <TI****@antarct ic.flowerpots> wrote in message
news:cn******** **@coranto.ucs. mun.ca...

Ron wrote:

Hi all

I'm deciding whether to use the PK also as an account number

Don't. Just don't. Never use the PK as a tracking number the user
sees. Always keep the PK/FK as numbers in the background that nobody
sees. You will always eventually run into a situation where someone
wants to apply a schema for account numbers, enter a new invoice number
system based on the direction his small toe points, whatever. If you're
using as a joining column/field data tht is being read, it becomes a
difficult process to apply such changes to previous records. If you use
a properly normalized approach, you change an account number....once.

--
Tim
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[Ron]

Thanks Paul, that helped A LOT! Hadn't even gotten to the
"multi-user"thoughts yet, but yeah, there could be 2 to 3 users (max) trying
to save to the same file at the same time.

Thinking about it, though, I'll have a detail subform on my invoice form.
As the details are stored into their table, I need that linking InvoiceID,
so I won't be able to wait till they leave the invoice form to assign the
InvoiceID.

Anything wrong with putting a file on the BE that a routine can access
sooner to pull a number off for the next number up (for whatever file is
being added to) and then store the number+1 figure back into that field for
the next user? System can lock that file, do the math, store the new number
and unlock the file all pretty quickly. Wouldn't that solve the number+1
problem and still allow for multiple users? (Just thinking out in the open,
here)

Sorry for all the probably really basic questions.

TIA
ron

"Paul" <pz****@rogers. com> wrote in message
news:Z4******** ************@ro gers.com...

BE CAREFUL IF YOU CHOOSE TO USE me!invID=nz(dma x("invID","Invo ice"),0)+1.
If 2 users try to make an invoice at the same time, they will both get the
same ref number because their record has not yet been saved. Its better to assign the number when you leave the record. This way it will look up the
number, assign it, save it and exit all at once. You could use a
docmd.runcomman d accmdsaverecord if you want, but I have seen some issues
with hard drive traffic that stops this command from occuring.

HTH
Paul

"Bas Cost Budde" <b.*********@he uvelqop.nl> wrote in message
news:cn******** **@news2.solcon .nl...

Ron wrote:

Hi all

I'm deciding whether to use the PK also as an account number, invoice
number, transaction number, etc that the user will see for the respective files. I understand that sometimes a number will be missing, which is
not a
problem for my purposes I don't think, but how would it be negative? Can randomly created PK autonumber fields be negative? Any way for a PK,
autonumbered, *incremented* to be negative?

With Jet sure it is possible. There is even a technical reason for this:
the high order bit of the binary representation is interpreted as the
number being negative.

Autonumbers are known to make frantic jumps now and then. You cannot rely on them climbing steadily.

Using your own incremential number is not too difficult. You must confine all actions on such a table to routes via your code (either via function
calls or through forms that do the calculation of the new number)

Suppose you have a table Invoice, PK=invID (long). Have this in the
BeforeInsert handler of a form based on this table:

me!invID=nz(dma x("invID","Invo ice"),0)+1

This will yield 1 if there is no record yet, and the next free number if
there are records.
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Trevor Best]

Ron wrote:

Well, now I'm even *more* confused.

The first responder tells me the incremented autonumber field shouldn't be
used as a PK so gave me a nice routine to come up with my own PK. No prob.
However, you seem to be saying I shouldn't use the PK (I'd assume even the
one I come up with using this nice routine) as a user-visible account
number, invoice number, etc. So, then, I now need 2 numbers I come up with,
one to show the user and one to be the actual PK?

I feel like one of those lil' ducks at the arcade shooting gallery that
reverses directions with each direct hit. Boing, I'm going left, boing I'm
going right. Help! Somebody stop me!

Roll your own number for the invoice number, have a unique index on it.
Use AutoNumber for the PK and relate all other table to that.

You can of course use your own rolled invoice number as the PK as this
is the candidate key for the record but there are drawbacks to doing
this, mostly to do with the number changing for some reason (usually
before the invoice is physically issued, then it should be frozen, set
in stone) lest you need to handle the dependant records or use cascades,
which IMHO are pretty hairy and invite major damage from a minor cock up.
--
This sig left intentionally blank

[Forrest]

> I'm deciding whether to use the PK also as an account number, invoice

number, transaction number, etc that the user will see for the respective
files. I understand that sometimes a number will be missing, which is not a
problem for my purposes I don't think, but how would it be negative? Can
randomly created PK autonumber fields be negative? Any way for a PK,
autonumbered, *incremented* to be negative?

If you ask for random numbers you can definately get negatives - this
should be half the time. If you ask for incremented numbers, you
would have to specifically set them up to be able to make negatives (
seed = -x ).

[Bas Cost Budde]

Paul wrote:

BE CAREFUL IF YOU CHOOSE TO USE me!invID=nz(dma x("invID","Invo ice"),0)+1.
If 2 users try to make an invoice at the same time, they will both get the
same ref number because their record has not yet been saved. Its better to
assign the number when you leave the record. This way it will look up the
number, assign it, save it and exit all at once. You could use a
docmd.runcomman d accmdsaverecord if you want, but I have seen some issues
with hard drive traffic that stops this command from occuring.

I agree. Should have mentioned that this is for single user.

My multi user approach is to maintain a keys table, where the next
available number is stored. (There can be keys for any table prone to
this kind of inserts; my fields are tableName(text) nextFree(number ))
The BeforeInsert handler of a form locks this table (so the next attempt
will fail, code should pause and retry), gets the number, increases it
and writes it back. That in itself is a short action, will not halt
other users too much (up to 20 simultaneous insert-requests) and has the
number available at the beginning of the process, which is sometimes
highly wanted.

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea