[Newbie] Need Help with Serial Numbers

[Doughboy]

Hello,

I am currently getting my feet wet with Access 2000, after years of
working with Filemaker Pro, so please bear with me.

I have a relatively simple Access database, in which records are added
via a web form. I'm having difficulty generating unique serial numbers
for each record. The serial numbers are of the form atl00001,
atl00002, etc. As for the number portion, I've defined a field called
serialNumber, which is of type AutoNumber, with a format of "00000".

I've also defined a field called serialNumberFinal, which is of type
text, but I can't seem to define the proper expression (calculation)
for it. I was hoping to define it using the Default Value field
property, but no luck. I then tried creating a dummy form with the
serialNumberFinal field on it, and setting the AfterEvent property to
the following expression:

="atl" & Str([tableName]![serialNumber])

 

[Joseph Meehan]

Hello,

I am currently getting my feet wet with Access 2000, after years of
working with Filemaker Pro, so please bear with me.

I have a relatively simple Access database, in which records are added
via a web form. I'm having difficulty generating unique serial numbers
for each record. The serial numbers are of the form atl00001,
atl00002, etc. As for the number portion, I've defined a field called
serialNumber, which is of type AutoNumber, with a format of "00000".

You may want to change that. Autonumber is not designed to produce
consecutive number. It is designed to produce unique numbers. It is not
very good for what you are doing and has frustrated many many people.
Generally it should only be used for internal record identification.

I suspect someone will post some code that will provide a incremented
"numbers" as text in the format you are looking for. Sorry I don't have any
myself.

 

[John Flanagan]

Create a query something like "SELECT Max(n.x) AS MaxOfx FROM n;"
For this example the query name it LastNumInN

The function would look something like this

Function NextNumInN()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("LastNumInN", dbOpenForwardOnly)
With rs
If .EOF And .BOF Then
NextNumInN = 1
Else
NextNumInN = !maxofx + 1
End If
End With
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Function

Note: If all of your serial numbers begin with atl then there's no need to
store the atl bit. If not, that's a slightly different procedure

John