GaryB
03-21-2020, 11:35 PM
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,
http://www.vbaexpress.com/members/pic1.jpg
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.
http://www.vbaexpress.com/members/pic2.jpg
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.
http://www.vbaexpress.com/members/pic8.jpg
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.
http://www.vbaexpress.com/members/pic9.jpg
Personally I prefer to show every field of every record in my queries, but that is a choice you can make at this point.
http://www.vbaexpress.com/members/pic10.jpg
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.
http://www.vbaexpress.com/members/pic11.jpg
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.
http://www.vbaexpress.com/members/pic12.jpg
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.
http://www.vbaexpress.com/members/pic3.jpg
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.
http://www.vbaexpress.com/members/pic4.jpg
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.
http://www.vbaexpress.com/members/pic5.jpg
http://www.vbaexpress.com/members/pic6.jpg
http://www.vbaexpress.com/members/pic7.jpg
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.
http://www.vbaexpress.com/members/pic13.jpg
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#
http://www.vbaexpress.com/members/pic14.jpg
Refresh Macro
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,
http://www.vbaexpress.com/members/pic1.jpg
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.
http://www.vbaexpress.com/members/pic2.jpg
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.
http://www.vbaexpress.com/members/pic8.jpg
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.
http://www.vbaexpress.com/members/pic9.jpg
Personally I prefer to show every field of every record in my queries, but that is a choice you can make at this point.
http://www.vbaexpress.com/members/pic10.jpg
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.
http://www.vbaexpress.com/members/pic11.jpg
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.
http://www.vbaexpress.com/members/pic12.jpg
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.
http://www.vbaexpress.com/members/pic3.jpg
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.
http://www.vbaexpress.com/members/pic4.jpg
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.
http://www.vbaexpress.com/members/pic5.jpg
http://www.vbaexpress.com/members/pic6.jpg
http://www.vbaexpress.com/members/pic7.jpg
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.
http://www.vbaexpress.com/members/pic13.jpg
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#
http://www.vbaexpress.com/members/pic14.jpg
Refresh Macro