Setting Invoice Numbers to Start At .....

[terry]

I have a series of invoices in a table which are up to 35000 at present. I would like to re-start using invoice numbers which start at 40,000. I have done this before but can't remember how. Any ideas.

 

[Rabbie]

Use the DMAX function to find the curren highest invoice number and Add 1 to it.

 

[terry]

I don't see how this resolves the problem.
The current invoice numbers work on an autonumber and have reached 35,000. I want the next invoices to start at 40,000 and increment by one each time from this point.

Terry

 

[gemma-the-husky]

i would have another table to store the next invoice number

read the invoice number from there, and increment it each time.

thats the best (only?) way to have any sequence other than continuous numbers.

 

[Simon_MT]

Put in a dummy Invoice 39999 and use Dmax + 1

Simon

 

[Mr. B]

Terry,

I have to assume that you are currently creating the next invoice number in some manner by incrementing your maximum number by one. I am assuming this because I am hoping that you are not trying to rely on an auto number to do this for you.

I would also guess that you are doing this with VBA code. I would suggest that you use some additional code at the point where you are incrementing the invoice number to check to see if the current number is less than your 40,000 number. if is is less, then make the next number 40,000. This code would only work once but it would accomplish the desired action.

 

[SOS]

Autonumbers are really good for one thing - Surrogate Primary Key. Other than that they are unreliable for anything because they really only guarantee to give you a UNIQUE number (and even then it falls down sometimes). It is not guaranteed to increment or stay in a certain range. In fact, it isn't even guaranteed to give you a positive number. The fact of the matter is, while it normally does increment and it normally does give you a positive number, it isn't necessarily going to always do so.

There have been plenty who post on the forums that they all of a sudden have an autonumber which has skipped hundreds or thousands of numbers and is now incrementing from those. Or those who have been uneducated to what happens when they wind up using replication and find out that they have now got numbers jumping all over the place and including negative numbers.

So, my advice to you is to change your scheme (if you MUST have a certain numbering sequence) and do NOT rely on autonumbering to do it for you. It will eventually fail (or at least the odds are great that it will) and you, or someone who follows you, will be left to pick up the pieces.

Take the DMax + 1 advice here (and you might look to use the single field table method to keep from running into issues with multiple users).

 

[gemma-the-husky]

to repeat - if you want the flexibilty of being able to change the number sequence form time to time - you will have to get the first number from outside the table

either enter it manually at the start of the invoicing process - or pick it up from a separate table.

 

[terry]

Thanks for all your advice and particularly about the autonumber which I am currently relying on to to increment by one each invoice. I will have a look at the DMax option although I am not a VBA programmer and may have to reve3rt back.

Thanks again,

Terry

 

[terry]

Thanks for all your advice and particularly about the autonumber which I am currently relying on to to increment by one each invoice. I will have a look at the DMax option although I am not a VBA programmer and may have to revert back.

Thanks again,

Terry

 

[smig]

create a simple code that will add dummy invoices till you get to 39,999.
delete all these dummys.
next one will be 40,000

Access keep the number you ued and won't repeat it again even if you delete the record.

 

[Mr. B]

As has been stated before, if you want a truly reliable incrementing invoice numbering scheme, you must not use the AutoNumber method your have been using. I was afraid from your original post that you were using the AutoNumber for this purpose.

You will need to use VBA code to accomplish your goal, but if you really want to be a serious Access developer you will eventually have to start your learning curve in VBA coding. It is not rocket science but will require some effort.

If as you start to try to resove your problems, simple post back here and someone will make every effort to assist you, even with VBA.

 

[terry]

Thanks for the advice Mr B. I will start looking at learning some VBA code. I have managed to achieve the required result by creating an update query to take the 39999 value from a secondary table.

Nevertheless this is obviously the wrong choice as stated in your comments. Can you advise on how to use the DMax function or the code necessary to make the thing work.

Regards

Terry

 

[dfenton]

create a simple code that will add dummy invoices till you get to 39,999.
delete all these dummys.
next one will be 40,000

You actually need to add only one record, with the value for the Autonumber field of 39,000, and then delete that one record.

Many people think you can't append a value to an Autonumber field, but that is not correct at all. An Autonumber is really little more than a long integer field with a special default value that can increment or choose a random value.

So, all you have to do is:

INSERT INTO MyTable (MyAutonumberField)
VALUES (39999)

...and then:

DELETE * FROM MyTable
WHERE MyAutonumberField = 39999

After executing those two SQL statements, the next Autonumber value with be 40,000 (assuming the Autonumber is set for Increment and there are no corruption issues with the seed value).

 

[Mr. B]

Terry,

While David's information about setting a value to an AutoNumber field is correct, his solution still indicates an intention of continuing to use the AutoNumber field as the invoice number. I must continue to maintain my position about using this type of field for this. I believe that it will eventually lead to problems.

I am sure that others here may have other ways of managing an invoice numbering scheme, but here is one way that it can be done:

