Editable Auto Numbering

[GaryB]

I am posting a method for editable auto numbering a wrote in 2007 for Access. What I want to know is, would something like this be doable in excel. lets say for invoices. I would want auto number an invoice and be able to start a new one and have it number based on the number of the last invoice and also be able to call it up when needed. Here is what I did in Access. Any insight or help with this would be greatly appreciated. Thank you Gary

EDITABLE AUTO NUMBERING

Microsoft XP Professional Office2000 Access

WHAT IT DOES AND WHY!








There are times, when using autonumbering, that having the number editable would give us some freedom to customize each record to a particular need. The ability to be able to add a letter at the end of the number, like A, B or C or even change the record to a totally different number does add a benefit at certain times and for certain purposes. In my case we need to be able to number job folders with the same job number but add, as mentioned above an alphabetic letter after the number to create a numerical series such as 23189A, 23819B and 23819C to sequence a particular job. What this article proposes is a way to do this by using a simple work around from the autonumber field.


THE BASICS BEHIND THE IDEA

What is proposed is to create a text box that is populated from the autonumber field and at the same time is editable and will still automatically generate the next sequential number when creating a new record. This is accomplished in a relatively easy procedure and it is done within the confines of Access without having to go to a custom VBA code. By using the DMAX option these goals are obtained and you will find that the numbering does become editable as outlined in the following instructions. For this to work smoothly you will need a table, a select query, a form and a macro to open the form to a new record each time the form is opened. If you build the form with the option " record selectors" and "navigation buttons" set to yes it will allow you to view and copy & paste previous records into your new record and allows you the convenience of retrieving prior information in an instant.



STEP 1 . WHAT TO DO IN YOUR TABLE

In your table, in design view,



Create two fields, in this case I named them JOBID and JOBID2. If you already have a field already committed to autonumbering keep that field and make a new field for it to refer to in this case JOBID. The new field, that is to be editable needs, to be a text box with the other field set as autonumber. ( See Screen Shot below). If you already have autonumbering in your table this should not cause you to lose any of your prior numbering, but the editable numbering in the text field will apply to new numbers starting with your next new record. Note: the autonumber field is going to linked to the editable field using Dmax. Your next step will be to create a query.




STEP 2 . WHAT TO DO IN YOUR QUERY

To create a select query, linked to your table, open the database window and select Create query by using Wizard. This will allow you to create a query very simply by following the step by step instructions outlined by the wizard.



This next step allows you to select the fields you want in your query. Choose what you will need for your form, or, if you already have a query for your form add the editable text field to the existing query so you can add it to your existing form.




Personally I prefer to show every field of every record in my queries, but that is a choice you can make at this point.



Now name the query, usually something to remind you what it is for and what it is linked to. In my case I named the query jobfolderquery since the name of the form I built was called job folder.



In your query you will see the row called "Sort" Click on that in the column for the editable field, here it is JOBID, and choose ascending. This will allow the query to sort the form from the newest to the oldest record. By sorting this way you will always have your newest information at the front of the sort.




STEP 3. WHAT TO DO ON YOUR FORM.


On our form we have two text boxes JOBID and JOBID2. JOBID2 is set as for autonumbering or your original autonumber field and JOBID ( the editable text box) uses this formula in properties, default value.

=DMax("JOBID2 or your autonumber field)","name of table")+1

This allows you to edit JOBID to, let?s say, add and A, B, C at the end of the number or even a totally different number Then the next record will automatically pickup the next autonumber. On any report you might generate from this form you can choose to show or hide JOBID2, but, it does need to be somewhere on the report and in any report query you might make. It can be a hidden field but for the report to work correctly these fields need to be part of the report for the numbering to show.



An added note: In our case we allowed both the autonumber and the editable number to be visible as a way of double checking the accuracy of the numbering


STEP 4. WHAT TO DO IN YOUR MACRO

When you create your macro or in an existing macro to open the form to a new record please set GoToRecord select as new. This will automatically take you to a new form to enter data and when you do so it will automatically autonumber the form at which time you can edit, in this case, JOBID.




The results of this exercise, as you can see to the left, can be useful in many ways. In this example the autonumber remains constant and the Job# becomes editable. One thing to be watchful of is a job number can be duplicated. When you paste the information from an old record into a new record you need to be aware that the Job# needs to be changed to match the new number generated in the autonumber field. This only happens when you cut and paste. If you open a new record the next number will automatically be generated.








ADDENDUM. OTHER OPTIONS.

On my form I have a another table, customer info, linked in the query so that my records will show for each customer if I want to do a search. I have both tables joined by account number. This is an advisable way to enable limited searches of your records by account.





Also, on my form, in the account field, properties in the Event Column at the "on exit" line I have a macro that was made for the refresh command. On the form we use the jobID does not populate until an account number has been entered. When that happens it automatically refreshes the form and locks out another user from using the same JOBID#




Refresh Macro

[SamT]

would something like this be doable in excel. lets say for invoices. I would want auto number an invoice and be able to start a new one and have it number based on the number of the last invoice and also be able to call it up when needed

It's been done many times in Excel. The only significant difference between Excel and Access is that Access is a Data Storage device: In Excel, one needs to use something as a storage device. That can be a Worksheet, usually hidden or very hidden. It can be an (Invoice) Archive Folder of previous (Invoices) or even a Database. The choice of data store depends on the particular details of the situation.

I would only recommend Excel as an (Invoice) Number generator in a very few circumstances; As a Shared Master Number Generator where all (Invoice) creators Pulled the number from the Master: Where there was only one (Invoice) creator. Where all (Invoice) creators included a Creator_ID into the Number.

If the main concern is unique numbers, the following snippet has a very high probability of producing Unique values across even tens of stations. Obviously, the "Uniqueness" can be increased by making the exponent bigger. The first 5 digits of all numbers created on the same day will be the same, ( First 5 for about another 15 years, anyway.)

Num = CLng(Now * 10^5)

This can be converted to a Date/Time (In Microsoft VBA) by

$DT = Format(CDate(Num / 10^5), "dd/mm/yy h:m:s AM/PM")

[GaryB]

Hi Sam, I really appreciate the reply on this it has me for a loop I did create this in an Access Database File but as you said the problem here is storage. I am not even sure I need editable autonumber just autonumbering so I can label each transaction with its own number and be able to access it later. I do not write code but I have used it on several occasions with the help of members like your self. could you please kind of layout what I need to make this happen. Eternal gratitude will surely be there
Thanks Gary

PS The sample if it is not goofed up iwhat I am looking to auto number and be ablel to save each one separtely


Thanks again.

[snb]

I'd use a file in a directory that is accessible to several users:
If you run this macro several times you will notice the ascending numbering.
This macro is application independent; you can use it in Word, Excel, Access, Powerpoint, Outlook, CAD. Every application that can make use of VBA.
As you can see no file has to be opened to store the latest number, the name of the fiile is the storage.

Sub M_snb()
   c00 = "G:\OF\"
   
   y = Dir(c00 & "*._")
   If y = "" Then CreateObject("scripting.filesystemobject").createtextfile c00 & "0._"
   Name c00 & y As c00 & Val(y) + 1 & "._"
   MsgBox Val(y)
End Sub

[GaryB]

ok. It created an invoice number when I placed the code in the cell. But what do I do now to create a new invoice with a new number. Please excuse the stupid in this but like I said I am not an Excel anything.

Thanks Gary