سلام
اين كد رو تو يك ماژول كپي كن :
Function DMax(ByVal strFieldName As String, ByVal strRecSource As String, _
Optional strCriteria As String) As Long
' DMax(expr, domain , criteria)
' Esample: vntReturnVal = DMax("Title", "Titles", "Au_ID = " & _
' Format(data1.Recordset("Au_ID")))
'
'
' Argument Description
' ----------------------------------------------------------------
' expr String expression identifying the field that contains
' the data you want to return. Operands in expr can
' include the name of a table field.
'
' domain String expression identifying the records that
' constitute the record set. It can be a table name,
' query name, or SQL expression that returns data.
'
' criteria Optional string expression used to restrict the range
' of data on which DMax is performed. For example,
' criteria could be the SQL expression's WHERE clause
' without the word WHERE. If criteria is omitted, DMax
' evaluates expr against the entire record set.
Dim rs, rs1 As ADODB.Recordset
Dim Cnxn As ADODB.Connection
Dim strCnxn, sql As String
Set Cnxn = New ADODB.Connection
strCnxn = "Provider='SQLOLEDB';Data Source='.';" & _
"Initial Catalog='YourDataBaseName';User ID='YourId';Password='YourPass';"
Cnxn.Open strCnxn
If strCriteria <> "" Then
sql = "SELECT " & strFieldName & " FROM " & strRecSource & " where " & strCriteria
Else
sql = "SELECT " & strFieldName & " FROM " & strRecSource
End If
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open sql, Cnxn, adOpenDynamic, adLockOptimistic, 1
'
'Dim dsResult As Recordset
Dim vntCurrentVal As Variant
On Local Error GoTo Error_DMax:
With rs
If Not .EOF Then
' Set variable vntCurrentVal
vntCurrentVal = .Fields(strFieldName)
Do While Not .EOF
' Cycle through each value from the row to find the largest
If .Fields(strFieldName) > vntCurrentVal Then
vntCurrentVal = .Fields(strFieldName)
End If
.MoveNext
Loop
' Return maximum value found
DMax = vntCurrentVal
Else
DMax = 0
End If
.Close
End With
DMax_Exit:
Exit Function
Error_DMax:
' No criteria specified
If Err <> 3077 Then
'Display the error and get out
MsgBox "Error (" & Err & "): " & Error(Err) & " in DMax", _
vbCritical, APP_NAME
Resume DMax_Exit:
Else
Resume Next
End If
End Function
بعد ميتوني عين تابع Dmax اكسس ازش استفاده كني.