![\"MissingNumbers\"](\"http:\/\/regina-whipp.com\/blog\/wp-content\/uploads\/2014\/09\/MissingNumbers.png\")
<\/p>\n
2. It\u2019s not always sequential<\/p>\n
Sometimes Access decides to assign a negative number. Autonumber is only guaranteed to be unique not sequential even if you select Increment<\/strong> under New Values<\/strong> at the Table level<\/strong>.<\/p>\n 3. While you can set the initial value (The Access Web –\u00a0Have Autonumber field start from a value different from 1<\/strong><\/a> or Allen Browne\u2019s Site Set AutoNumbers to start from<\/strong><\/a>) you cannot otherwise modify the number. The Autonumber Data Type is not editable and there is no way around that via Access. The only exception to this rule is if you upsized your Table to SQL Server in which case you can modify the number.<\/p>\n 4. And, it does not always indicate the order in which records are entered, see Item 2.<\/p>\n So, now what?\u00a0 Instead, create a separate field for your sequential\/custom number\/string, herein referred to as the Sequential ID.\u00a0 A few tips before we get started…<\/p>\n The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.<\/p><\/blockquote>\n You can implement your own Error Handling or Roger Carlson has some simple code to trap the error, see Domain Function Example: Simulate AutoNumber with DMax()<\/strong><\/a><\/p>\n You will need to decide for yourself what you want to do with this *orphaned* numbers, let them go or allow your Users to use one of them when creating a new Record.<\/p>\n So, let\u2019s create some Sequential ID\u2019s…<\/p>\n A.<\/strong> Sequential ID number, pseudo Autonumber a. Sequential ID with Year prefixing starting fresh each New Year D.<\/strong> Variations of Sequential ID\u2019s<\/p>\n Side note:\u00a0 While some of the methods here allow you to use Sequential ID as a Primary Key, however, it would not be my first choice.\u00a0 That said the method described in A. is at the top of the list for preferred methods should you decide not to use the Autonumber Data Type but implement your own.\u00a0 Remember, just because you can do so does not mean you should do so.\u00a0 Evaluate your schema and decide what works best for you (and the Database) your Users are only going to see what you let them see.<\/p>\n Special note:\u00a0 The sample code provide here does not provide any Error Handling which means you should implement your own.<\/p>\n With this example you can use the Sequential ID as a Primary Key by putting it in the Before_Insert of the Form, since the Before_Insert event fires when a User moves to a new Record you can increment and then Save<\/strong> locking your number in place. (Again, just because you can do thing does not mean you should do a thing… so, again, use this one as the one the User sees and maintain Autonumber with another field as the Primary Key Access uses.)<\/p>\n Private Sub Form_BeforeInsert(Cancel As Integer)<\/p>\n End Sub Private Sub Form_BeforeUpdate(Cancel As Integer)<\/p>\n End Sub When using this method you\u2019ll want to *push* the User to a new Record and then create your Sequential ID. After all, we don\u2019t them overwriting an existing Sequential ID. You can either do it automatically…<\/p>\n Private Sub cmdAddNew_Click()<\/p>\n End Sub End Sub<\/p>\n C. Customize your Sequential ID<\/strong><\/b><\/p>\n a. Sequential ID with Year prefixing starting fresh each New Year<\/strong><\/b><\/p>\n This example will only increment up to 99999, see GetMyID<\/strong>!\u00a0 If you plan to use this method you must be careful you will not have more than 99,999 records per year or, if you want to go higher, you can expand on the spaces (placeholders), however, you still need to take note of *how many* you allot when using this example or this might happen…<\/p>\n 1400000 …go to add a new Receiving ID and you get…<\/p>\n 1400000<\/p>\n …and an Error Message because at the end it will *start again*! \u00a0So, let’s avoid code that locks you into a maximum record count unless that is what you mean to and have an end game, like starting a new unique sequence.<\/p>\n Copy\/paste the below in a Module then Debug > Compile.\u00a0 Be sure to change the name of the Fields and Table below to match your own.<\/p>\n Create your Command Button and the copy\/paste the lines between Private Sub\u2026 and End Sub in the Event Procedure of your Command Button.\u00a0 There are two procedures listed below, one with validation and one without.\u00a0 You only need to copy\/paste the one you want to use not both.\u00a0 Again, be sure to change the name of the Fields and Table below to match your own.<\/p>\n With validation\u2026<\/p>\n Private Sub cmdReceive<\/strong>_Click()<\/p>\n End Sub<\/p>\n OR<\/strong> if no validation is needed\u2026<\/p>\n Without validation\u2026<\/p>\n Private Sub cmdReceive<\/strong>_Click()<\/p>\n \u00a0End Sub<\/p>\n \u00a0<\/b><\/p>\n b. Sequential ID with YYMMDD with an incremental number suffix<\/strong><\/b>\u00a0 In this example you can easily tell how many Quotes you got on any particular day and still maintain a unique ID.\u00a0 This type of Sequential ID can be useful for tracking sales or sales per Associate\/Employee.\u00a0 You can query the table to check how your business or Sales Staff are doing.\u00a0 One caveat of doing it this way is you will to determine what part is which because you have not formatted two digits for Months and Days.\u00a0 The second example does this and makes it much easier to determine when the Quote was actually created no matter what create date is entered.<\/p>\n Create a query to count records each day to use in the Function, i.e.<\/p>\n qryCountOfQuotes<\/strong><\/p>\n Be sure to change the name of the Fields and Table below to match your own.<\/p>\n Create your Command Button and the copy\/paste the lines between Private Sub\u2026 and End Sub in the Event Procedure of your Command Button.\u00a0 There are two procedures listed below, one no space holders for double digit Months and Days, Example One and; one with spaces holders for Months and Days, Example Two.\u00a0 The Year is double digit for both examples.\u00a0 You only need to copy\/paste the one you want to use not both.\u00a0 Again, be sure to change the name of the Fields and Table below to match your own.<\/p>\n Private Sub cmdAddNew<\/strong>_Click()<\/p>\n \u00a0End Sub<\/p>\n OR<\/strong><\/p>\n \u00a0Private Sub cmdAddNew<\/strong>_Click()<\/p>\n End Sub<\/p>\n c. Sequential ID strings alpha-numeric or numeric alpha<\/strong><\/b><\/p>\n Here are two examples using incremental letters; the first will be using an YYDDMM format implementing the letter at the end. The second example I\u2019m using Property Cards which, in this case, only have a letter if there is more than one card. The Property already has a Parcel Number, so no incrementing of the Sequential ID needed.<\/p>\n Create a query which will be used to create the Sequential ID<\/p>\n <\/p>\n Create your Command Button and the copy\/paste the above line between Private Sub\u2026 and End Sub in the Event Procedure of your Command Button.\u00a0 You only need to copy\/paste the one you want to use.\u00a0 Again, be sure to change the name of the Fields and Table below to match your own.<\/p>\n OR<\/strong><\/p>\n Create your Command Button and the copy\/paste the lines between Private Sub\u2026 and End Sub in the Event Procedure of your Command Button.\u00a0 You only need to copy\/paste the one you want to use.\u00a0 Again, be sure to change the name of the Fields and Table below to match your own.<\/p>\n Because this is a Property Card and 90% of the information will be duplicated, I am taking what I need from the first card and putting it on the next card.\u00a0 If this is the first time it\u2019s happening, the only card, I first need to add the *A* and then create the new card.<\/p>\n Public Sub cmdDuplicate<\/strong>_Click()<\/p>\n End Sub<\/p>\n If you just want to increment letters you can use the below.\u00a0 Note, when using the alphabet you can only go up to *Z* unless you use a custom VBA procedure.\u00a0 See the link below for one such custom procedure.<\/p>\n To Increment Letters…<\/p>\n To increment beyond the single letter click here<\/a>.<\/p>\n D. Variations<\/strong><\/b><\/p>\n I. You can change how many letters you want use as the Prefix by changing all the 3\u2019s to how ever many letters you want as the Prefix.<\/p>\n II. …using a Department, Section or Index Key as a Prefix…<\/p>\n …to add a hyphen\u2026\u00a0 To use any of the above create your Command Button and the copy\/paste the lines between Private Sub\u2026 and End Sub in the Event Procedure of your Command Button.\u00a0 You only need to copy\/paste the one you want to use.\u00a0 Again, be sure to change the name of the Fields and Table below to match your own.<\/p>\n III. Private Sub Form_BeforeInsert(Cancel as Integer)<\/strong><\/p>\n End Sub<\/p>\n IV. To use copy\/paste the above lines into a Module.\u00a0 To use\u2026<\/p>\n <\/p>\n So, when do those Form Event fire?<\/p>\n Creating a new record Current<\/strong> (form) \u2192 Enter<\/strong> (control) \u2192 GotFocus<\/strong> (control) \u2192 BeforeInsert<\/strong> (form) \u2192 AfterInsert<\/strong> (form)<\/p>\n The BeforeUpdate<\/strong> and AfterUpdate<\/strong> events for the controls on the form and for the new record occur after the BeforeInsert<\/strong> event and before the AfterInsert<\/strong> event.<\/p>\n From: Order of Events for Database Objects<\/a><\/p>\n <\/p>\n More sites about Sequential Numbering\u2026![\"OrderByID\"](\"http:\/\/regina-whipp.com\/blog\/wp-content\/uploads\/2014\/09\/OrderByID.png\")
<\/p>\n\n
\n
\r\nSELECT T.lLiftID+1 AS Missing\r\nFROM tblLifts AS T LEFT JOIN tblLifts AS T1 ON T1.lLiftID=T.lLiftID+1\r\nWHERE (((T1.lLiftID) Is Null));\r\n<\/pre>\n
\nB.<\/strong> Use a Command Button to assign a Sequential ID
\nC.<\/strong> Customize your Sequential ID<\/p>\n
\nb. Sequential ID with YYMMDD with an incremental number suffix
\nc. Sequential ID strings alpha-numeric or numeric alpha<\/p>\n\r\n If Me.NewRecord Then\r\n Nz(DMax("YourIDField","YourTable"), 0) + 1\r\n DoCmd.RunCommand acCmdSaveRecord\r\n End If\r\n<\/pre>\n
\nNote<\/strong>: You can also use the Before_Update1<\/sup> event of the Form, and while, the Before_Insert event fires before the Before_Update event the Before_Update event is the only event that *writes to disk* upon firing.<\/p>\n\r\n If Me.NewRecord Then\r\n Nz(DMax("YourIDField","YourTable"), 0) + 1\r\n DoCmd.RunCommand acCmdSaveRecord\r\n End If\r\n<\/pre>\n
\nB. Use a Command Button to assign a Sequential Increment<\/strong><\/b><\/p>\n\r\n If Dirty Then Exit Sub\r\n DoCmd.GoToRecord , , acNewRec 'Force User to a New Record\r\n Me.txtCoilID = Nz(DMax("crCoilID", "tblCoilReceiving"), 0) + 1\r\n Me.cboCoilID = Me.txtCoilID\r\n<\/pre>\n
\n…or send them a message to go to a New Record if they are not already on one…
\nPrivate Sub cmdAddNew_Click()<\/p>\n\r\n If Not IsNull(Me.txtCoilID) Then\r\n 'Tell User to go to a New Record first\r\n MsgBox "You MUST go to NEW record first!", vbCritical, "Add New"\r\n Exit Sub\r\n End If\r\n\r\n Me.txtCoilID = Nz(DMax("crCoilID", "tblCoilReceiving"), 0) + 1\r\n Me.cboCoilID = Me.txtCoilID\r\n<\/pre>\n![\"GetMyID\"](\"http:\/\/regina-whipp.com\/blog\/wp-content\/uploads\/2014\/09\/GetMyID.png\")
<\/p>\n
\n1400001
\netc…
\n1499999<\/p>\nStep 1<\/h6>\n
\r\nFunction GetMyID() As Long\r\n GetMyID = Format(Date, "yy") & Format(Nz(DMax("rSequenceID", "tblReceiving", "Year([rReceiveDate]) = " & Year(Date)), 0) + 1, "00000")\r\nEnd Function\r\n<\/pre>\n\u00a0Step 2<\/h6>\n
\r\n'Inform User they are not on a New Record\r\n If Not IsNull(Me.txtReceiveID) Then\r\n MsgBox "You MUST go to NEW record first!", vbCritical, "Receive"\r\n Exit Sub\r\n End If\r\n \r\n 'Validate that all other required Controls are filled in prior to creating Sequential ID.\r\n If sfrValidateData Then\r\n Me.rSequenceID = Nz(DMax("rSequenceID", "tblReceiving", "Year([rReceiveDate]) = " & Year(Date)), 0) + 1\r\n DoEvents\r\n Me.txtReceiveID = <strong>GetMyID()\r\n DoCmd.RunCommand acCmdSaveRecord\r\n 'Requery the Combo Box to exclude the newly received item from the Combo Box which shows items not yet received\r\n Me.cboResults.Requery\r\n Else\r\n MsgBox "Oops, no Receive ID created, please retry!"\r\n End If\r\n<\/pre>\n\r\n'Inform User they are not on a New Record\r\n If Not IsNull(Me.txtReceiveID) Then\r\n MsgBox "You MUST go to NEW record first!", vbCritical, "Receive"\r\n Exit Sub\r\n End If\r\n \r\n Me.rSequenceID = Nz(DMax("rSequenceID", "tblReceiving", "Year([rReceiveDate]) = " & Year(Date)), 0) + 1\r\n DoEvents\r\n Me.txtReceiveID = GetMyID()\r\n DoCmd.RunCommand acCmdSaveRecord\r\n \r\n 'Requery the Combo Box to exclude the newly received item\r\n Me.cboResults.Requery\r\n<\/pre>\n![\"SequentialYYMMDD\"](\"http:\/\/regina-whipp.com\/blog\/wp-content\/uploads\/2014\/09\/SequentialYYMMDD.png\")
<\/p>\nExample One<\/h5>\n
Step 1<\/h6>\n
\r\nSELECT Left([qQuoteID],InStr([qQuoteID],"-")-1) AS Quotes, Count(Left([qQuoteID],InStr([qQuoteID],"-")-1)) AS CountOfQuotes FROM tblQuote GROUP BY Left([qQuoteID],InStr([qQuoteID],"-")-1);\r\n<\/pre>\n
Step 2<\/h6>\n
\r\n 'Push User to a New Record\r\n DoCmd.GoToRecord , , acNewRec\r\n Me.txtQuoteID = Format(Date, "yy") & Format(Date, "m") & Format(Date, "d") & "-" & Nz(DLookup("CountOfQuotes", "qryCountOfQuotes", "Quotes = " & Format(Date, "yy") & Format(Date, "m") & Format(Date, "d")), 0) + 1\r\n Me.cboQuoteID = Me.txtQuoteID\r\n<\/pre>\nExample Two
![\"qryCountOfQuotes\"](\"http:\/\/regina-whipp.com\/blog\/wp-content\/uploads\/2014\/09\/qryCountOfQuotes.png\")
<\/h5>\n\r\n Me.txtQuoteID = Format(Date, "yymmdd") & "-" & Nz(DLookup("CountOfQuotes", "qryCountOfQuotes", "Quotes = " & Format(Date, "yymmdd"), 0) + 1\r\n<\/pre>\nExample one
![\"140725A\"](\"http:\/\/regina-whipp.com\/blog\/wp-content\/uploads\/2014\/09\/140725A.png\")
<\/h5>\n\u00a0Step 1<\/h6>\n
\r\nMe.txtQuoteID = Format(Date,"yymmdd") & Chr(Nz(DMax("Asc(Right(qQuoteID,1)),","tblQuote","Left(qQuoteID,6) = """ & Format(Date,"yymmdd") & """"),64)+1)\r\n<\/pre>\nStep 2<\/h6>\n
Example two<\/h5>\n
![\"123456789B\"](\"http:\/\/regina-whipp.com\/blog\/wp-content\/uploads\/2014\/09\/123456789B.png\")
<\/p>\nStep 1<\/h6>\n
\r\n Dim intRecCount As Integer\r\n Dim db As DAO.Database\r\n \r\n Set db = DBEngine(0)(0)\r\n 'We need to know how many Property Cards there are to determine if we are starting a *A* or some other letter\r\n intRecCount = DCount("ppParcelNumber", "tblPropertyProfile", "[ppParcelNumber] = '" & Me![txtParcelNumber] & "'") - 1\r\n\r\n If Me.Dirty Then\r\n Me.Dirty = False\r\n End If\r\n\r\n If Me.NewRecord Then\r\n MsgBox "Select an Property Card to duplicate!", vbInformation, "Duplicate"\r\n Else\r\n 'If there is only one Property Card assign it an *A*\r\n If intRecCount = 0 Then\r\n Me.cboFormID = "A"\r\n End If\r\n \r\n 'Duplicate the main record\r\n With Me.RecordsetClone\r\n .AddNew\r\n !ppOwner = Me.txtOwner\r\n !ppAddress = Me.txtAddress\r\n !ppLegal = Me.txtLegal\r\n !ppDTEID = Me.cboDTEID\r\n !ppPropertyClassificationID = Me.cboPropertyClassificationID\r\n 'Start the lettering from *B* on.\r\n !ppFormID = Chr(Asc("B") + intRecCount)\r\n !ppParcelNumber = Me.txtParcelNumber\r\n !ppCountyFIPSID = Me.cboCountyFIPSID\r\n .Update\r\n .Bookmark = .LastModified\r\n Me.Bookmark = .LastModified\r\n Me.cboParcelNumber = Me.txtPropertyID\r\n Me.cboParcelNumber.Requery\r\n End With\r\nEnd If\r\nSet db = Nothing\r\n<\/pre>\n\r\n If IsNull(Me.txtRevisionID) Then\r\n Me.txtRevisionID = "A"\r\n Else\r\n Me.txtRevisionID = Chr(Asc(Me.txtRevisionID) + 1)\r\n End If\r\n<\/pre>\n
\nT<\/strong><\/b>his method grabs the first three letters of the Company Name. The Company name used in this example is Access Diva. No worries though, if you have two Companies that have the same first three letters it will increment the numeric side to keep the ID unique. This only becomes a problem if you have more than 9,999 Companies that have the identical first three letters.<\/p>\n![\"ACC0001\"](\"http:\/\/regina-whipp.com\/blog\/wp-content\/uploads\/2014\/09\/ACC0001.png\")
<\/p>\n\r\nDim lngCountIDs As Long\r\n 'Find how many Companies start with the same three letters and count plus one\r\n lngCountIDs = Nz(DCount("Right(cCompanyID,3),", "tblCompanies", "Left(cCompanyID,3) = '" & Left(Me.txtCompanyName, 3) & "'"), 0) + 1\r\n\r\n Me.txtCompanyID = StrConv(Trim(Left(Me.txtCompanyName, 3)), vbUpperCase) & Format(lngCountIDs, "0000")\r\n DoCmd.RunCommand acCmdSaveRecord\r\n<\/pre>\n
\n<\/strong>Always want it to start with specific letter(s)…\u00a0 This method starts every Sequential ID with the same letters.\u00a0 Ultimately, you could have a table that determines the Prefix if you want to *tag* which Department, Section or Index Key you assigned the number to.<\/p>\n![\"KB\"](\"http:\/\/regina-whipp.com\/blog\/wp-content\/uploads\/2014\/09\/KB.png\")
<\/p>\n\r\nMe.txtFFESpecification = "KB" & Nz(DMax("ffeSpecificationID", "tblFFESpecifications"), 0) + 1\r\n<\/pre>\n![\"BR\"](\"http:\/\/regina-whipp.com\/blog\/wp-content\/uploads\/2014\/09\/BR.png\")
<\/p>\n\r\nMe.txtFFESpecificationID = Me.cboIndexKeyID.Column(1) & Nz(DMax("ffeSpecificationID", "tblFFESpecifications"), 0) + 1\r\n<\/pre>\n![\"BRwithHyphen\"](\"http:\/\/regina-whipp.com\/blog\/wp-content\/uploads\/2014\/09\/BRwithHyphen.png\")
<\/p>\n\r\nMe.txtFFESpecificationID = Me.cboIndexKeyID.Column(1) & "-" & Nz(DMax("ffeSpecificationID", "tblFFESpecifications"), 0) + 1\r\n<\/pre>\n
\n<\/strong>Only want to go to a specific number…<\/p>\n\r\n Dim iNext As Integer iNext = Nz(DMax("[ID]", "[tablename]")) + 1 If iNext >= 10000 Then\r\n Cancel = True\r\n MsgBox "All ID's have been used, shut off the PC and go home.", vbOKOnly\r\n Else Me![ID] = iNext\r\n End If\r\n<\/pre>\n
\n<\/strong>Create a Function for use with more than one table\u2026<\/p>\n\r\nFunction lngSequentialID(strID As String, strTable As String) As Long\r\n lngSequentialID = Nz(DMax(strID, strTable), 0) + 1\r\nEnd Function\r\n<\/pre>\n
\r\nlngSequentialID("YourFieldName", "YourTableName")\r\n<\/pre>\n
\n<\/strong>When you move the focus to a new (blank) record on a form and then create a new record by typing in a control, the following sequence of events occurs:<\/p>\n
\n<\/strong>Auto Increment a Value http:\/\/www.devhut.net\/2010\/10\/30\/ms-access-auto-increment-a-value\/<\/a>
\n<\/strong>Sequential Numbering\u00a0http:\/\/scottgem.wordpress.com\/2009\/11\/25\/sequential-numbering\/
\n<\/a>Tech on the Net http:\/\/www.techonthenet.com\/access\/modules\/sequential_nbr4.php
\n<\/a>FMS http:\/\/www.fmsinc.com\/microsoftaccess\/autonumber%20field\/creating.asp<\/a><\/p>\n