Problem with a Function and Question on Passing the Data to a Variable

btp111

Registered User.
Local time
Today, 08:54
Joined
May 8, 2008
Messages
15
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.
 
At a quick glance, try:

strSQL = "Select Max(ManualAutoNumber) As MyNumber From ManualNextNumber"

and

UniqueNumber = rs.fields("MyNumber").value + 1

Pete.
 
Still returning a 1 to the text box

Peter. Thanks but I tried changing those 2 lines and ran the app again but it still errors out. The only way I know is that the txt field is set to a 1 and not the Max # that I expected. Any other suggestions?

Thanks Again
 
When you say 'errors out' what do you mean? What error are you getting? Have you tried stepping through the code to see what line is creating the error.
Try putting 'Debug.Print Err.Number & " : " & Err.Description' in your error trap then read what shows up in the immediate window.
 
Also - I can see that you've got :

Set cmd2.ActiveConnection = cn1

But cn1 is not declared?

I haven't used ADODB recordsets much before, but am offering up what I can!

Pete
 
Just curious but why not go simple and use a DMax to get the number?
 
Problem with Creating Unique Number

91 : Object variable or With block variable not set
Hi Doco, I have put that statement into the code and the error is above. I am not sure why I would be getting this error. Any insight is greatly appreciated.

Bob, I am not sure how I would use a DMAX here as I need an incrementing number. Each time the form is loaded I need it to increment the # and I need to store it to send the # up to my SQL DB via the INSERT statement that I have. I understand I could use DMAX to find the max # of a field in the DB but I am not sure how to use that to tie back to a central SQL Svr DB. Thought it could only be used with a Local DB in Access.

All help is greatly appreciated. If I could get this to work, one way or another, we could use the DB.
Thanks again.
 
I am on the road today and am not real famililiar with ADOX either. But what the error is telling you is you are not binding your one (or all) of your ADOX objects. Also I suspicion overkill in your declarations.
Is the data you are seeking resident in the database the code resides? If so, then why not just use DMAX as Bob suggested? Search your online help for that function.

Else heres a clue how to use DAO

Code:
Dim rs As DAO.Recordset
Dim lMax As Long
Dim SQL As String
 
'    quote from earlier post
SQL = "Select Max(ManualAutoNumber) As MyNumber From ManualNextNumber"
 
Set rs = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)
 
lMax = rs.Fields("MyNumber") +1

Or such like...
 
91 : Object variable or With block variable not set
Bob, I am not sure how I would use a DMAX here as I need an incrementing number. Each time the form is loaded I need it to increment the # and I need to store it to send the # up to my SQL DB via the INSERT statement that I have.
The typical use is to use DMax("fieldName","tableName", "Anycriteriahere") + 1 to assign the next number.
I understand I could use DMAX to find the max # of a field in the DB but I am not sure how to use that to tie back to a central SQL Svr DB. Thought it could only be used with a Local DB in Access.
This is true, but nowhere in your post did you mention you were trying to do this on a SQL Server (remember we don't read minds).

If on SQL Server then I would, if possible, create a custom function to get the Max number and add one and then call it using a stored procedure (or just write it as a stored procedure).
 
As for the error 91, you need to instantiate your connection object first either with a

Set cn = "something"

you can't just start setting the properties. So, if you just change the declaration to:

Code:
Dim cn As New ADODB.Connection

you should be good on that one.
 
Thanks

Thanks for the help. I ended up (probably not the best way) using a local table to grab my data and increment the manualautonumber. The code is below:
im LocalConnection As New ADODB.Connection
Set LocalConnection = CurrentProject.AccessConnection
Dim rsAdd As New ADODB.Recordset
If IsNumeric(Me.txttest11) Then
'Assign updatable cursor and lock type properties.
rsAdd.CursorType = adOpenDynamic
rsAdd.LockType = adLockOptimistic
'Open the Recordset object.
rsAdd.Open "AutoNumber", LocalConnection, , , adCmdTable
With rsAdd
.AddNew
!ManualAutoNumber = (Me.txttest11 + 1)
.Update
.Close
End With
Else
MsgBox "Error"
End If
LocalConnection.Close

Basically, upon every open I am incrementing the # by 1 and using my returned # as my variable for the insert into SQL.
THanks again.
 

Users who are viewing this thread

Back
Top Bottom