Sequential Numbering

A common question I’ve received has to do with sequential numbering. People often want to use some identifier for their records that involves information about the record and a sequential number. Sometimes these numbers are sequential throughout the table, other times the sequence is specific to other criteria.

To illustrate how to do this, I’m going to use the following scenarios;

1)      Acme Co. needs to sequentially number their Purchase Orders to conform to standard accounting rules.

2)      Apex Inc. wants to sequentially number inquiries they receive but within each year.

3)      Hollywood & Vine, a law firm, wants to tag all documents they receive with a client ID, case number and then number them sequentially by case.

There are three issues common to all three scenarios. First, the sequential number is stored separately in the table as an Integer datatype (might need Long Integer). Second, the DMax function is used to  determine the last number used. Third, the number needs to be generated immediately prior to saving  the record.

One other caveat here; the number generated in these scenarios is NOT a primary key. These tables  should use an Autonumber as the PK. The possible exception would be scenario 1, but I would still tend to use an Autonumber as the PK.

We’ll start with the simplest, scenario 1. Acme will use a straight sequential number for its Purchase Order numbers. Add a (Long?) Integer field to the table named PONum. Add a control to your Purchase Order form bound to that field. Then use the following expression to populate that control:

Me.txtPONum = Nz(DMax(“[PONum]”,”tblPO”),0)+1

The Nz function will return 0 if there are no records so this expression will return a 1 the first time and then increment the highest number each additional time. The key is where to put this line of code. As I said it needs to be generated immediately prior to saving the record so that another user does not get the same number. So generally I would put this behind a Save button or a button to specifically generate the number. I would then add a second line to commit the records. Either:

Me.Dirty = False

or

DoCmd.RunCommand acCmdSaveRecord

Next we will look at scenario 2. The variation here is that Apex wants to start each year with anew sequence of numbers. Also, they want to include the year when displaying this number. We still use a DMax, but we need to add criteria to determine the year. So Apex needs to have a field in their table that indicates the date. Generally, such inquiries will have a date/time stamp to indicate when received or the user will input the date and time. So we will assume there is a field in record called InquiryDate. We will also add an Integer field named Sequence. On the form we will add a control bound to Sequence, but set its Visible property to No. The line of code will look like this:

Me.txtSequence = Nz(DMax(“[Sequence]”, ”tblInquiry”, ”Year([InquiryDate]) = “ &
Year(Me.[txtInquiryDate])),0)+1

With this expression we check only for records that are in the same year as the Inquiry date to increment the sequence. Again, the NZ function will automatically restart the sequence with each year. Cleary, though, we cannot generate this number until the InquiryDate has been entered. Is this value is a user entry, then you may want to check that it’s filled in before generating the number. Something like:

If IsNull(Me.txtInquiryDate) Then

            MsgBox “Inquiry Date must be entered first!”, vbOKOnly

            Me.txtInquiryDate.SetFocus

Else

Me.txtSequence = Nz(DMax(“[Sequence]”, ”tblInquiry”, ”Year([InquiryDate]) = “ &
Year(Me.[txtInquiryDate])),0)+1

End If

Also again, you want to generate this number immediately before saving the record.

The final piece is to display the identifier. For that you use the following expression:

=Format([InquiryDate],”yyyy”) & “-“ & Format([Sequence],”0000”)

This expression can be used anywhere you want to display this identifier. It will appear like this:

2009-0010,
indicating the tenth inquiry of 2009. You can use that as the Controlsource of a control on a form or report. In a query you would preface with an Alias rather than the equals sign (i.e.; Inquiry Code:).

The final scenario is very similar to Scenario 2. The two main differences are in the criteria of the DMax and the expression to display the identifier. The document table already has two fields to identify a
document; ClientCode, which is a 5 character code that identifies the client and CaseNumber, which is an alpha-numeric code assigned to the case. Since the Case Number is unique to the Client, we only need to set the criteria in the DMax for the case number. We will also need to add an Integer field named
Sequence to the table. So the Dmax expression will look like this:

Me.txtSequence = Nz(DMax(“[Sequence]”, ”tblDocument”, ” [CaseNumber] = ‘“ & Me.[txtCaseNumber]
& “’”),0)+1

Notice here that we need to surround CaseNumber with Single quotes since it’s a Text datatype. As in the other scenarios, The NZ function automatically restarts the sequence when a new CaseNumber is used. In this scenario we would want to check whether the CaseNumber has been entered before generating the sequence.

For displaying this identifier, we would use an expression like:

=[ClientCode] & “/“ & [CaseNumber] & “-“ & Format([Sequence],”000”)

In this scenario we are assuming that there will be no more than 999 documents attached to a case. In Scenario 2 we assumed no more than 9999 inquires during a year. So you need to adjust the number of zeros when formatting Sequence for the anticipated number of records. Of course this can always be changed later. You also don’t need to format the sequence with leading zeros as the Format function does. As shown the expression returns something like: DCASD/CI123-025 for the 25th document in case CI123 for client DCASD. Without leading zeros it would be: DCASD/CI123-25. The advantage to the latter is that you don’t have to anticipate the number of records you might have in the sequence, but I’ve found many users prefer a more uniform number with the leading zeros.

As you’ve seen, the identifier is built using other data besides the sequence number. Since this other data is a part of the record, you don’t want to store this identifier since that means storing the same data twice. That’s why we use an expression to display the identifier rather than store it.

