If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Auto Numbering
I am creating a data base and want to add a formula to my table so that when
a form is filled out an automatic number is assigned to each new document. The starting number will be 05-001. Is there a formula I can write? Or is there another way to achieve this? |
#2
|
|||
|
|||
Have you tried searching for your answer? This is asked and answered
multiple times a day! Rick B "Craig B." Craig wrote in message ... I am creating a data base and want to add a formula to my table so that when a form is filled out an automatic number is assigned to each new document. The starting number will be 05-001. Is there a formula I can write? Or is there another way to achieve this? |
#3
|
|||
|
|||
Craig B. wrote:
I am creating a data base and want to add a formula to my table so that when a form is filled out an automatic number is assigned to each new document. The starting number will be 05-001. Is there a formula I can write? Or is there another way to achieve this? Assuming that the "05" portion is the year it would be best to store this in two fields. One that holds the record creation date [RecordDate], and one that holds the ordinal value [RecordID]. Then it becomes relatively easy (in a form) to assign the appropriate values. For RecordDate you simply set the default value property to either Date() or Now() depending on whether you want to capture the time as well as the date. I would recommend Now() because even if you don't think you need to know the exact time of record creation, that requirement could change down the road and you will already have it. This default value could be set at the table level. For RecordID you need a code routine that will find the highest existing ordinal value for the records created in the current year and then add one to it. I would use the BeforeUpdate event of the form with the following code... If IsNull(Me.RecordID) = True Then Me.RecordID = Nz(DMax("RecordID", "TableName", "Year(RecordDate) = Year(Date)"), 0) + 1 End If You need to make sure that RecordID has no default setting in either the table design or the form. Now, *for display* on your forms and reports you use an expression of... =Format(RecordDate,"yy-") & Format(RecordID,"000") -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com |
#4
|
|||
|
|||
Ok, how do you USE a BeforeUpdate event? I see the before update line when I
look at the code. How do I use it? "Rick Brandt" wrote: Craig B. wrote: I am creating a data base and want to add a formula to my table so that when a form is filled out an automatic number is assigned to each new document. The starting number will be 05-001. Is there a formula I can write? Or is there another way to achieve this? Assuming that the "05" portion is the year it would be best to store this in two fields. One that holds the record creation date [RecordDate], and one that holds the ordinal value [RecordID]. Then it becomes relatively easy (in a form) to assign the appropriate values. For RecordDate you simply set the default value property to either Date() or Now() depending on whether you want to capture the time as well as the date. I would recommend Now() because even if you don't think you need to know the exact time of record creation, that requirement could change down the road and you will already have it. This default value could be set at the table level. For RecordID you need a code routine that will find the highest existing ordinal value for the records created in the current year and then add one to it. I would use the BeforeUpdate event of the form with the following code... If IsNull(Me.RecordID) = True Then Me.RecordID = Nz(DMax("RecordID", "TableName", "Year(RecordDate) = Year(Date)"), 0) + 1 End If You need to make sure that RecordID has no default setting in either the table design or the form. Now, *for display* on your forms and reports you use an expression of... =Format(RecordDate,"yy-") & Format(RecordID,"000") -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com |
#5
|
|||
|
|||
Scott Schindler wrote:
Ok, how do you USE a BeforeUpdate event? I see the before update line when I look at the code. How do I use it? "Rick Brandt" wrote: You find the BeforeUpdate box in the [Events] tab of the property sheet and enter "[Event Procedure]" in that box (without the quotes). Then you click on the build button [...] to the right. That will take you to the VBA code editor window. Access will have already created the lines that define the beginning and end of the procedure. You simply place your code between those lines. You have to make sure before doing the above that the property sheet is displaying properties for the Form object and not for some control on the form or some section of the form. Click on the small gray sqaure in the upper right of the form to ensure this. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com |
#6
|
|||
|
|||
Can anyone tell me if it is possible to insert auto numbering into an Excel
spreadsheet? I know this is possible with Access, however the people that employ me do not feel that Access is neccessary at this time. "Rick B" wrote: Have you tried searching for your answer? This is asked and answered multiple times a day! Rick B "Craig B." Craig wrote in message ... I am creating a data base and want to add a formula to my table so that when a form is filled out an automatic number is assigned to each new document. The starting number will be 05-001. Is there a formula I can write? Or is there another way to achieve this? |
#7
|
|||
|
|||
In Excel, you can do this by applying a custom number format to the
column. This should work: "005-"000;; Then type 1 in the first cell (e.g. A2) and use a formula to increment it in subsequent cells (e.g. =A2+1). On Fri, 28 Jan 2005 13:31:09 -0800, "Kevin" wrote: Can anyone tell me if it is possible to insert auto numbering into an Excel spreadsheet? I know this is possible with Access, however the people that employ me do not feel that Access is neccessary at this time. "Rick B" wrote: Have you tried searching for your answer? This is asked and answered multiple times a day! Rick B "Craig B." Craig wrote in message ... I am creating a data base and want to add a formula to my table so that when a form is filled out an automatic number is assigned to each new document. The starting number will be 05-001. Is there a formula I can write? Or is there another way to achieve this? -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Need Help in numbering cells / auto fill | BostonBeaner | General Discussion | 5 | October 18th, 2004 10:01 PM |
WORD Auto Numbering | Jeff | Page Layout | 1 | September 4th, 2004 10:57 PM |
Losing numbers on pre-formatted styles | JulieG | General Discussion | 5 | September 1st, 2004 04:38 PM |
Auto Numbering revisited | Keith | Worksheet Functions | 2 | April 13th, 2004 07:53 PM |
P.O. Auto Numbering | Trevor Shuttleworth | Worksheet Functions | 1 | March 23rd, 2004 06:57 PM |