How do I set up a form with a sequential number?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am setting up a form to update a quote log in Access 2000. The original
data is imported from an excel spreadsheet. I need to create a form that
will automatically generate the next quote number based on the previous quote
number. For example all quote #'s for 2005 begin with 05. The current
number range is 05001 through 05345. Beginning in 2006 the quote numbering
will start over at 06001. I have very limited experience with Access and
just cannot figure out a way to make Access generate the number that I am
looking for.
 
Set the default value in your table's column to something like this:

Format(Date,"YY") & DMax("TableFieldName", "TableName",
"Left$(TableFieldName) = " & Format(Date,"YY"))

Replace the parameters TableFieldName and TableName with your own table
and field names.
 
I am setting up a form to update a quote log in Access 2000. The original
data is imported from an excel spreadsheet. I need to create a form that
will automatically generate the next quote number based on the previous quote
number. For example all quote #'s for 2005 begin with 05. The current
number range is 05001 through 05345. Beginning in 2006 the quote numbering
will start over at 06001. I have very limited experience with Access and
just cannot figure out a way to make Access generate the number that I am
looking for.
Click to expand...

This is called an "Intelligent Key" - not a compliment, I fear. You're
storing two disparate pieces of information in one field. If you're
stuck with this field definition, you could use code like the
following in the Form's BeforeInsert event:

Private Sub Form_Insert(Cancel as Integer)
Dim strQuoteNo As String
Dim iNum As Integer
strQuoteNo = NZ(DMax("[Quote #]", "[tablename]", "[Quote #] LIKE '" _
& Format(Date(), "yy") & "'"), "00000")
iNum = Val(Mid(strQuoteNo, 3) ' extract the sequential number
If iNum >= 999 Then
MsgBox "Go home until January, no more quote numbers available", _
vbOKOnly
Cancel = True
Else
Me![Quote #] = Format(Date(), "yy") & Format(iNum, "000")
End If
End Sub


John W. Vinson[MVP]
 
Set the default value in your table's column to something like this:

Format(Date,"YY") & DMax("TableFieldName", "TableName",
"Left$(TableFieldName) = " & Format(Date,"YY"))

Replace the parameters TableFieldName and TableName with your own table
and field names.
Click to expand...

You can do this in a Form control's Default property, but do note that
a Table default value cannot call any user functions, nor can it
reference any data in any of your tables.

John W. Vinson[MVP]
 
Back
Top