自动编号代码,觉得太长了~可以简化吗?

K仔

Sub get_new_number()
Dim mydb As New ADODB.Connection
Dim temp_id As Double
Dim temp_no As String
Dim temp_int As Double
Dim temp_len As Double
mydb.Open SqlConnection
Dim rsX As ADODB.Recordset
Set rsX = New ADODB.Recordset
Set rsX.ActiveConnection = mydb
rsX.Source = "select max([swatch_no]) as max_id from [T - color window]"
rsX.Open , , adOpenForwardOnly, adLockReadOnly

If IsNull(rsX![max_id]) = True Or Trim(rsX![max_id]) = "" Then
   rsX.Close
   serial_no = "G00001"
   Else
   temp_no = rsX![max_id]
   rsX.Close
   temp_int = CDbl(Right(temp_no, 5)) + 1
   temp_len = Len(CStr(temp_int))
   If temp_len = 1 Then
   serial_no.Value = "G0000" & temp_int
   End If
   If temp_len = 2 Then
     serial_no.Value = "G000" & temp_int
   End If
   If temp_len = 3 Then
    serial_no.Value = "G00" & temp_int
   End If
   If temp_len = 4 Then
     serial_no.Value = "G0" & temp_int
   End If
   If temp_len = 5 Then
     serial_no.Value = "G" & temp_int
   End If
   
End If

mydb.Close
End Sub

这是我一位同事写的自动编号按钮代码,觉得太多条件了~能否简化呢?[em06]

K仔

怎麼沒人理啊???[em06]

sweetduck

他的代码是编号不够位数以0补齐,写得很好嘛

海狸先生

Sub get_new_number()
Dim mydb As New ADODB.Connection
Dim rsX As New ADODB.Recordset
Dim temp_no As String
Dim temp_id As Double, temp_int As Double

mydb.Open SqlConnection
rsX.Open "select max([swatch_no]) as max_id from [T - color window]", mydb, adOpenForwardOnly, adLockReadOnly


If IsNull(rsX![max_id]) Or Trim(rsX![max_id]) = "" Then
   serial_no = "G00001"
Else
   temp_no = rsX![max_id]
   temp_int = CDbl(Right(temp_no, 5)) + 1
   If Len(CStr(temp_int)) <= 5 Then serial_no.Value = "G" & String("0", 5 - Len(CStr(temp_int))) & temp_int
End If
rsX.Close
mydb.Close
End Sub

tz-chf

serial_no.Value = "G" & right("0000" & temp_int,5)

temp_int不要超过5位

K仔

麻煩海狸先生了,我收好了呵呵~

AND Why temp_int不要超过5位 ?

我是個超級小白不太懂VBA啊......

我同事如果用這段呢?   serial_no.value="G" & format("00000",temp_int)

[此贴子已经被作者于2006-3-30 9:15:30编辑过]

leanechen

正好我需要这样的代码，谢谢了