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!!!!