If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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… |
#2
|
|||
|
|||
Copy field value.............................FMR
"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) |
Thread Tools | |
Display Modes | |
|
|