Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object required error...
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object required error...
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object required error...
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object required error...
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object required error...
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object required error...
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object required error...
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 "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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object required error...
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. "Mike" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Object Required Error | Excel Programming | |||
Object Required Error 424 | Excel Programming | |||
Error: Object required | Excel Programming | |||
Object required error | Excel Programming | |||
Syntax Error Runtime Error '424' Object Required | Excel Programming |