Object required error...

[Guest]

I've been pulling my hair out on this "object required" error all day and I
can't figure out where the object is needed. As far as I can tell, everything
ties together. The error is at the "sqlBase =" part of the following code:

Private Sub Userform_Initialize()
Dim db As Database
Dim rs1, rs2, rs3 As Recordset
Dim sqlBase As String
Dim sqlPly As String
Dim sqlCap As String

Set db = OpenDatabase("roofing.mdb")

Set sqlBase = "SELECT * FROM vList WHERE " _
& "vList.mftrName =" & Chr(34) & rsholder2.Caption & Chr(34) & _
" AND vList.prType =" & Chr(34) & rsholder1.Caption & Chr(34) & _
" AND vList.prUse =" & Chr(34) & "base" & Chr(34)

Set sqlPly = "SELECT * FROM vList WHERE " _
& "vList.mftrName =" & Chr(34) & rsholder2.Caption & Chr(34) & _
" AND vList.prType =" & Chr(34) & rsholder1.Caption & Chr(34) & _
" AND vList.prUse =" & Chr(34) & "interply" & Chr(34)

Set sqlCap = "SELECT * FROM vList WHERE " _
& "vList.mftrName =" & Chr(34) & rsholder2.Caption & Chr(34) & _
" AND vList.prType =" & Chr(34) & rsholder1.Caption & Chr(34) & _
" AND vList.prUse =" & Chr(34) & "surfacing" & Chr(34)

Set rs1 = db.OpenRecordset(sqlBase)
Set rs2 = db.OpenRecordset(sqlPly)
Set rs3 = db.OpenRecordset(sqlCap)

With rs1
If Not .BOF Then .MoveFirst
While Not .EOF
With rshot05
.AddItem rs1("prName")
End With
.MoveNext
Wend
End If
End With

With rs2
If Not .BOF Then .MoveFirst
While Not .EOF
With rshot35
.AddItem rs2("prName")
End With
.MoveNext
Wend
End If
End With

With rs3
If Not .BOF Then .MoveFirst
While Not .EOF
With rshot38
.AddItem rs3("prName")
End With
.MoveNext
Wend
End If
End With

Set rs1 = Nothing
Set rs2 = Nothing
Set rs3 = Nothing
db.Close
Set db = Nothing
End Sub

 

[Guest]

Try this
Sub Userform_Initialize()
Dim db As Database
Dim rs1, rs2, rs3 As Recordset
Dim sqlBase As String
Dim sqlPly As String
Dim sqlCap As String

Set db = OpenDatabase("roofing.mdb")

sqlBase = "SELECT * FROM vList WHERE " _
& "vList.mftrName =" & Chr(34) & rsholder2.Caption & Chr(34) & _
" AND vList.prType =" & Chr(34) & rsholder1.Caption & Chr(34) & _
" AND vList.prUse =" & Chr(34) & "base" & Chr(34)

sqlPly = "SELECT * FROM vList WHERE " _
& "vList.mftrName =" & Chr(34) & rsholder2.Caption & Chr(34) & _
" AND vList.prType =" & Chr(34) & rsholder1.Caption & Chr(34) & _
" AND vList.prUse =" & Chr(34) & "interply" & Chr(34)

sqlCap = "SELECT * FROM vList WHERE " _
& "vList.mftrName =" & Chr(34) & rsholder2.Caption & Chr(34) & _
" AND vList.prType =" & Chr(34) & rsholder1.Caption & Chr(34) & _
" AND vList.prUse =" & Chr(34) & "surfacing" & Chr(34)

Set rs1 = db.OpenRecordset(sqlBase)
Set rs2 = db.OpenRecordset(sqlPly)
Set rs3 = db.OpenRecordset(sqlCap)

With rs1
If Not .BOF Then .MoveFirst
While Not .EOF
With rshot05
.AddItem rs1("prName")
End With
.MoveNext
Wend
' End If
End With

With rs2
If Not .BOF Then .MoveFirst
While Not .EOF
With rshot35
.AddItem rs2("prName")
End With
.MoveNext
Wend
' End If
End With

With rs3
If Not .BOF Then .MoveFirst
While Not .EOF
With rshot38
.AddItem rs3("prName")
End With
.MoveNext
Wend
' End If
End With

Set rs1 = Nothing
Set rs2 = Nothing
Set rs3 = Nothing
db.Close
Set db = Nothing
End Sub

 

[Guest]

That fixed the object req'd error, but now there's a different error:

Runtime Error 3601
Too few parameters. Expected: 1

....and it points to this: Set rs1 = db.OpenRecordset(sqlBase)

Any ideas?

 

[Guest]

Try removing the Set from rs1 rs2 rs3

That fixed the object req'd error, but now there's a different error:

Runtime Error 3601
Too few parameters. Expected: 1

...and it points to this: Set rs1 = db.OpenRecordset(sqlBase)

Any ideas?

 

[Guest]

Ok - that resulted in "Invalid use of property" so I looked into the help
file for OpenRecordSet and added in the following to each "set rs*"

db.OpenRecordset(sqlBase, dbOpenTable, dbReadOnly, dbReadOnly)

which, of course, resulted in ANOTHER error stating that the "SELECT * FROM
...." string is an object that can't be found. Oh well. Try, try again until
insane or program works.

 

[Guest]

If you could email me a copy of the database I might be of more help

 

[Guest]

Here's code i use to get data from mdb
Private Sub testread()
Dim cnn As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim strSQL1 As String, strConn
Dim i As Integer
Dim mydate1 As String
Dim mydate2 As String
mydate1 = Sheets(1).Range("F1")
mydate2 = Sheets(1).Range("F2")
i = 1

'Use for Access (jet)
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=C:\Ilsa\Data\" _
& "Ilsa.mdb;Persist Security Info=False"

'Use for jet
strSQL1 = "SELECT FIELDNAME, FIELDNAME2 " _
& "FROM TABLENAME " _
& "WHERE (((TIME_STAMP) Between #" & mydate1 & "# " _
& "And #" & mydate2 & "#+1)) " _
& "ORDER BY FIELDNAME; "

Set cnn = New ADODB.Connection
Set rs1 = New ADODB.Recordset
cnn.Open strConn
rs1.Open strSQL1, cnn, adOpenForwardOnly, adLockReadOnly
Do While rs1.EOF = False
Sheets("Sheet1").Range("A" & i) = rs1!FIELDNAME1
'Sheets("Sheet1").Range("B" & i) = rs1!FIELDNAME2
'Sheets("Sheet1").Range("C" & i) = rs1!FIELDNAME3
'Sheets("Sheet1").Range("D" & i) = rs1!FIELDNAME4
'Sheets("Sheet1").Range("E" & i) = rs1!FIELDNAME5
'Sheets("Sheet1").Range("F" & i) = rs1!FIELDNAME6
'Sheets("Sheet1").Range("G" & i) = rs1!FIELDNAME7
'Sheets("Sheet1").Range("H" & i) = rs1!FIELDNAME8
i = i + 1
rs1.MoveNext
Loop
rs1.Close
cnn.Close
End Sub

 

[Guest]

I appreciate the offer - got it solved a few minutes ago.

I removed the ", dbOpenTable, dbREadOnly, dbReadOnly" portion of the set rs#
string,
added "& ";" to the end of the set sqlBase, sqlPly and sqlCap "SELECT * FROM
...." string and moved the whole chunk of code into a Userform_Activate
routine and now it works. I still don't understand why, but it works.

Thanks for your help Mike.