To recap, you use a DMax function to return the highest number in the Sequence and increment it by 1. You assign the incremented number to a control on your form and then immediately save the record. If your identifier includes additional information from the record, you use an expression that concatenates that information with the sequence number to display the full identifier.

With these techniques, you can generate a sequential number for just about any situation. I hope you find this information helpful.

© 2009 Scott B. Diamond — Diamond Computing Associates

Advertisements

93 Comments

  1. I\’m new to access with that being said where do I put the code Me.txtSequence = Nz(DMax(“[Sequence]”,”tblInquiry”,”Year([InquiryDate]) = “ & Year(Me.[txtInquiryDate])),0)+1when I try to build an expression code I receive an invalid syntax error message.

  2. I am trying to implement scenario 3 and can not get it to work. I created a button and put the code in the “on click” event procedure. I modified the code to work with my fields and tables. I also added the me.dirty = False and Me.Requery in an attempt to get things to work.

    Me.Sequence = Nz(DMax(“[Sequence]”, “[EvidenceDetail]”, ” [MatterCode] = ‘” & Me.[mattercode] & “‘”), 0) + 1
    Me.Dirty = False
    Me.Requery

    Can you tell me what I am doing wrong and why the following code does not populate the Sequence field? I am running MS Access 2007

    • Try it this way:
      Me.Sequence = Nz(DMax(“[Sequence]“, “EvidenceDetail“, ” [MatterCode] = ‘” & Me.[mattercode] & “‘”), 0) + 1

      The brackets around the tablename may be confusing Access. If you review the blog you will see I never use brackets for the table name. Other things to check. Is the Sequence control bound to the Sequence field in EvidenceDetail? Is MatterCode a text datatype?

      If you need more help with this, please post in the Access forum at askmehelpdesk.com

      Scott

  3. Hi Scott, I had a question regarding the sequential numbering Apex example…I am looking to automatically restart the sequence every month, which is not a problem using your example (i.e. changing the year to month in the expression). However, I would also like to add in a condition for year, so that the sequence restarts for each month of each year (i.e. my problem is for example that Feb 2011 last sequence number is 5, and then in Feb 2012 this becomes 6, where I would like it to be 1). I am wondering what the syntax would be. Thanks in advance, Lawn.

  4. Scott, trying my best to have sequential numbering almost identical to your scenario 1 (bound control on input form which increments last number by 1). Where do I enter this code? I have a form which opens and is ready for entry of new Project Designs. I want to show in the control box the next Project ID (a number) so do I place the code, Me.txtProject ID = Nz(DMax(“[Project ID]”,”DESIGN PROJECTS”),0)+1, under On Load, After Update, or where? And also, the Me.Dirty = False or DoCmd.RunCommand
    acCmdSaveRecord, where does this go? I currently have a control button which saves the record then closes the form.
    Thanks.
    JP

    • As I indicated, because you want to only generate this number just before saving, I would put this code behind a Save button or another event that runs just before you save the record. So if you want to prevent duplications, you don’t use the expression as a control source, but put the code behind a button or event. Since you have a Save button, put it there.

      Scott

      • Scott, you’ll need to be more specific to help me. When you say “put this code behind a save button” what exactly does that mean – where do I type the code you provided? Yes, I do have a Save Button, which saves the record and closes the form (but currently has no way to save the next sequential Project ID). I want show this next Project ID (number on the entry form) and have that new number flow to the table along with the other data on the form.
        John

      • John, Sorry for the delay, but I was away last week with limited Internet access. I assumed if you had a Save button, you would know how to put code behind it. To see the code behind a button, Select the button in Form Design Mode and open the Properties Dialog (Right click and select properties), on the Events tab there should be something in the On Click event of the button. If you click the Ellipses […] next to the event, it will open Code Builder where you can enter the code.

      • Scott, I was able to try this as I assumed that is what I needed to do. It does not work. First, the current macro for that button is a Save Record, Close Window operation. How could I keep this and also add Code? If I just add a new button to the form and under the On Click Event put the code you suggested, I get a compile error of “method or data member not found” and ” .txtPROJECT” is highlighted. The actual field is “PROJECT ID#” and it is a long integer number. Entry I have is: Private Sub Command88_Click()
        Me.txtPROJECT id#
        = Nz(DMax(“[PROJECT ID#]”,”DESIGN PROJECTS”),0)+1
        End Sub

        John

  5. Ahh you are using an macro.

    First, you have to use YOUR field and control names. The ones I use are samples. The error you are getting indicated you do not have a control named txtProject. So you have to substitute the correct name of the control. bound to the ProjectID field. By the way it is not a good idea to the octothorpe(#) as part of a field name. A controlname may be different from a fieldname. The name property of a control is on the Other tab in the Properties dialog.

    If you want to do this as part of the Save button, then you would need to convert the macro to VBA code first.

    • Scott, I have “txtProject ID#” in the code but the error message replies with highlight of “txtProject” omitting the ID#. I am aware of the # issue. The table field name is PROJECT ID#. The control is a text box of that field name. Is this not the control to use?
      If I can get the button working with this code then I can worry about putting VBA code for all the necessary actions.
      Thanks.
      John

      • Ok, So you want to store the sequential number in your field Project ID#, correct? Then the code you need to use is:

        Me.Controlname
        = Nz(DMax(“[PROJECT ID#]“,”DESIGN PROJECTS”),0)+1
        Me.Dirty=False

        For controlname you need to use the name of the control bound to your Project ID# field. However, the # may be complicating things. Type the Me. and Intellisense should give you a list of controls and methods. Press the P key to go to Ps and scroll until you find the controlname. Selecting the name from the Intellisense list will make sure you are using the correct control. That control needs to have a ControlSource of the Project ID# field. If you do this correctly, then pressing the button should populate the control with the next number. It will also save the record.

  6. Scott, we must be getting close to a working form, and following all the above goes well but upon clicking the button the error message I get is “You must enter a value in the Design Projects.Project ID field.” The text box on the form is bound to the table is Project ID. Naturally it is blank when the form opens. So, what next?
    By the way, I have removed the # from the field name and it is PROJECT ID now.
    John

  7. Try removing the required setting on the table level. It should work if you are doing the Me.Dirty=False after the assignment. But lets get the assignment working first. The line of code:

    Me.Controlname = Nz(DMax(“[PROJECT ID]“,”DESIGN PROJECTS”),0)+1

    should work fine. I’ve used in many times.

  8. Hi Scott, I’m following your writing and answers on this numbering problem. Mine must be far easier, except that I’ve closed my eyes.

    This is it. In my queries and reports I can rank students according to their performance. How then can I add a number to show the rank? The top student can be seen but I’d like to put a column showing the Number 1… last.

    Kind regards.

  9. For whatever reason, AllExperts did not let me post a direct reply to your response re: “Ok, what is the ControlSource of the Fixture Number control? It should be: =cboZone & “-” & Format(Me.FNumber,”000″)” and adding “Me.Refresh” to my code (within the last 10 minutes). It just had the “rate this reply”. I added the Me.Refresh and corrected my location of the =cboZone code and it works correctly now.
    Thanks again for all of your help on this
    Steve

  10. Hi Scott you helped me with the sequential numbering however, I am having a problem where I changed from before insert to putting the code behind a button Save Record and Get New Number under the OnClick

    Private Sub Save_Record_and_Get_New_Number_Click()

    Me.Job_No = Nz(DMax(“Job_No”, “Quote Sheet”), 0) + 1
    Me.Dirty = False
    MsgBox “Job Saved as Number: ” & Me.Job_No

    End Sub

    However, when I click on this button in Form view it just makes the number back to 1 instead of adding 1 to the existing records. Is this because when I open the form it brings me to record number 1 and not the last record I entered?
    Thanks for any help you can give me.

    • Hi Scott
      OK I found the ControlSource property but it is on the Job No text box, do I enter the code there or am I entering my code on the button I created to save and get new number? If I put it behind the button, when I open the form it goes to the first record so I go to the last record and hit save get new number button and it gives me the number 1……Is it because when I open the form it goes to the first record and not a new record????

      • You code says:

        Me.[Job_No] = Nz(DMax(“[Job_No]”,…

        Me.[Job_No] should refer to a text box control. That control should be bound to the Job_No field in the table. So when you assign the next increment, its stored in the field in the table.

        You should not be on the last record but on a NEW record when you click to save and get a new number.

  11. I have screen shots of what I have in my db, where can I send it to you (it is in word format) so that you can take a look at it to see what I am doing wrong. I took a screen shot of what is listed for the button and I took a screen shot of the text box Job No. I don’t know how to bound anything other than put the code behind the button and putting the Job_No in the code as you stated. I understand what you are saying, but I thought if I put the text box name Job No in the code behind the button then it would be bound to my table with the Job No field. Heeeelllllppppp!!!! I am sorry that I can’t seem to grasp this….but appreciate all of your help!

  12. Hi Scot,
    please could you describe step-by-step option 2 (Apex): where to put the code, behind which buttons, etc. becuse I just need a little impuls to finsih your tutorial.
    Thank You very much,
    Alen

    • The blog is pretty much step by step. Where it may not be that specific is, because, these are decisions the developer needs to make. For example, where to put the DMax expression is a matter of your workflow so I can’t tell you where to put it. I’ve given tips in the blog to help you decide. If you are having issues, then please give me more info about your application and I can suggest things.

      Scott

  13. OK.
    I have form with 4 text boxes. First txtBox is protocol number (this number consists of two letters, current year and increasing numbers: eg: KU-2013/0001). Next field is date field. Then I have description field (memo) and Operator name (text).

    Then I have two buttons: “add record” and “close form”. When I click Add record, I want protocol number to increase for one (eg: KU-2013/0002) and then I will populate other text boxes.

    That’s it.
    Thank you very much,
    Best regards,

    Alen

  14. Hi Scot,
    this is “Add” button in VB (converted macro):
    —————————————
    Private Sub cmdAdd_Click()
    On Error GoTo cmdAdd_Click_Err

    On Error Resume Next
    DoCmd.GoToRecord , “”, acNewRec
    If (MacroError 0) Then
    Beep
    MsgBox MacroError.Description, vbOKOnly, “”
    End If
    ‘ _AXL:
    ‘ New[MacroError]<>0=[MacroError].
    ‘ _AXL:[Description]

    cmdAdd_Click_Exit:
    Exit Sub

    cmdAdd_Click_Err:
    MsgBox Error$
    Resume cmdAdd_Click_Exit
    End Sub
    —————————————

    I have table “1-ULAZ_2013” with these fields:

    – Protocol number (txt)
    – Primljeno (date/time)
    – Description (memo)
    – Operator (txt)

    I created integer field “Sequence”.

    My DMax function is something like this:

    Me.txtSequence = Nz(DMax((Sequence), ”1 – ULAZ_2013”, ”Year([Primljeno]) = “ & Year(Me.[Primljeno])), 0) + 1

    Where to put this line of code?

    Compiler also throw me error that I need to define “Year”…

    Thank you very much,
    Alen

    • Ok first your Dmax should look like this:

      Me.txtSequence = Nz(DMax(“[Sequence]”, ”1 – ULAZ_2013”, ”Year([Primljeno]) = “ & Year(Me.[Primljeno])), 0) + 1

      You also need to add a textbox control on your form names Sequence. This textbox can be hidden (Visible property set to No).

      Second, as the blog says, where you put it is up to you and your work flow. It has to be executed after the date is entered. And the record has to be saved immediately after you generate the number.

      You can put it in a Save button, you can put it after the date is entered or wherever you want to and wherever it fits in your workflow.

      Scott

  15. I placed DMax function behind txtSequence textbox in afterupdate event…
    Compiler throw me this error – highlighted this part of function: [Sequence] and popup: Expected: list separator or )

    Thank you for your patience,
    Alen

    • “I placed DMax function behind txtSequence textbox in afterupdate event…”
      That makes no sense. The Afterupdate event of txtSequence will never be triggered because you should not be entering anything in that control. The only value that is entered, is entered via code.

      Again, you have to put this behind an event that is triggered after you enter the date.

      Scott

  16. Hi Scott,

    I am having trouble with this procedure. My situation is almost identical to example #2. I want to populate RecordNumber on my form with a date number like “130001, 130002, etc.” where “13” is the year based on a date automatically generated in the OpenDate field in tblLogRecords. OpenDate is a date/time type field that has a default value of the date the record is created and is not editable. I have added a field called Sequence to tblLogRecords and it is a Number, Long Integer type. I also created a bound control for Sequence on my form. I have a completely separate autonumber field as the PK.

    I added the following code to the control SaveRecordButton:
    Private Sub SaveRecordButton_Click()
    Me.txtSequence = Nz(DMax(“[Sequence]”, “tblLogRecords”, “Year([OpenDate]) = ” & Year(Me.[OpenDate])), 0) + 1
    DoCmd.RunCommand acSaveRecordButton
    End Sub

    I also created a control called RecordNumber with the control source as:
    =Format([OpenDate],”yy”) & Format([Sequence],”0000″)

    I’ve even tried adding the procedure to the OpenDate_AfterUpdate field instead of with the save record control, but I can’t seem to get it to work either. The only thing that shows up in the RecordNumber field is “13” unless I manually populate the Sequence field (which misses the whole point).

    Please help! Am I missing something?
    Thanks, Jen

    • The control bound to the Sequence field, did you name it txtSequence? If you did, then try the following as the Controlsource RecordNumber

      =Format([OpenDate],”yy”) & Format(Me.[txtSequence],”0000″)

      I would also doublecheck that the Sequence field is being populated. You might also requery the RecordNumber control.

      Scott

  17. Thanks, Scott. I made the changes you suggested, but it’s still not working. Nothing is being populated in the Sequence control on the form. If I manually enter a # in the Sequence field, the RecordNumber displays correctly. Here is my code for the SaveRecordButton and the controlsource expression for the RecordNumber.

    Private Sub SaveRecordButton_Click()
    Me.Sequence = Nz(DMax(“[Sequence]”, “tblLogRecords”, “Year([OpenDate]) = ” & Year(Me.[OpenDate])), 0) + 1
    DoCmd.RunCommand acSaveRecordButton
    End Sub

    =Format([OpenDate],”yy”) & Format([Sequence],”0000″)

    I didn’t requery the RecordNumber because Sequence isn’t populating.

    Thanks for your help!

  18. Thanks for correcting my Save Record procedure.

    Yes, the controlsource of Sequence is Sequence. I’m not getting any errors, just isn’t populating Sequence at all. I also tried making the controlsource the Sequence field in tblLogRecords, but that didn’t work either and gave me “#Name?” in the form.

    I made the field in tblLogRecords a long integer number and the control in the form is a text box.

    Not sure what isn’t working…

    Jen

  19. Hi Scott,
    I finally was able to make the code work by making the Sequence field a text field instead of a long integer and adding the If Then argument below. Here is the code I added to the save record button’s “On Click” event procedure:

    Private Sub SaveRecordButton_Click()
    If Me.Sequence = 0 Or IsNull(Me.Sequence) Then
    Me.Sequence = Nz(DMax(“Sequence”, “tblLogRecords”, “Year(OpenDate) = ” & Year(Me.OpenDate)), 0) + 1
    End If
    End Sub

    Now I have another problem. This code worked perfectly up to ten records. When I tried to add the eleventh record, the Sequence field won’t add any more numbers. It’s stuck at 10. This of course causes my RecordNumber field, which displays as
    =Format([OpenDate],”yy”) & Format([Sequence],”0000″)
    to display 130010 for every new record going forward.

    Any ideas what’s wrong?

    I appreciate your help!
    Jen

  20. Thanks, Scott! I was having a problem a few weeks ago getting the sequence field to populate and thought it was the data type based on another blog I read, but then added the If/Then statement. Just changed the data type back to integer and it’s working again.

    Thanks so much for your assistance!
    Jen

    • Ronnie,

      Yes, I have used this system in a multi-user setting. As noted, the key is to commit the record immediately after generating the sequence. However, if the application is one where there is very heavy transaction processing. In other words dozens of users creating records simultaneously, you might want to guard further against duplication. At the speeds computers process, it is not impossible that multiple users will grab the max value before it can be incremented and saved.

      Scott

  21. Hi Scott, you directed me here from the experts blog for this topic. Thanks again for all your assistance. I want to build a test example of your Acme example before creating it in my donations database, as I mentioned this is all very new to me so please bear with me:

    1. So I have a PO table with PONum as a field in the table, data type Number, field size long integer. Is this correct?

    – Also since I do not want the number to ever repeat do I need to have it Indexed – Yes(No Duplicates) ?

    2. In the form design view, I see the purchase number field and I try to enter the code : – Me.txtPONum = Nz(DMax(“[PONum]”,”tblPO”),000)+1

    – I want the code to be at least 3 numbers, non repeating so I changed your 0 to 000, so the first number will hopefully be 001. Is that right?

    – I had entered the code above into the property sheet of the PO Number, after reading all the questions and answer in this topic, I see now that I need to create a button.

    – On the button maybe called generate on the form, in the property sheet, the code above with be entered in the On Click field in the Event tab?

    – The Control source will be the PONum? All the fields are showing in the drop down arrow.

    – So when I create the button, I do not want it to be the save button, I want it to be a separate button in the form that populates/generates the number into the PONum field. Is that what your current example does?

    – I will also still have another exit/save button on the same form. This is appropriate, right?

    Your time and effort are truly appreciated.

    • 1. Correct, and yes, setting a Unique index is a good idea here.

      2. Yes, The code should be entered using CodeBuilder. Where you enter it depends on how and when you want to trigger the generation of the next number. If you want to use a button, that works. And no, you don’t use 000 in the NZ() function. If you want to DISPLAY at least 3 digits with leading zeros, then you do that in the Format function. Note, though, you will need to change that when you hit 1000 POs.

      If you want to create the button, don’t use the Wizard or, just Cancel the wizard as soon as you place the button control. Since you will need to customize the button’s properties.

      Yes, the Controlsource is the PONum field.

      Yes, you can have as many buttons as you want or need.

      Final note. Remember to make sure you use the actual object names. The textbo bound to the PONum filed would not be named txtPONum by default. And don’t forget to add the Me.Dirty=False to commit the records immediately after generating the number.

      Scott

      • OK,Thanks.

        I do not want the numbers to begin with 000. I just want 3 digits. From your example, it seemed to me that the first number generated will be 1, then increase there after.

        I just want a random 3 digit number every time. They don’t even have to be in sequence, just generate a unique number every time I click the “generate/assign” button. The database right now is only about 200 members, but it is expected to grow over time.

        If I want to display 3 digits where in the Format tab do I specify this?

        Thanks for the Me.Dirty reminder. The Me.Dirty=False, is that on the same line as the code at the end? Or in the field that says On Dirty?

        I am a little confused by your finale note paragraph. In my original try, the field in the form, I had used design wizard so it already had the field PONum on it, is this something I am supposed to change?

        If I build the form from scratch in design view, are you just reminding to name them correctly?

        So that in the Caption Field within the Format Tab, I accurately name txtbox to be what I want like Personal Number ?

        Just to clarify – the number will generate in the PO field right?

        Many Thanks!

  22. Ok, generating a random 3 digit number is a whole different thing, so I’m not going to go into that. If you want your numbers to start at 100 (to insure three digits) then change the 0 to 99. The Nz function will return the value listed if the field is Null. So the first time you execute that code, the DMax should return a Null since no numbers have been generated for the PONum field. The Nz will then substitute 99 and then increment that by 1. You can accomplish something similar by just entering 100 as the PONum for one record. .

    Not in the Format tab. But the blog refers to a Format function when you want to display the #. But, if you are going to start at 100, then using a Format function won’t be necessary.

    The Me.Dirty = False line of code goes on the NEXT line after the DMax.

    If you use the Form Wizard, controls will be named with the field name the control is bound to. But that name can be changed. This trips up a lot of people because my code samples use a naming convention that is not what is automatically generated. So you just need to make sure you use the correct name for the object. The name is shown in the Name property on the Other tab (Not the Caption property). To determine what field in your table the control is bound to check the ControlSource property. It should be bound to the PONum field.

    Scott

  23. Ok. Thanks.

    By starting at 99, will I reach a problem when it reaches a certain number like in your prior response?

    I guess random 3 digit number is not as straight forward? Is that why you will not be going into it?

    • No, Since you will be starting with a 3 digit number initially, then you don’t need to use the Format function to pad with leading zeros. And since you won’t be using the Format function, then there will be no problem when the number goes to 4 digits.

      Its not that its not as straight forward, though it is more complex. Its more that it requires a completely different method to generate a number. Plus a you would have to add checking to ensure uniqueness. Plus you then have to partially rewrite your code when you reach 999 members.

      Scott

      • Ok, thanks again.

        Initially tried it your way:

        Me.txtPONum=Nz(DMax(“[PONum]”,tblPO”),0)+1. The “),0)+1 part is highlighted . It says compile error: Expected: list separator or)

        When I try to test it anyway, it returns Compile error: syntax error.

        Thanks again.

    • Right. Don’t know how I missed that.

      Now there is an error message Compile error: Method or data member not found. With a yellow arrow, and the Private Sub Command4_Click() is highlighted in yellow. and the .txtPONum = is highlighted in blue.

      I changed the Command4 to the new name ASSIGN, as I called the button, no more errors, but the field stays empty. Nothing is generated.

      Thank you very much. Your help is really appreciated.

  24. Hi again Scott,

    I have re-read through all your responses and made the changes and it is still not working. No error message anymore, it is just not generating a number.

    In the other tab for the PONum field, I typed in txtPONum as you had mentioned earlier. It is just not generating a number. I don’t even know what question to ask now, ’cause I do not know where this is going wrong. I’ll keep trying something…

    Anyway, thanks again for all your assistance. it was appreciated.

    • Hi Kay is it just not generating a number or just not STORING the number. These are different things.

      Can you step through the code and make sure whether a number is not being generated or whether its just not being assigned to the control.

      If you put a break point on the line:

      Me.txtPONum = NzDmax(etc.)

      You can add Debug.Print Me.txtPONum right after. This will display what is in the control (again assuming that is the correct control name).

      To recap. The NZ(DMax(etc.) will take the highest value of the PONum field in tblPO and increment it. You then have to store that value somewhere. If you assign it to a control on a form, that control has to have a ControlSource of the filed in the table. Also, you need to save the record before it gets to the table.

      Scott

  25. Hi Scott,

    I have been reading this thread thoroughly but am having some trouble getting this to work with my specific project. I will try to give as much information as I can.
    I have a table named Artifact Catalog in which there is a field Collection Point ID and a field Artifact ID. On the form I have created the user will input the Collection Point ID, for example: 2-1050. I need to find a way to have this Collection Point ID automatically generate a corresponding Artifact ID, i.e when you click the save button the first record under Artifact ID becomes: 2-1050.1 and the second becomes 2-1050.2 and so on.
    I am new to coding in VBA and working with Access but have spent hours on this one issue and would really appreciate if you could help me out.
    Both the Collection Point ID and Artifact ID fields are bound properly and display those exact names in the property sheet under both control source and name. On the save button I have on the form, when I click on the event tab and the on click option I have event procedure and I click the […] option to open up the code builder and this is what I currently have:

    Option Compare Database

    Private Sub Command40_Click()
    Me.Artifact ID= Nz(DMax(“Artifact ID”,”Artifact Catalog”,” ”Collection Point ID” = ‘“ & Me.[Collection Point ID]& “’”),0)+1
    End Sub

    Private Sub Save_Button_Click()

    End Sub

    I get an error with this code but it is something I literally copied from a post in this thread where you said it works every time and simply replaced the names of fields and tables with those of my project.
    I know this may seem like I’m just not trying but this is driving me crazy, if you could help me with this it would make such a difference.
    Also since I do not have much experience with this if you could be as specific about what I need to do as possible it would be great.

    Thanks and hope to hear from you soon!
    Dylan

    • Hi Dylan,

      Ok, First a part of your problem is that you have spaces in your object names. This is not recommended as it can come back to haunt you which it is doing here.

      Try changing your code to:

      Private Sub Command40_Click()
      Me.[Artifact ID]= Nz(DMax(“[Artifact ID]”,”Artifact Catalog”,” ”[Collection Point ID]” = ‘“ & Me.[Collection Point ID]& “’”),0)+1
      End Sub

      Also make sure you are not using the quote marks that are shown here. If you continue to get an error, please let me know the EXACT error message.

      Now, as mentioned in my blog, your sequential number should be stored in a separate field with a datatype of Long Integer. You can use your Artifact ID field where I was using Sequence, but make sure its the correct datatype. Now, when you want to DISPLAY the combination of Collection Point ID and Artifact ID you use an expression:

      =[Collection Point ID] & “.” & [Artifact ID]

      Another point here is that you should have:

      Me.Dirty=False
      immediately after the DMax. And a third point is that you have nothing behind your Save button.

      Also you shouldn’t use the generic object names like Command40. Give you objects descriptive names so you know what they do like cmdSequence.

      Scott

      • Hey Scott,

        Thanks for the quick reply, I think I’m a little confused by the Display aspect of this. Artifact ID is a primary key to the table I am working in and I need it to have a unique identifier that other people can search for. The previously adopted naming convention for this ID is that which I mentioned before, Collection Point ID plus a period and then a sequential number restarting at each new Collection Point ID i.e 2-1050.1, 2-1050.2, 2-1060.1 and so on. So I think I actually need to save this in the Artifact ID field. I realize this might be slightly redundant but I’m not sure how the display would work as far as searching for specific Artifact IDs in the future.

        As far as the code goes I have changed it to this:

        Option Compare Database

        Private Sub Form_Click()
        Me.[Sequence] = Nz(DMax(“[Sequence]”, “Artifact Catalog”, “[Collection Point ID]” = Me.[Collection Point ID]), 0) + 1
        Me.Dirty = False
        Me.[Artifact ID] = [Collection Point ID] & “.“ & Format([Sequence], “000“)
        End Sub

        Private Sub Save_Button_Click()

        End Sub

        It is now attached to a save button and attached the code properly (I think). I added the field Sequence as Long Integer since I want to actually store the result of this code into Artifact ID field.

        When I run this I get the error:
        The identifier ‘[ArtifactID]’ could not be found.

        It makes me think there’s an issue with the spaces I have used in my field names but I have been trying every combination of double quotes and square brackets and when writing the code in the Event Procedure I don’t get any error messages.

        I have checked the control source for the Artifact ID field and it is Artifact ID, spelled and with the same spacing. I feel like I must be getting close now, hopefully I’m on track!

        Thanks again,
        Dylan

      • The Artifact ID should NOT be your PK. There is no reason for it to be and to try and use it as such will be a headache. A primary key is simply a unique identifier for a record. Many purists will tell you that users should never see the PK and in your case, I would recommend that. Use an Autonumber as you PK and you can use that as your corresponding Foreign Key in related records. To prevent duplication you can make the combination of Collection Point ID and Artifact ID a unique, multi-field index. Then display the combination as I indicated where you need to show the user a record ID. This is all explained int he blog.

        And yes using spaces in object names will come back to haunt you. I recommend never to use spaces in object names.

        As for that error, you need to find what line of code is generating it. If the error message has a Debug choice use it to see what line of code. If not, you need to step through the code to see where it errors out.

        Finally,you said: “I’m not sure how the display would work as far as searching for specific Artifact IDs in the future.”. This will not be a problem. The best solution is to use a combobox to select the Artifact ID. The relevant properties of that combo would be:
        RowSource: SELECT recordID, [Collection Point ID] & “.“ & Format([Sequence], “000“) AS ArtifactID
        FROM [Artifact Catalog]
        ORDER BY [Collection Point ID],[Sequence];
        Bound Column: 1
        Column Count: 2
        Column Widths: 0″;1″

        The user will then see a list of the combinations of to choose from, but the combo will return the autonumber RecordID to use to select the record. In fact, you can use the combobox wizard to create a search combo.

        if you prefer they just type in the number, you can parse out the number into the Collection Point and Sequence components, then use a DLookup to return the RecordID which can be used to find the record. Like I said, the user never sees the autonumber ID, its meaningless to them.

        Scott

  26. Pingback: Another post on Sequential Numbering… « Gina's Coffee Shop

  27. Hi! I hope you help me with this. I have the almost same problem as #2. I have PassengerTable as table name, then I have the following fields: sequence (to follow what you have in your post), transaction_date, and transaction_ID. Basically I want the to have sequential numbering in the transaction_ID where month and date from transaction_date is shown. i also have a generate (Command27)button as trigger.Tried the code with few modifications, but it sequence doesnt populate, and doesnt show any, except for what i have in control for transaction_ID which is the “Format(transaction_date,”yyyy-mm”) & “-” & Format(sequence,”000″)” and this only shows the year and date, so instead of 2015-08-001 it shows 2015-08-.

    This is what I have in the generate button:

    Private Sub Command27_Click()
    Me.sequence = Nz(DMax(“[sequence]”, “PassengerTable”, “Year([transaction_date]) = ” & Year(Me.Transaction_Date) & ” AND Month([transaction_date]) = ” & Month(Me.Transaction_Date)), 0) + 1
    End Sub

    I hope you can help me with this, please.

    Thank you very much

    • You have a field in your table named sequence? But what is the controlsource of sequence control on the form? It sounds like you didn’t assign that. Also, if this is going to be your transactionID, you shouldn’t have a field in your table as you will be using the expression to display it. Use an AutoNumber for you PK.

  28. thank you for your very fast reply!

    uhm, for the sequence, i just have “sequence” as control. i’m not actually sure what to put in there.

    so you mean instead of having a field “transactionID”, i’ll just have a field with autonumber? sorry i didn’t quite get it.

    • But what is the ControlSource. Data controls have a Controlsource property. This property can be viewed in the Property dialog at the top of the Data tab. You need to set this to the Sequence field in your table.

      What I mean is that TransactionID is a calculated value. It is calculated using the TransactionDate and Sequence fields. So it does not need to be stored. You use the expression to display it wherever you need to. This is explained in the blog.

      But you still need a PK field so use an autonumber for that.

      Scott

  29. Ugh okay though the number shows up when I move or type in another field (First_name field) than clicking generate. When I click generate it doesn’t give the number. how can i fix it? here’s what I did:

    Option Compare Database

    Private Sub Command18_Click()
    Me.sequence = Nz(DMax(“[sequence]”, “table”, “Year([transaction_date]) = ” & Year(Me.Transaction_Date) & ” AND Month([transaction_date]) = ” & Month(Me.Transaction_Date)), 0) + 1
    End Sub

    sorry for the bother ><

    • I’m not clear what is happening.

      First, you need to enter the TransactionDate first. You can’t generate the Sequence until a TransactionDate is entered. Second, you are missing a line of code. In the blog I emphasize that you have to commit the record immediately after generating the sequence. So you need that second line: Me.Dirty=False after the code you have.

      I’m also concerned about your table design. You mentioned entering a First name. But I don’t really see that people names belong in a transaction record. Can you explain more about what you are tracking in your app and what these transactions are.

      Scott

  30. Hello! Sorry for the late reply!

    I’m kind of new in access and vb so forgive me.

    We’re ,making a database for a train reservation system. there are two tables: passengertable (which has the sequence, transaction_date, transaction_ID, first_name, last_name, mobile_number, address), reservationtable (transaction_ID, train_number, train_schedule).

    I have a transaction_date and it does have a content.

    Now I’m having multiple problems even if i see the sequential numbering in the form view under transaction_ID field, it does not “save” those numbers in the transaction_ID in passengertable but it shows up in the form view (passengerform).

    I provided a few screenshots if my explanation got you a bit confused: http://s1124.photobucket.com/user/KotoneYuudai/library/Access%20Problems

  31. OK, First, you need to reread the blog. You should NOT be storing the Transaction_ID. It is a calculated value. Second, your naming is what confused me. Your Passenger table should have an autonumber PassengerID. That PassengerID should be the Foreign Key in your Reservations table. Your servation table should also have an autonumber for ReservationID. I really don’t see why you need sequential numbering in the passenger table. I can understand it in the Reservations table, but not in the passenger table. Also, I don’t see any reason for a transaction date in the passenger table. I can understand a CreateDate for when the passenger signed up.

    This is how I see it. A person (passenger) makes a reservation. If the person has never made a reservation before, then they are added to the passenger table, But it really makes no sense to assign a sequential number to the passenger. Otherwise, an entry, just needs to be made in the reservations table. The Reservation needs to include the specific train (number and date so there should be a table for train schedules so all you need is a foreign key), the passenger and date of travel. Here I can understand wanting a sequential number to show passengers booked in a day.

    But you still don’t need to store that ID since you can display it anytime using the expression.

    Scott

  32. Dear Sir,

    I have table of following fileds

    ID (as a primary key)

    AccountingYear (Filed for current accounting Year e.g., 14-15; 15-16)

    Series (text- as R, Y, B etc to denote series code)

    OrderNo (field to store Order No e.g., for Series R-1, R-2, for series Y Y-1, Y-2, etc)

    Now what i want to do is I want to create custom order no as

    first 2 digit of accounting year+Series+Autonumber of respective series

    as

    14R-1
    14R-2
    14Y-1
    14Y-2
    14Y-3
    14B-1

    15R-1
    15R-2
    15Y-1
    15Y-2

    Means autonumber starts with 1 for every accounting year of respective Series

    Please guide me as how to do this and what code or formula should be applied and where the code should be inserted as I am new to Ms access

    • First, you can’t use an Autonumber as part of your identifier as it can’t be reset for each year. So you have to follow the instructions in my blog article to create a sequential number. The article describes exactly how you can do what you want. If you follow the article closely, you should be able to do what you need.

      If you have any specific questions about techniques illustrated in the article, feel free to ask, But the blog already IS a guide to how to do it, you just need to follow it.

      Scott

  33. Scott, I had posted on Microsoft and you sent me to your blog to have the numbering system (similar to APEX example) explained. I am not a programmer and I don’t understand where these codes and expressions are even suppose to go in access. When I do try to implement the little I do know I continue to get errors. I am not sure if I am putting the information in the wrong place or if I am way off. Do you know of any youtube videos that could walk me through it step by step? Or if you have the time could you help walk me through the steps.
    Thanks

    • I replied to you on the Answers forum. Let’s continue there, but I need to know what you have done and what about is not working. Please reply there with details. But the blog instructions are pretty specific, if you follow them with the only changes substituting your actual field and control names, then it should work.

      Scott

  34. One other thought. It may not hurt to make option 1 a logical expresssion where it will update the number IF a number other than 0 already exists for it. This will prevent it from giving a new number if you go back, edit it and save it. I accomplished this by adding the following (roughly): If PONo=0 Then My.PONo…. (Expression and save command) Else (Save Command)

    • The expression: Nz(DMax(“[PONum]”,”tblPO”),0)+1 will check if a PONum already exists. If it doesn’t it returns a 1, if it does it returns the number incremented by 1. If the number exists, but is 0 it will return a 1. In my blog I advise that number should NOT be generated until the user is ready to save the record. And to immediately commit the record after generating the number. Therefore, there should be no issue about giving them a new number if they go back to it.

      So if you encountered this problem, then you didn’t follow the instructions completely (which is OK) and appear to be automatically generating the number whenever the record is accessed. If that’s the case, then you are risking duplication because you are not saving the generated number immediately. Rather then try to test if a number already exists as you seem to be doing, you should not automatically generate the number, but trigger the generation from some action in the form.

      Scott

  35. Scott can I please get more detail on how to do what you are talking about in the scenario 3 you described above as this is exactly what I am basically trying to do. I would like to explain in more detail what I have accomplished so far and what I am trying to do.

      • Ok I guess it is better for me to explain what I am doing. I am in the process of creating an Access Database that an individual has been using an Excel spreadsheet forever and a day. Well the individual has on occasion doubled up numbers, forgot numbers, etc. So on what I have learned from different Access courses and Google searches I am trying to apply my knowledge.

        What I have is a table that has the following fields in order:

        LH Number – Which I am trying to figure out how to autonumber as they use the following format LH-YYYY-001

        Name of Case

        Date of Case – This is the date the case was received from Office of General Counsel

        Attorney – This needs to be a multi-valued field as several attorneys maybe assigned the case.

        Custodian – This needs to be a multi-valued field as well as there may be several locations that may have records.

        Date of Lift – This is the date the case is closed.

        Region – Location

        Type of Case – This is whether it is EEO/FOIA/etc.

        MSPB Docket

        Civil Case

        Misc Notes

        So when a case is closed I am wanting to take that record and append it into an archived location so that way it is not in the active cases.

        However at the same time I am trying to autonumber the LH field so that at the end of the calendar year it will automatically roll to the new year with a new sequential number. I hope this all makes more sense now.

      • OK, so what didn’t you understand in my blog? The blog contains instructions on how to deal with this. It tells you that you do not want an LH Identifier field. It tells you that all you need is to add a Sequence field to store the Sequential number. It tells you how to base that number on the current year so it restarts with the new year, Finally it tells you how to combine the year and the sequence # with whatever else you need to display the LH identifier. These instructions are all detailed in the blog.

        As for archiving, why? Add a flag to your table named Archive that defaults to 0. When a case is complete just set the flag to true and filter your form and reports for Archive = False.

        Scott

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s