I have found that in many of my applications, I will have various things like default values and default paths, etc. that I what to allow users to define while they are using the application. I will typically store these values in a table that I name "AppValues". This table can then have any number of fields but will never have more than one row or set of values. It is in this table that I would create a field in which to store my seed value to be used in creating the next invoice number. I might call this field "InvSeedVal" and make it either a string type field (if I intended to have any alpha characters in my invoice numbering scheme) or a numeric, long integer, type field if the invoice numbers were only going to have number type characters.

For my example I am going to use the numeric, long integer, type field.

To use the code below will require that you add a reference to the Microsoft DAO 3.6 Object Library (or the version number of the DAO Object Library that is currently available on your computer). To set this reference, open the Visual Basic code window and then select "References" from the "Tools" option on the menu. Then locate the "Microsoft DAO 3.6 Object Library" option and place a check in the check box at the left end of that option and click Ok button to close the Reference window.

The VBA code to manage the invoice number would go something like this:


'define a variable to hold the current invoice seed value
dim lngInvSeedVal as Integer

'define a vairiable to hold the next invoice value
dim lngNxtInvVal as Integer

'define a record set variable
dim rs as DAO Recordset

'open a recordset against the "AppValues" table
set rs = currentdb.OpenRecordset("AppValues")

'read the value from the "InvSeedVal" field in the "AppValues" table
'and store the exising value in a variable
lngInvSeedVal = rs.Fields("InvSeedVal").value

'increment the existing value by one to create the next Invoice number
lngNxtInvVal = lngInvSeedVal + 1

'update the "InvSeedVal" field in the "AppValues" table with the most recent invoice number
'open the record set for editing
rs.edit
'assign the new value to the field
rs.field("InvSeedVal").value = lngNxtInvVal
'save the new value in the table
rs.update
'close the recordset
rs.close
set rs = nothing

Now you can use the value in the "lngNxtInvVal" variable anywhere you desire, including assigning it to a text box as the new Invoice number.

Hope this helps.

 

[gemma-the-husky]

Terry,

While David's information about setting a value to an AutoNumber field is correct, his solution still indicates an intention of continuing to use the AutoNumber field as the invoice number. I must continue to maintain my position about using this type of field for this. I believe that it will eventually lead to problems.

I am sure that others here may have other ways of managing an invoice numbering scheme, but here is one way that it can be done:

I have found that in many of my applications, I will have various things like default values and default paths, etc. that I what to allow users to define while they are using the application. I will typically store these values in a table that I name "AppValues". This table can then have any number of fields but will never have more than one row or set of values. It is in this table that I would create a field in which to store my seed value to be used in creating the next invoice number. I might call this field "InvSeedVal" and make it either a string type field (if I intended to have any alpha characters in my invoice numbering scheme) or a numeric, long integer, type field if the invoice numbers were only going to have number type characters.

For my example I am going to use the numeric, long integer, type field.

To use the code below will require that you add a reference to the Microsoft DAO 3.6 Object Library (or the version number of the DAO Object Library that is currently available on your computer). To set this reference, open the Visual Basic code window and then select "References" from the "Tools" option on the menu. Then locate the "Microsoft DAO 3.6 Object Library" option and place a check in the check box at the left end of that option and click Ok button to close the Reference window.

The VBA code to manage the invoice number would go something like this:


'define a variable to hold the current invoice seed value
dim lngInvSeedVal as Integer

'define a vairiable to hold the next invoice value
dim lngNxtInvVal as Integer

'define a record set variable
dim rs as DAO Recordset

'open a recordset against the "AppValues" table
set rs = currentdb.OpenRecordset("AppValues")

'read the value from the "InvSeedVal" field in the "AppValues" table
'and store the exising value in a variable
lngInvSeedVal = rs.Fields("InvSeedVal").value

'increment the existing value by one to create the next Invoice number
lngNxtInvVal = lngInvSeedVal + 1

'update the "InvSeedVal" field in the "AppValues" table with the most recent invoice number
'open the record set for editing
rs.edit
'assign the new value to the field
rs.field("InvSeedVal").value = lngNxtInvVal
'save the new value in the table
rs.update
'close the recordset
rs.close
set rs = nothing

Now you can use the value in the "lngNxtInvVal" variable anywhere you desire, including assigning it to a text box as the new Invoice number.

Hope this helps.

This is easily the most flexible solution. It lets you store a central record of sequence numbers. it even lets you sequence things that arent necessarily recorded anywhere else in your database - eg, a sequence number for a report, or an output file.

Some authorities actually recommend storing these tables as unlinked tables in the backend only, and writing/reading them with functions that lock the table to prevent multiple users corrupting the sequence. I think this may be overkill, but it demonstrates how critical the issue is, of controlling numbering sequences.

 

[dfenton]

While David's information about setting a value to an AutoNumber field is correct, his solution still indicates an intention of continuing to use the AutoNumber field as the invoice number.

I didn't bother to say anything about the inadvisability of it for an invoice number because that would have been repetitious -- others had already explained why it's not a good idea.

But there are cases where you may very well need to do what I outlined for legitimate reasons, and so I felt it was important to post those instructions.