Copy field value.............................FMR

I inherited a database and in order to keep from rebuilding it I want to
change the way a value is populated in a form field.

Basically, the form has an order ID (autonumber) and an order number (text
with number) I want to change the order number to a pure number and then
have it figure what number should be next and auto populate it. I thought of
just coping the autonumber value, but having it figure the next number may be
easier.

What would the module command look like?

I have this in another database:
****************************
Option Compare Database

Public Function NextOrderNo() As String

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim strSQL As String

strSQL = "SELECT Max(CLng(Mid([Ticket # :], 4))) " & _
"FROM [Routing Details - Table]"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

NextOrderNo = "MCO" & rs.Fields.Item(0).Value + 1

rs.Close
Set rs = Nothing
Set db = Nothing

End Function

I hate to mess with what is working, but I am getting complaints about the
text & number field not sorting using the digits as numbers. Ex: MCO1,
MCO11, MCO111, MCO2, MCO3 etc…

"RA" wrote in message
...
I inherited a database and in order to keep from rebuilding it I want to
change the way a value is populated in a form field.

Basically, the form has an order ID (autonumber) and an order number (text
with number) I want to change the order number to a pure number and then
have it figure what number should be next and auto populate it. I thought
of
just coping the autonumber value, but having it figure the next number may
be
easier.

What would the module command look like?

I have this in another database:
****************************
Option Compare Database

Public Function NextOrderNo() As String

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim strSQL As String

strSQL = "SELECT Max(CLng(Mid([Ticket # :], 4))) " & _
"FROM [Routing Details - Table]"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

NextOrderNo = "MCO" & rs.Fields.Item(0).Value + 1

rs.Close
Set rs = Nothing
Set db = Nothing

End Function

I hate to mess with what is working, but I am getting complaints about the
text & number field not sorting using the digits as numbers. Ex: MCO1,
MCO11, MCO111, MCO2, MCO3 etc…


You could always generate your numbers with leading zeros, so the numeric
part is always the same length:

If rs.EOF Then
NextOrderNo = "MCO0000001"
Else
NextOrderNo = "MCO" & Format(rs.Fields.Item(0).Value + 1, "0000000")
End If

You'd have to go back and change the ones you previously created, though, so
that they will sort properly with the others. An update query could do
that.

Also, the number of digits you specify in your format places a hard maximum
on the number. For example, 7 digits (as in my example above) gives you a
maximum of 9,999,999 items you can number this way.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)