Hi,
I have the below function that I thought would pull the Max # out of a field in my SQL table.
strSQL = "Select (Max(ManualAutoNumber))+1 from ManualNextNumber"
Public Function UniqueNumber() As Integer
On Error GoTo Err_UniqueNumber
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String
Dim cmd2 As New ADODB.Command
cn.Provider = "sqloledb"
cn.Properties("Data Source").Value = "HLAERP1"
cn.Properties("Initial Catalog").Value = "PS_CRP"
cn.Properties("Integrated Security").Value = "SSPI"
cn.Open
strSQL = "Select (Max(ManualAutoNumber))+1 from ManualNextNumber"
Set cmd2.ActiveConnection = cn1
cmd2.CommandText = strSQL
cmd2.CommandType = adCmdText
cmd2.CommandTimeout = 15
Set rs = New ADODB.Recordset
rs.Open strSQL, cn, 1
UniqueNumber = rs.Fields("ManualAutoNumber")
'("ManualAutoNumber")
rs.Close
cn.Close
Exit_UniqueNumber:
Set rs = Nothing
Set cn = Nothing
Exit Function
Err_UniqueNumber:
UniqueNumber = 1
Resume Exit_UniqueNumber
End Function
On Form Load I have the following:
Call UniqueNumber
newnum = UniqueNumber
Me.txttest1 = newnum
However, the value in the txt box is 1. I don't know where to begin to find why the function is failing. Any ideas are greatly appreciated.
Thanks.
I have the below function that I thought would pull the Max # out of a field in my SQL table.
strSQL = "Select (Max(ManualAutoNumber))+1 from ManualNextNumber"
Public Function UniqueNumber() As Integer
On Error GoTo Err_UniqueNumber
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String
Dim cmd2 As New ADODB.Command
cn.Provider = "sqloledb"
cn.Properties("Data Source").Value = "HLAERP1"
cn.Properties("Initial Catalog").Value = "PS_CRP"
cn.Properties("Integrated Security").Value = "SSPI"
cn.Open
strSQL = "Select (Max(ManualAutoNumber))+1 from ManualNextNumber"
Set cmd2.ActiveConnection = cn1
cmd2.CommandText = strSQL
cmd2.CommandType = adCmdText
cmd2.CommandTimeout = 15
Set rs = New ADODB.Recordset
rs.Open strSQL, cn, 1
UniqueNumber = rs.Fields("ManualAutoNumber")
'("ManualAutoNumber")
rs.Close
cn.Close
Exit_UniqueNumber:
Set rs = Nothing
Set cn = Nothing
Exit Function
Err_UniqueNumber:
UniqueNumber = 1
Resume Exit_UniqueNumber
End Function
On Form Load I have the following:
Call UniqueNumber
newnum = UniqueNumber
Me.txttest1 = newnum
However, the value in the txt box is 1. I don't know where to begin to find why the function is failing. Any ideas are greatly appreciated.
Thanks.