Auto Number field in Access begins from 1. Can we make it begin from any other number we want? How?
Auto Number field in Access begins from 1. Can we make it begin from any other number we want? How?
It is possible, but it needs doing before adding any actual data.
If you wanted it to start at 5000 for instance you can create 4999 blank or dummy records and then delete them.
Or you can use VBA code to create bespoke numbering systems.
As I said the VBA is bespoke, so each one is individual to the user's requirements, some users require the number include the current year and to start back at zero at the beginning of the new year, others require it to include a customer number.
Here are a couple of examples.
Dim year As String, month As String, count As Integer, recount As Integer, rs As Object, zeroes As String, callno As String Dim SQL As String zeroes = "000000": year = Format(Date, "yyyy"): month = Format(Date, "mm") SQL = "SELECT Que_CallNumber.* " & _ "FROM Que_CallNumber " & _ "WHERE DealerCode = " & Me.Dealercode Set rs = CurrentDb.OpenRecordset(SQL) If rs.RecordCount <> 0 Then rs.MoveLast rs.MoveFirst End If recount = rs.RecordCount If recount = 0 Then callno = "000001" Else callno = Str(recount + 1) callno = Right(callno, (Len(callno) - 1)) End If If Len(callno) < Len(zeroes) Then callno = Left(zeroes, Len(zeroes) - Len(callno)) & callno Me.CallNumber = Me.Dealercode & year & month & callno or On Error GoTo Eventerror If Me.NewRecord Then Dim rs As Object, zeroes As String, temps As String, tempv As Integer zeroes = "000" Set rs = CurrentDb.OpenRecordset("qryOrderNos") rs.movelast If rs.RecordCount <> 0 And mid(rs![InvoiceNumber],9,2) = Format(me.[Order Date], "YY") Then tempv = Val(Right(rs![InvoiceNumber], 3)) + 1 temps = Str(tempv) temps = Right(temps, Len(temps) - 1) If Len(temps) < Len(zeroes) Then temps = Left(zeroes, (Len(zeroes) - Len(temps))) & temps Me.InvoiceNumber = Left(rs![InvoiceNumber], 9) & Format(me.[Order Date], "YY") & temps Else Me.InvoiceNumber = "0730/TAK/" & Format(me.[Order Date], "YY") & " - 001" End If rs.Close Set rs = Nothing End If Exit Sub Eventerror: MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
Well, you could try making a copy and then reformat it as an Access 2007 database.
How successful it will be will depend on how many of the new features you have used.
If it is the same as Access 2007, with the Database open you use the top left round button and choose the Save As option.
Make sure it is the copy that you work with.