Bạn có thể dùng thử đoạn mã sau nhé
Mình xin lỗi vì các đoạn comment - hihi không dấu
Sub Test()
Debug.Print GetOrder("tbl_Solution", "Heading", , "C:\ts.mdb")
End Sub
Function GetOrder(MyTable As String, MyKeyField As String, Optional MyCriteria As String = "", Optional dbPath As String) As String
' Ham phai de o kieu string - vi ban dat kieu ma la string ma
' MyTable la ten bang can xac dinh ma
' MyKeyField la ten cot can xac dinh danh so tu dong
' MyCriteria la Dieu kien truy van
' Truoc khi dung ham nay, ban can dat tham chieu den bo thu vien DAO 3.xx nhe
' Ham nay chi su dung duoc neu ban dat kieu dinh dang the nhap kho la dong kieu
' Vi du NK#####, PNK######..
' O day toi su dung mot doan thu tuc de tim phan bat dau cua ma ky tu
' Ham RetInitialChar()
Dim rs As Recordset, db As Database
Dim iCond As String, i As Long, iQry As String
'Dim Currentdb As Database 'neu ban o trong access thi thoi - con trong VB thi phai bo comment dong nay nhe
' Lay doan ma danh so
iCond = RetInitialChar(MyTable, MyKeyField, dbPath)
Set db = IIf(dbPath = "", Currentdb, OpenDatabase(dbPath))
' Bay gio mo bang va tim doi tuong lon nhat
iQry = "Select Max(Mid([" & MyKeyField & "]," & Len(iCond) + 1 & ")) as MyField " & _
"from " & MyTable & IIf(MyCriteria = "", ";", " Where " & MyCriteria & ";")
Debug.Print iQry
Set rs = db.OpenRecordset(iQry)
' Tat nhien SQL tren hoi phuc tap mot chut nhung co the giai thich the nay
'Max(Val(Mid([" & MyKeyField & "],Len(" & iCond & ")+1)) de tra lai phan so lon nhat co trong bang
'IIf(MyCriteria = "", ";", "Where " & MyCriteria & ";")) De dua cau dieu kien neu chuoi myCriteria <>0
On Error Resume Next
i = Val(rs.Fields(0))
If Err.Number <> 0 Then
' Co loi - chac la khong co doi tuong nao - tat nhien ban can phai chac chan
' la minh khong co loi trong cau dieu kien
iCond = iCond & Format(1, "000000")
Err.Clear
Else
iCond = iCond & Format(i + 1, "000000")
End If
rs.Close
If dbPath = "" Then Set db = Nothing Else db.Close
GetOrder = iCond
End Function
Private Function RetInitialChar(ByVal MyTable As String, ByVal MyKeyField As String, Optional dbPath As String) As String
' Ham se mo CSDL va tim ma phieu mac dinh, neu khong tim thay
' ham se tu dong gan ma mac dinh la "NK#####"
Dim rs As Recordset, db As Database
Dim MyKey As String, i As Long
'Dim Currentdb As Database 'neu ban o trong access thi thoi - con trong VB thi phai bo comment dong nay nhe
' Mo co so du lieu nhe/ neu bien dbpath khong duoc chi ra - db se duoc gan cho currentdb
' day la doi tuong mac dinh chi den csdl hien thoi cua Access
Set db = IIf(dbPath = "", Currentdb, OpenDatabase(dbPath))
' Mo bang lam viec nhe
Set rs = db.OpenRecordset("Select [" & MyKeyField & "] From [" & MyTable & "];")
On Error Resume Next
MyKey = rs.Fields(0)
For i = 1 To Len(MyKey)
' Kiem tra xem ky tu dang duyet co phai la so hay khong
If InStr("0123456789", Mid(MyKey, i, 1)) <> 0 Then
' Lay doan dau cua ma the va thoat khoi vong lap
MyKey = Left(MyKey, i - 1)
Exit For
End If
Next
' Neu co loi xay ra, chuong trinh se xu ly ngay thu tuc nay
If Err.Number <> 0 Then
' Co loi nao do .. co the la khong co ban ghi nao
MyKey = "NK"
Err.Clear
End If
' Dong doi tuong recordset truoc khi thoat
rs.Close
If dbPath = "" Then Set db = Nothing Else db.Close
' Tra ve gia tri va thoat ra
RetInitialChar = MyKey
End Function