SQL in VBA - Object 424 - Object Required

[herghost]

Hi guys

I am getting the above error when executing my code, can anyone spot anything obvious?

Dim conn As ADODB.Connection    Dim rs As ADODB.Recordset
    Dim sConnString As String
    userid = GetName(2)
    sConnString = "Provider=SQLOLEDB;REMOVEDREST"
    
   
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    
    conn.Open sConnString
    Set rs = conn.Execute("SELECT polNumber FROM WOFT_tbl_clients WHERE staffName = '" & userid & "';")
 
    If Not rs.EOF Then
    rs.MoveFirst
    
    Do Until rsData.EOF
    Set casesheet.Columns(1) = rs![polNumber]
    rs.MoveNext
    Loop
    rs.Close
    Else
        MsgBox "Error: No records returned.", vbCritical
    End If


    
    If CBool(conn.State And adStateOpen) Then conn.Close
    Set conn = Nothing
    Set rs = Nothing


End Sub

Basically I am opening a worksheet control in a userform in vba and trying to paste the database results to it.

getName is a function which returns the excel user.

Cheers

 

[Sektor]

1. What's the intention of this code?

Set casesheet.Columns(1) = rs![polNumber]

2. Change code to this:

If CBool(conn.State And adStateOpen)[CODE] = adStateOpen

Then conn.Close[/CODE]

 

[herghost]

I have now noticed I had an error on the Do unti line, however I have changed this from rsData. to rs. and now have a new error!

Object doesn't support this property or method (Error 438)

 

[Sektor]

You use "rsData" variable in "Do Until" line whereas other places have "rs" variable.
And you can't use "Set" with Columns property. Anyway, your intention is not clear what you try to do. May be this:

Dim i As Long

i=1
Do Until rs.EOF
    casesheet.Cells(i) = rs("polNumber")
    i = i + 1
    rs.MoveNext
Loop

 

[herghost]

1. What's the intention of this code?

Set casesheet.Columns(1) = rs![polNumber]

2. Change code to this:

If CBool(conn.State And adStateOpen) = adStateOpen Then conn.Close

1) To print the results in column 1 (a:a), I have removed the SET command as I reliase id did not need it, and it now prints the 1st result from the db all the way down the column

2) What should I be replacing with this?

Thanks for your reply!

 

[herghost]

Sorry, there is no notification made if another reply has been made!

Yes that is my intention, but to print vertically down the sheet not horizontally, but leave the 1st cell blank?

Thanks, this is the closest I have got!

 

[Sektor]

Then try this:

Sub SomeSub()
    
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String
    Dim i As Long
    
    UserId = GetName(2)
    sConnString = "Provider=SQLOLEDB;REMOVEDREST"
    
   
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    conn.Open sConnString
    Set rs = conn.Execute("SELECT polNumber FROM WOFT_tbl_clients WHERE staffName = '" & UserId & "';")
 
    i = i + 1
    If Not rs.EOF Then
        rs.MoveFirst
        Do Until rsData.EOF
            casesheet.Cells(i, 1) = rs![polNumber]
            rs.MoveNext
            i = i + 1
        Loop
        rs.Close
    Else
        MsgBox "Error: No records returned.", vbCritical
    End If
    If CBool(conn.State And adStateOpen) = adStateOpen Then conn.Close
    
    Set conn = Nothing
    Set rs = Nothing
End Sub

 

[herghost]

Thanks - This now shows "Invalid procedure call or argument (Error 5)"

Then try this:

Sub SomeSub()
    
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String
    Dim i As Long
    
    UserId = GetName(2)
    sConnString = "Provider=SQLOLEDB;REMOVEDREST"
    
   
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    conn.Open sConnString
    Set rs = conn.Execute("SELECT polNumber FROM WOFT_tbl_clients WHERE staffName = '" & UserId & "';")
 
    i = i + 1
    If Not rs.EOF Then
        rs.MoveFirst
        Do Until rsData.EOF
            casesheet.Cells(i, 1) = rs![polNumber]
            rs.MoveNext
            i = i + 1
        Loop
        rs.Close
    Else
        MsgBox "Error: No records returned.", vbCritical
    End If
    If CBool(conn.State And adStateOpen) = adStateOpen Then conn.Close
    
    Set conn = Nothing
    Set rs = Nothing
End Sub

 

[herghost]

Actually ignore me Now working, well apart from it starts from row 1 still, but I will try and work that out Your help is greatly appreciated, I have been trying to figure this out for 2 days before I gave in and asked

 

[Sektor]

Oh! Change rs![polNumber] to rs!polNumber or rs(polNumber").