Regarding Auto Numbering

[Kundan]

Auto Number field in Access begins from 1. Can we make it begin from any other number we want? How?

[OBP]

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.

[Kundan]

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.

Can you give some guidance regarding the said VBA code?

[OBP]

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

[Kundan]

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

Thanks a Lot!!!! It would have been better if you could view my DB. Many problems cannot be expressed clearly through words.

[OBP]

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.

[Kundan]

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.

How to reformat in Access 2007???

[OBP]

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.

[Kundan]

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.

Thanks!!!!