field in table auto increment

[smile]

I have a table with order numbers I would like to auto increment the number part of the order when new record is added to the table.

My order numbers looks like this:

UZSK-0001
UZSK-0002
UZSK-0003
UZSK-0002

How to make access increment the number part?

 

[The_Doc_Man]

You have a couple of choices. First, is that text part absolutely constant? If so, don't ever store it as a table field at all. You put constants in label boxes on forms or reports.

Second, if it CAN change, you probably don't want autonumber for this field anyway. Search the forum for "AUTONUMBER" topics - then look at the list to see where the title indicates some special formatting of the number. You'll see what was given as answers for those threads.

If you really want "unique autonumber no matter what the prefix" you can do that - but if you want "unique different thread of autonumbers per prefix value" (I.e. GMSB-0001 and EXEG-0001 can both exist; you can have two 0001's if the first parts are different) then you definitely DO NOT want autonumber.

 

[smile]

You have a couple of choices. First, is that text part absolutely constant? If so, don't ever store it as a table field at all. You put constants in label boxes on forms or reports.

Second, if it CAN change, you probably don't want autonumber for this field anyway. Search the forum for "AUTONUMBER" topics - then look at the list to see where the title indicates some special formatting of the number. You'll see what was given as answers for those threads.

If you really want "unique autonumber no matter what the prefix" you can do that - but if you want "unique different thread of autonumbers per prefix value" (I.e. GMSB-0001 and EXEG-0001 can both exist; you can have two 0001's if the first parts are different) then you definitely DO NOT want autonumber.

Well then how do I make when I add new record it is duplicated from previous record and incremented by one? Also automatically enter todays date.

 

[GaryPanic]

ok check Alex01 post- i have answered and also given a sample d/b how this is done excpet the date and thats a doddle ..

gary

 

[GaryPanic]

heres the d/b

you need the table nextnumtbl
and look at the code behind the add new record button

Access2000

the date field is = now()

hth

 

[smile]

heres the d/b

you need the table nextnumtbl
and look at the code behind the add new record button

Access2000

the date field is = now()

hth

Thanks for your help, but I have problem:

If I enter some records and number is incremented by one like

100
101
102
103
104

Then if I delete some record like "104"

Next record is entered like

105
106 etc. But I need to check what is the last record and enter new incremented by 1, that would be 104. It should be able to override this automation if a record like 102 was deleted and new record is suggeted 112 the operator should be able to write 102 insted of 112.

This is just some automation I would like to be done because it decreases errors and makes more easy useage. Thanks

 

[smile]

Perhaps someone can modify the code here
http://support.microsoft.com/Default.aspx?id=210236

To increment the data by one ?

 

[smile]

Perhaps someone can modify the code here
http://support.microsoft.com/Default.aspx?id=210236

To increment the data by one ?

found a solution here:http://support.microsoft.com/kb/209830
Now need some help to modify the code I posted my question at VBA section if can help please do.

http://www.access-programmers.co.uk/forums/showthread.php?p=674257#post674257

 

[gemma-the-husky]

the think is autonumbers/keys are generally there to provide a unique key, and not really to provide an intact sequence

it would probably be quite unusual to reuse deleted index numbers. what would be more usual is instead of deleting the item, to flag it as deleted/not required, but keep it in the fiel, thereby retaining the sequence intact

 

[smile]

the think is autonumbers/keys are generally there to provide a unique key, and not really to provide an intact sequence

it would probably be quite unusual to reuse deleted index numbers. what would be more usual is instead of deleting the item, to flag it as deleted/not required, but keep it in the fiel, thereby retaining the sequence intact

You understood me wrong I need to increment other field in table not autonumber

 

[GaryPanic]

you are doing something wrong here .

you ask for a squenceial number 101,102,103,104,105 -106 etc

now if you delete 102 you are asking the number system to find a space and reuse it - thats not making much sense - there would be very little relationship value in doing this

reasoning behind this


you run a report on 102 in one month , then you delete 102 and reuse the number and run the same report - it has no meaning

example
102 = chocolate bars

you delete 102 - now replace it with carrots

your report asks how many 102 did you use in 1 month

1 report done in differnet months gives a mix/match result in the first month you are asking about chocolate and the next carrots- there is no logic behind this

now there may be a reason for doing this - but once a number is issued then really you should use the next one -

to have the ability to over type a number is do-able but will play havoc with numbering, how would you know that number 102 has been deleted when you are on record 546?? - you are playing into a real nightmare area...

there may be a good reason for what you are trying to do - but you are going against the grain of what most programmers would expect-design or recommend -

my sample d/b does give you the abilty to set numbering systems up
you coudl start the numbering up at 200800001 and it wll count onwards
using 2008 as a year and adding enough 00000 to ensure that you would not fall over in numbering give 99,999 numbers then you reset it to 200900001
for each year - this gives a certain amount of flexibility ..(the numbering d/base was put together by me - but the coding wasn't -if you get my meaning --gp)

 

[smile]

Well I'm just making some invoice billing system here.

If a client purchases some goods like:

1 chocolate
2 carrots

etc.....

All items using relationships are bound to an invoice with a unique number like INVOICE-006

I use auto number field in my invoice table
Then I use actual Invoice number that must start at 1 and go like 2,3,4,5 etc
Now I need to increase invoice number on every purchase and auto generating last max number is key here.

I can't use access auto number because it be like

1
2
3
then I delete 3
Access write 4
5
6
Then I have no way to override it to write 3 instead of 7 then 8 etc.

if the client does not pay for some pre-set period of time (like 10 days). I using a query can find that client and instead to go and delete from purchases table every product etc. I just go to invoice table delete invoice INVOICE-006 and when new client makes a purchase I override default invoice numbering script and write INVOICE-006.

The point of having auto number for invoice is not to loose numbering and to be able to override when needed. You can't override access default auto number and numbering is not straight numbering it will not write value again if you deleted it means you can't use it as lookup in another table.

 

[gemma-the-husky]

if you are looking to generate sequential (EG INVOICE) numbers, then you should read the next number from a constants table, then increment it in the table.

to go back to your point though, it sounds like you are thinking of deleting invoices in certain cases, and reusing the gap in the sequence.

I think this is where it becomes difficult. if you raise an invoice, and change your mind, you should really issue a credit. Its really really awkward to try to maintain an intact sequence, and if you do fill gaps, you will have dates out of sequence

 

[GaryPanic]

Gemma - has hit this on the head

the sample I posted up I use for invoices
if you are going to cancel a invoice then you need a credit system

-
I presume that you have a history table

what I mean by this is that you invocie account Jones
and all of the transaction goes into a history table now you need to grab the invocie you wish to cancel and times by -1 and then append this into your histroy table

you would end up with


debit note number 12345 for 100.00

credit note number 15555 for -100.00

and then run a statement fromt he history table both transactions would show up but your net amount due would be 0.00

now as to settling - I use the following method


I have a list of invocies and I select the one I want to say paided and date stamp it with the date that has been paid - now I rn a qry off asking show whee paiddate is null - this lists only the ones with nil dates in it - which will be the unpaid ones -

credits that cancel out a debit I settle on the same day .


now we need to know how your tables are set up to give you some proper insight on how to use invoices

 

[smile]

Yes I'm trying to keep intact sequence. The dates will be fine I think because each invoice needs to have a date when order took place.


ID
1 invoice 1 will have date 2008-01-01
2 invoice 2 will have date 2008-01-10
3 invoice 3 will have date 2008-01-15
5 invoice 4 will have date 2008-02-01 (because it was deleted)
6 invoice 5 will have date 2008-01-20 (because next order took place)
7 invoice 6 will have date 2008-01-30 (because next order took place)
8 invoice 7 will have date 2008-02-10
9 invoice 8 will have date 2008-02-15

When I have to make report on income for a month I pull a query based on invoice ID as a lookup in my purchased items table not by date.

2008-01 will be: invoices: 1;2;3;5;6;
2008-02 will be: invoices: 4;7;8;

If I wan't to look at invoices by dates I get:

2008-01 invoices: 1;2;3;5;6
2008-02 invoices: 4;7;8;

So I do not understand how I can have dates out of sequence

When I sell items in my purchased items page I by lookup assign each item an invoice ID using lookup from invoices table, I also assign summary ID from summary table.

Then When I need a report summary for income for a month I pull a query and enter "summary ID" then same "summary ID" for subreport for invoices.
I hate to do it twice but nobody have shown me how to make access format data in columns by VBA and not subreport. Subreport even request to enter criteria when you click print button even when it shows print preview with all the data. It's stupid for access to do it.

I do not use Double-Entry Accounting with debit and credit, I use simple Single-Entry Accounting.

Even if I do keep all invoices without deleting invoice numbers in the summary will be out of sequence because some unpaid, damaged etc, invoices will be excluded from list in summary for period pulled by query bu dates will be fine no?

Any suggestions are welcome.

 

[gemma-the-husky]

obviously you can sort the data however you want to filter reports etc

the point is that in your example invoice 4 is dated before invoices 5 and 6

if thats what you want, then you can programme it that way - but filling gaps in sequences is not easy. it means when you need an invoice number you have to start at the bottom and check every number until you find a gap - thats slow and its not the easiest way to do it.

as i say normally you would not delete invoices, you would mark them to show they were cancelled, so you NEVER create sequence gaps

clearly you dont have to be audited, so it isnt an issue from that point of view. if you are VAT registered it still might be an issue. I am sure VAT inspectors would be bothered about unusual date sequences etc

 

[Rich]

if the client does not pay for some pre-set period of time (like 10 days). I using a query can find that client and instead to go and delete from purchases table every product etc. I just go to invoice table delete invoice INVOICE-006 and when new client makes a purchase I override default invoice numbering script and write INVOICE-006.

So you're suggesting that if somebody hasn't payed then having had the goods you can simply delete the record, a strange accounting ethos

 

[smile]

So you're suggesting that if somebody hasn't payed then having had the goods you can simply delete the record, a strange accounting ethos

I was talking about not phisical goods, about a service. This DB should be used with online services. If someone orders service but fails to pay for it you do not need to keep his record in DB to begin with. Otherwise the DB will get larger and larger.