将现有的 ID 按后三位降序排序,取出最大的,在自加 1 即可!Dim cSql As String Dim nId As String Dim adoCnn As New ADODB.Connection Dim adoRst As ADODB.Recordset... '用 adoCnn 连结数据库cSql = "SELECT * FORM 表 ORDER BY RIGHT(ID,3) DESC" Set adoRst = adoCnn.Execute(cSql, , adCmdText) With adoRst If .RecordCount = 0 Then nId = "001" Else .MoveFirst nId = Format(Val(Right(!ID, 3)) + 1, "000") End If .AddNew !ID = "XS" + Format(Date, "YYYYMMDD") + nId .Update End With'或者以下语句也可以: cSql = "SELECT MAX(RIGHT(ID,3)) AS nID FROM 表" Set adoRst = adoCnn.Execute(cSql, , adCmdText) With adoRst If .RecordCount = 0 Then nId = "001" Else nId = Format(Val(!nID) + 1, "000") End If .AddNew !ID = "XS" + Format(Date, "YYYYMMDD") + nId .Update End With
1.先用排序方法找到流水号最大的记录,并得到后三位,如果没有记录则为000;
2.使用vb的format函数得到下一个流水号
流水号="XS" & format(date,"yyyymmdd") & format(cstr(int(上一条的后三位)+1),"000")
Dim nId As String
Dim adoCnn As New ADODB.Connection
Dim adoRst As ADODB.Recordset... '用 adoCnn 连结数据库cSql = "SELECT * FORM 表 ORDER BY RIGHT(ID,3) DESC"
Set adoRst = adoCnn.Execute(cSql, , adCmdText)
With adoRst
If .RecordCount = 0 Then
nId = "001"
Else
.MoveFirst
nId = Format(Val(Right(!ID, 3)) + 1, "000")
End If
.AddNew
!ID = "XS" + Format(Date, "YYYYMMDD") + nId
.Update
End With'或者以下语句也可以:
cSql = "SELECT MAX(RIGHT(ID,3)) AS nID FROM 表"
Set adoRst = adoCnn.Execute(cSql, , adCmdText)
With adoRst
If .RecordCount = 0 Then
nId = "001"
Else
nId = Format(Val(!nID) + 1, "000")
End If
.AddNew
!ID = "XS" + Format(Date, "YYYYMMDD") + nId
.Update
End With