This gets the maximum number from the
Voucher_Number
tblInvoiceLog
Source
Me.Source.Value
Private Sub Source_AfterUpdate()
Dim rs As ADODB.Recordset, MyVal
Set rs = New ADODB.Recordset
rs.Open "SELECT MAX(Voucher_Number) from tblInvoiceLog", CurrentProject.Connection
rs.MoveFirst
MyVal = rs.Fields(0).Value
Me.Voucher_Number.Value = MyVal + 1
rs.Close
Set rs = Nothing
End Sub
You should add a WHERE clause to your SQL query
Private Sub Source_AfterUpdate()
Dim rs As ADODB.Recordset, MyVal
Dim SQL as String
Set rs = New ADODB.Recordset
' If Source field is STRING type
SQL = "SELECT MAX(Voucher_Number) from tblInvoiceLog WHERE [Source]='" & Me.Source.Value & "'"
' If Source field is NUMBER type
SQL = "SELECT MAX(Voucher_Number) from tblInvoiceLog WHERE [Source]=" & Me.Source.Value
rs.Open SQL, CurrentProject.Connection
rs.MoveFirst
MyVal = rs.Fields(0).Value
Me.Voucher_Number.Value = MyVal + 1
rs.Close
Set rs = Nothing
End Sub