Help with error 91 variable or with block variable not set 1

[vamoose]

Here is the code. The error appears at the Do Until statement. Any ideas, I am so frustrated. Thanks


Option Compare Database

Private Sub Command0_Click()
Dim SQLa, SQLb, SQLc, SQLd As String
Dim conn As ADODB.Connection: Dim rs As ADODB.Recordset: Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= c:\temp\john.mdb" & ";" & _
"Persist Security Info=False": conn.Open
With rs
Do Until .EOF
SQLa = "SELECT top 1 Plant, Material, [Material Description], RDC from FESA"
Set rs = conn.Execute(SQLa, , adCmdText)
P = rs.Fields("Plant").Value: M = rs.Fields("Material").Value: MD = rs.Fields("Material Description").Value
R = rs.Fields("RDC").Value
SQLb = "SELECT sum(Quantity) as Quan from FESA where Material='" & M & "' and RDC = " & R
Set rs = conn.Execute(SQLb, , adCmdText)
Q = rs.Fields("Quan").Value
SQLc = "Insert Into Results (Plant, Material, [Material Description], Quantity, RDC) values (" & P & ", '" & M & "', '" & MD & "'," & Q & ", " & R & ");"
Set rs = conn.Execute(SQLc, , adCmdText)
SQLd = "Delete * from FESA where Material='" & M & "' and RDC = " & R
Set rs = conn.Execute(SQLd, , adCmdText)
Loop
If .EOF = True Then rs.Close: conn.Close: Set rs = Nothing: Set conn = Nothing
OK = MsgBox("Done.", vbOKOnly + vbInformation, "Production Management Tool"): Exit Sub
End With
End Sub

 

[rjoubert]

You need to set your rs variable.

 

[rjoubert]

With rs
Do Until .EOF
...

You're attempting to loop through a recordset that has not been set.

 

[Golom]

As the other guys said ... but that's not all

Dim SQLa                        As String
Dim SQLb                        As String
Dim SQLc                        As String
Dim SQLd                        As String
Dim conn                        As ADODB.Connection
Dim rs                          As ADODB.Recordset
Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= c:\temp\john.mdb" & ";" & _
                        "Persist Security Info=False": conn.Open
With rs
    Do Until .EOF
        [COLOR=black cyan]' You're redefining the rs recordset[/color]
        [COLOR=black cyan]' within a loop that is looking for[/color]
        [COLOR=black cyan]' the EOF on the recordset![/color]
        SQLa = "SELECT top 1 Plant, Material, [Material Description], RDC from FESA"
        Set rs = conn.Execute(SQLa, , adCmdText)
        P = rs.Fields("Plant").Value
        m = rs.Fields("Material").Value
        MD = rs.Fields("Material Description").Value
        R = rs.Fields("RDC").Value
        [COLOR=black cyan]' ... and you're doing it again![/color]
        SQLb = "SELECT sum(Quantity) as Quan from FESA where Material='" & m & "' and RDC = " & R
        Set rs = conn.Execute(SQLb, , adCmdText)
        q = rs.Fields("Quan").Value
        [COLOR=black cyan]' INSERT doesn't return records[/color]
        SQLc = "Insert Into Results (Plant, Material, [Material Description], Quantity, RDC) values (" & P & ", '" & m & "', '" & MD & "'," & q & ", " & R & ");"
        Set rs = conn.Execute(SQLc, , adCmdText)
        [COLOR=black cyan]' Neither does DELETE[/color]
        SQLd = "Delete * from FESA where Material='" & m & "' and RDC = " & R
        Set rs = conn.Execute(SQLd, , adCmdText)
        [COLOR=black cyan]' There's no 'MoveNext' so you will stay[/color]
        [COLOR=black cyan]' on the same record forever.[/color]
    Loop
    If .EOF = True Then 
       rs.Close
       conn.Close
       Set rs = Nothing
       Set conn = Nothing
    End If
    OK = MsgBox("Done.", vbOKOnly + vbInformation, "Production Management Tool")
 Exit Sub

End With

 

[Golom]

Is this more like what you intended?

Private Sub Command0_Click()
Dim SQL                         As String
Dim conn                        As ADODB.Connection
Dim rs                          As ADODB.Recordset
Dim rt                          As ADODB.Recordset
Dim RA                          As Long
Dim RD                          As Long
Dim nRecs                       As Long

Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source= c:\temp\john.mdb" & ";" & _
                        "Persist Security Info=False"
conn.Open

SQL = "SELECT TOP 1 Plant, Material, [Material Description], RDC from FESA"
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open SQL, conn, , , adCmdText

With rs
    Do Until .EOF
        P = ![Plant]
        m = ![Material]
        MD = ![Material Description]
        R = ![RDC]
        SQL = "SELECT SUM(Quantity) as Quan from FESA " & _
              "WHERE Material='" & m & "' and RDC = " & R
        Set rt = conn.Execute(SQL, , adCmdText)
        q = rt![Quan]
        SQL = "Insert Into Results (Plant, Material," & _
              "[Material Description], Quantity, RDC) " & _
              "VALUES (" & P & ", '" & m & "', '" & MD & _
                       "'," & q & ", " & R & ");"
        conn.Execute SQL, nRecs, adCmdText
        RA = RA + nRecs
        SQL = "Delete * from FESA where Material='" & m & "' and RDC = " & R
        conn.Execute SQL, nRecs, adCmdText
        RD = RD + nRecs
        .MoveNext
    Loop
End With

Set rs = Nothing
Set rt = Nothing
Set conn = Nothing

MsgBox "Records Added   = " & RA & vbCrLf & _
       "Records Deleted = " & RD, _
       vbOKOnly + vbInformation, _
       "Production Management Tool"

End Sub

 

[JerryKlmns]

Golom

Isn't this returning only one record?
SQL = "SELECT TOP 1 ... "
So what's the need of the loop for rst?

Besides that a
.CursorLocation = adUseServer
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly

would be faster (if the loop is needed)

and also closing objects first and then destroying

rst.Close
rt.Close
conn.Close
Set rs = Nothing
Set rt = Nothing
Set conn = Nothing

must be, definetly, things caused by low cafeine in blood ....

 

[vamoose]

Yes, I am selecting the top 1 record, searching for matches on material and rdc, adding the matches quantity together, copying them to another table, deleting all the matches and searching for matches again. This database contains some 15,000 records. Thanks to all those who replied, I really appreciate the help. Thanks.

 

[Golom]

JerryKlmns

Yes, you're right. It does return one record ... but it might also return zero records ... so the loop (or at least some test for EOF before attempting to retrieve fields) is required just for CYA reasons.

I agree with your recordset property specifications. I just use "rs.CursorLocation = adUseClient" out of instinct because server-side cursors have created problems for me in the past.

Arguably, "closing and then destroying" is the "right" way to do it because the close will (it is assumed) allow ADO to properly handle things like flushing the cache that may not get addressed if you just destroy the objects without closing them. That said, I've never had a problem with just destroying an object without first closing it.

 

[PHV]

I've never had a problem with just destroying an object
I agree with most objects BUT recordset ...