Sequencing numbers

[Herbie]

I have a form that has a report number where the first two
numbers represent the last two digits of the year (this is
in its own text field "txtRepYear", the next part is the
sequential part that is 001, 002, 003, etc....,(in its own
field "txtRepno"), and the last part is a Letter that
represents part of a code that is controlled from
someplace else (this two is in it's own text
field "txtRepCode).

What I would like to know is if it is possible to create a
sort of autonumber field for for the second Text Box
("txtRepno"), that can be bumped up or down should a
record be placed in or taken out (there are 5 yrs worth of
records, and it may be necessary for the user to change
the number should he realize he missed a record.

as it is now, the database is not finished, and is still
being worked on. I have an input mask set up (eg. "00"#)
which is only temporary as I know it will cause problems.

The user wants it to be in a similar format with the zeros
preceding the number so that it will always be 3 digits.

I also have a hidden Autonumber field in the table that is
used as the primary key.

If anyone has any suggestions or ideas, or if this is
something that can only be dreamed of, please let me know.

Many thanks in advance.

 

[Guest]

To increment the Number you can us
DMax("txtRepNo","YourTableName")+
Also check out Q88169 of KB
To have 3 digits the format should be 00
BUT i have 1 questio

The user wants it to be in a similar format with the zeros
preceding the number so that it will always be 3 digits

Do you mean to say the maximum number which will be used is only 999 and not more.


----- Herbie wrote: ----

I have a form that has a report number where the first two
numbers represent the last two digits of the year (this is
in its own text field "txtRepYear", the next part is the
sequential part that is 001, 002, 003, etc....,(in its own
field "txtRepno"), and the last part is a Letter that
represents part of a code that is controlled from
someplace else (this two is in it's own text
field "txtRepCode)

What I would like to know is if it is possible to create a
sort of autonumber field for for the second Text Box
("txtRepno"), that can be bumped up or down should a
record be placed in or taken out (there are 5 yrs worth of
records, and it may be necessary for the user to change
the number should he realize he missed a record

as it is now, the database is not finished, and is still
being worked on. I have an input mask set up (eg. "00"#)
which is only temporary as I know it will cause problems

The user wants it to be in a similar format with the zeros
preceding the number so that it will always be 3 digits

I also have a hidden Autonumber field in the table that is
used as the primary key

If anyone has any suggestions or ideas, or if this is
something that can only be dreamed of, please let me know

Many thanks in advance

 

[Herbie]

I don't know what I'm doing wrong.
I tried the first suggestion and the fields didn't change
at all.

I tried the article in the kb and receive this error

Run-time error '5':
Invalid procedure call or argument.

Here is my code, maybe you can see what I've done wrong?

Function FindMax()

Dim db As Database
Dim mx As Integer
Dim rs As Recordset
Dim rsVal As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("tblOccRep", dbOpenDynaset)
' NOTE: If you are using Access 2.0 the previous line
should be
' Set rs = db.OpenRecordset("Increment",
DB_OPEN_DYNASET)

rs.MoveFirst

rsVal = rs.Fields("[REPORTNO]").Value
' set mx equal to the numeric portion of the field
mx = Right(rsVal, Len(rsVal) - 3)

' loop to make sure you have the maximum number
Do While Not rs.EOF
rsVal = rs.Fields("[REPORTNO]").Value
If Right(rsVal, Len(rsVal) - 3) > mx Then
mx = Right(rsVal, Len(rsVal) - 3)
End If
rs.MoveNext
Loop

' increment the maximum value by one and
' combine the text with the maximum number
FindMax = "BO-" & (mx + 1)

rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

End Function