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

[Edward]

"Ron" <ro************ *******@earthli nk.com> wrote in message news:<I5******* ***********@new sread1.news.pas .earthlink.net> ...

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?

You have no control over the Autonumber field. You can reset it by
clearing out the table and compressing the database. But negative?
No. However, it is not that hard to roll your own PK, which is what I
would do in your case.

Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk

[Tim Marshall]

Ron wrote:

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

Hi Ron,

What I'm telling you applies to use of a primary key. In the
application you have described, the basic structure should include a
table for invoice header information and a second table for invoice line
items. It sounds as if you are planning to join the two tables in
queries by use of the invoice table's primary key - good - which you
are planing to make the invoice number - bad.

So I assumed you are using the primary key as a menas to link tables.

It truly is a good rule of thumb to have the means by which you link
tables together to be absolutey meaningless. That way you and your
users can happily tinker away with whatever scheme you have for
information. There are some instances when a developer might be tempted
to use actul real information as a PK and the one that comes to my mind
that is often used is an account number or component(s) of an account
number for multi-part account number set ups. How often is an account
number ever going to change?

Well, even bean counters screw up and decide an account number has to
change from time to time. I run a maintenance system which is fed
account numbers from a larger financial system and the maintenance
system populates account fields in various tables with an actual account
number to avoid the pain of having to associate the extra account
table(s) via a "meaningles s" system number in putting together SQL
statements. It always seemd to me to be a reasonable step until 2 years
ago when our university bean counters decided a coule of account codes
had to change. It was a hideous exercise trying to worm through all the
tables in our maintenance app (it does a lot of stuff and there are a
couple of hundred Oracle tables rife with account number information)
and change all these.

Having a link via a meaningless system generated number for the account
PK and linking the account table would have meant the account number
would needed to have been changed once, and once only.

So yes, I would strongly recommend you come up with two numbers to
generate - an autonumber for a PK and an autonumber or some other means
of generating a sequence for the invoice number.
--
Tim
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[paii, Ron]

I have a table on the BE containing the next available sequence number for
PO, Invoice ect. Use a function of get the next value, increment and write
out the next number to the BE table. Always have the function verify the
number does not exit before returning. Otherwise users could still get
duplicate values. Also check that the number sequence does not restart.

"Ron" <ro************ *******@earthli nk.com> wrote in message
news:Zx******** *********@newsr ead3.news.pas.e arthlink.net...

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

[Carlos Nunes-Ueno]

The recommendation that PKs be kept hidden from users stems from the
need for a PK to be a static value for each row in each table. If you
have a separate public identifier from the PK, then you can change
those identifiers at any time as needs be. If you're using the PK as
the identifier, then it really can't be changed without a whole mess
of updates and other pain and suffering.

So the question to ask yourself is "Will an
account/invoice/transaction number ever need to be changed?" It all
comes down to this issue. If the answer is a no, then you're okay
using a PK (either AutoNumber or roll-your-own) as an identifier. If
it's a yes, then you need to hide the PK and create another user
visible identifier. If you're not certain, the safer way to go is to
assume yes. Therefore many people consider it a best practice to hide
all PKs and create their own user visible identifiers.

Hope that helps,

Carlos

"Ron" <ro************ *******@earthli nk.com> wrote in message news:<j%******* ************@ne wsread1.news.pa s.earthlink.net >...

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