Customising an autonumber

[Daniel Lees]

I am implementing an access database and need an autonumber field to
automatically fill in when a new record is created. I need the field to be in
the following format...

QN then 5 digit number starting from 06700 or around there then the last two
digits of the year on the end.

EG

QN0676306

Can any1 help me with this?

Cheers

Danny

 

[Douglas J. Steele]

Why? As in why are you using a so-called "intelligent key" (where that isn't
a complimentary thing)?

You're trying to store 3 separate pieces of information in a single field.
That's a violation of database normalization principles. You should store
them as 3 separate fields. You can always create a query that concatenates
them into a single value for display purposes, and use the query wherever
you would otherwise have used the table.

If you must, though, you'd put logic in the form's BeforeInsert event to
check what the highest value used to date is, and assign the next number.

Something like the following untested air-code:

Dim strNextNumber As String
Dim varLastNumber As Variant

varLastNumber = DMax("IDField", "MyTable", "IDField ='QN?????" &
Format(Date(), "yy") & "'")
If IsNull(varLastNumber) Then
strNextNumber = "QN06700" & Format(Date(), "yy")
Else
strNextNumber = "QN" & _
Format(CLng(Mid(varLastNumber, 3, 5)) + 1, "00000") & _
Format(Date(), "yy")
End If

This assumes that you want to restart numbering at 06700 each new year.

 

[BruceM]

Does the five digit number reset each year? If not, see this link for one
way of adding an incremented number to new records:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb
The simplest thing would probably be to enter 6700 in the field (I will call
it YourID) for the first record, then implement Roger's method for
successive records.
If there is a date field in the record (I will call it YourDateField) then
it is just a matter of adding something like the following to the Control
Source of an unbound text box:
= "QN" & Format([YourID],"00000") & Format([YourDateField],"yy")
There is no need to store the full number. It can be displayed whenever you
like.
If a form or report is based on a query, you could put this into the top of
a blank column in the query design grid:
CompleteID: "QN" & Format([YourID],"00000") & Format([YourDateField],"yy")
Then you can use CompleteID as the Control Source of a text box.
Substitute your own field names, of course.

 

[Pieter Wijnen]

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim Db AS DAO.Database
Dim Rs As DAO.Recordset
Dim thQ As string
Dim MaxNo as Long

Set Db = Access.CurrentDb
thQ = "SELECT MAX(Mid(A.QN,3,5)) AS MaxNo FROM TheTable A WHERE A.QN Is
Not Null AND Right(A.QN,2) = Format(Date(),'yy')"
MaxNo = Nz(Rs.Fields(0).Value,6699) + 1
Rs.Close : Set Rs = Nothing
Me.QN.Value = "QN" & Format(MaxNo,"00000") & Format(Date(),"yy")
set Db = nothing

End Sub

HTH

Pieter

 

[Pieter Wijnen]

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim Db AS DAO.Database
Dim Rs As DAO.Recordset
Dim thQ As string
Dim MaxNo as Long

Set Db = Access.CurrentDb
thQ = "SELECT MAX(Mid(A.QN,3,5)) AS MaxNo FROM TheTable A WHERE A.QN Is
Not Null AND Right(A.QN,2) = Format(Date(),'yy')"
MaxNo = Nz(Rs.Fields(0).Value,6699) + 1
Rs.Close : Set Rs = Nothing
Me.QN.Value = "QN" & Format(MaxNo,"00000") & Format(Date(),"yy")
set Db = nothing

End Sub

HTH

Pieter

I am implementing an access database and need an autonumber field to
automatically fill in when a new record is created. I need the field to be
in
the following format...

QN then 5 digit number starting from 06700 or around there then the last
two
digits of the year on the end.

EG

QN0676306

Can any1 help me with this?

Cheers

Danny

--