AutoNumber

[Brook]

Hi All:

I am setting up an invoiceing table with a form/subform.
My invoice numbers start with KI-then a number (I.E. KI-
100).

I want to set up an autonumbering sytem that will add a
one to the invoice. so the next number is KI-101.

Any help ...tips are greatly appreciated!

Brook

 

[sg]

http://techrepublic.com.com/5208-6239-0.html?forumID=44&threadID=140424
Hope this helps.
Sarah

 

[Tim Ferguson]

My invoice numbers start with KI-then a number (I.E. KI-
100).

I want to set up an autonumbering sytem that will add a
one to the invoice. so the next number is KI-101.

First of all, the techrepublic page is the wrong answer to a different
question.

Second: if the "KI-" part is always the same, then you don't need to store
it at all. Just set the Format property of the control on the form or
report to something like

"KI-"000

and Access will do the rest for you. If you set it in the Format property
of the field in the table, Access will pass it on to any new forms and
reports you make based on it.

Third: if the letter part changes sometimes, then you will need another
field to hold the variable part. Once again, it's easy to set the
controlsource of your textboxes to

=[VariablePart] & [SerialNumber]

so that your users still see what they expect.

Fourth: if you want to reset the SerialNumber every time you change the
VariablePart, then you can't use an autonumber any more. Ask here again if
you need more detail on that.

Hope that helps


Tim F

 

[tina]

that can be a more complex issue than it appears, depending on
circumstances. first, if you're including the KI in the invoice number
field, that field must be a text data type.
you can use a separate field for the KI, so that the invoice number itself
can be entered in a field with a numeric data type. but if you need the
invoice numbers to be absolutely sequential (i.e., 101, 102, 103) without
gaps, then you should NOT use an autonumber field. you can control what
number an autonumber field initially assigns, in the first record of the
table, but after that all you have no control. if a new record is started,
then deleted, Access still will not reuse that number, so you will
eventually have gaps in sequencing (i.e., 101, 102, 104, 107, 108, 109,
111).
you can use VBA to assign a number, based on adding 1 to the highest invoice
number currently in the table. however, if you have multiple users entering
invoice records at the same time, ensuring that the same number is not
assigned more than once can be very tricky - the issue has been discussed at
some length in this newsgroup in the past.
if you need specific guidelines/solutions, suggest you post more details of
your specific circumstances.

hth

 

[sg]

Tim,
I like your textboxes solution. I tried, the number shows ok, but I wonder
how I can store textbox value into this field.

Thanks,

My invoice numbers start with KI-then a number (I.E. KI-
100).

I want to set up an autonumbering sytem that will add a
one to the invoice. so the next number is KI-101.

First of all, the techrepublic page is the wrong answer to a different
question.

Second: if the "KI-" part is always the same, then you don't need to store
it at all. Just set the Format property of the control on the form or
report to something like

"KI-"000

and Access will do the rest for you. If you set it in the Format property
of the field in the table, Access will pass it on to any new forms and
reports you make based on it.

Third: if the letter part changes sometimes, then you will need another
field to hold the variable part. Once again, it's easy to set the
controlsource of your textboxes to

=[VariablePart] & [SerialNumber]

so that your users still see what they expect.

Fourth: if you want to reset the SerialNumber every time you change the
VariablePart, then you can't use an autonumber any more. Ask here again if
you need more detail on that.

Hope that helps


Tim F

 

[Tim Ferguson]

I like your textboxes solution. I tried, the number shows ok, but I
wonder how I can store textbox value into this field.

If you are going with solution (1), where the KI part never changes, then
there is no reason to store it ever. The only thing that matters is what
your users see (and their clients), and as long as they have the KI- right,
then what lives in the tables is nobody's business but yours.

HTH


Tim F

 

[sg]

I'm using third solution, in my table, I have tag# field, seq#
field(autonumber) and con field(company). In the textbox show [con]&[seq#],
I like store this value into field tag#. How can I do this? Should I create
a code on afterupdate event for textbox?
Thanks,

 

[Tim Ferguson]

, in my table, I have tag# field, seq#
field(autonumber) and con field(company).

Well: since the autonumber field is by definition unique, the ConField
column is not strictly neccessary. Still it's not going to do any harm.

In the textbox show [con]&[seq#],

Good, fine.

I like store this value into field tag#. How can I do
this?

Nonononononono -- that's the whole point. If you store the whole thing,
then it will get out of synch as soon as someone changes the ConField
value. In any case, sticking the two strings together in memory will be
birthdays faster than reading the extra stuff from disk. Whether you want
to look at it from the Second Normal Form point of view, or the O-level
computing aspect, it's what is called a Bad Idea. Don't Do It.

HTH


Tim F

 

[sg]

Thanks Tim, I understand now. The whole idea what I like to do is to print
those labels finally. But it's fine to use just the number.

Thanks again for explaining this.
Sarah

, in my table, I have tag# field, seq#
field(autonumber) and con field(company).

Well: since the autonumber field is by definition unique, the ConField
column is not strictly neccessary. Still it's not going to do any harm.

In the textbox show [con]&[seq#],

Good, fine.

I like store this value into field tag#. How can I do
this?

Nonononononono -- that's the whole point. If you store the whole thing,
then it will get out of synch as soon as someone changes the ConField
value. In any case, sticking the two strings together in memory will be
birthdays faster than reading the extra stuff from disk. Whether you want
to look at it from the Second Normal Form point of view, or the O-level
computing aspect, it's what is called a Bad Idea. Don't Do It.

HTH


Tim F

 

[Tim Ferguson]

The whole idea what I like to do is to print
those labels finally. But it's fine to use just the number.

I don't really understand. You can print anything you like on the labels
(or reports or forms or whatever). The idea is to base the label/ report/
form on a query, and then you can calculate whatever conglomeration of
fields you need: and you know they will always be correct and up to date
because they don't rely on some method of being updated on some other
fields.

Think about something like this:

SELECT TagNumber & ":" & SeqNumber AS FormattedSerialNumber,
FirstName & " " & LastName AS FullName
DateDiff("y", DateOfBirth, Date()) AS AgeInYears
etc_
FROM SomeTable
WHERE etc_


It's really quite normal for the user to see something quite different from
what is actually stored in the magnetic stripes on disk. Come to think of
it, even at the developer level, I neither know nor care how Access stores
the data or where, just as long as I can get it back by specifying a table,
a field and a PK value. So with the users; as long as they see the Serial
Number they are expecting, it does not matter to them how you choose to
code that into fields and tables.

Hope it makes it a bit clearer!
All the best


Tim F