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

"IT_roofer" wrote:

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. :)

"Mike" wrote:

Try removing the Set from rs1 rs2 rs3

"IT_roofer" wrote:

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?

"Mike" wrote:

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

"IT_roofer" wrote:

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