code problem - dbs.openrecordset(strSQL).. 1

[Trevoke]

Public Function boxes(curday As Variant, curbox As Integer)
Dim dbs As Database
Dim rst As Recordset
Dim temp2 As Date
Dim strSQL As String

temp2 = (DateAdd("d", curbox, curday))
strSQL = "SELECT * INTO tblTemp1 FROM tblSummary1 WHERE ShipDate = #" & temp2 & "# OR BatchMakeDate = #" & temp2 & "# OR NYStdDate = #" & temp2 & "# OR NYMassDate = #" & temp2 & "# OR ComponentsDueBy = #" & temp2 & "#;"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
rst.MoveLast
If rst.RecordCount <> 0 Then
[&quot;D&quot; & curbox].BackColor = &quot;FF0000&quot;
End If
End Function

Anybody know why I get an &quot;invalid operation&quot; message at the line
Set rst = dbs.OpenRecordset(strSQL) ? Catapultam habeo. Nisi pecuniam omnem mihi dabis, ad caput tuum saxum immane mittam.

 

[rookery]

Have you set your references to include the DAO Library?

 

[Trevoke]

Erm.. This is Access 97, and I've never had a problem doing this before.. That, and considering I don't know where I can set the references.. Only have a month and a half of experience with Access.. Catapultam habeo. Nisi pecuniam omnem mihi dabis, ad caput tuum saxum immane mittam.

 

[rookery]

On second thoughts I dont think you'd have been able to declare dbs as a Database variable if you didnt have the DAO reference selected. I use 2000 and as such had to set it. For future reference the References are in the Tools menu within the Code window.
Sorry cant be of any more help.

 

[Trevoke]

Well.. Thanks anyway. Anybody else? Catapultam habeo. Nisi pecuniam omnem mihi dabis, ad caput tuum saxum immane mittam.

 

[krsherm]

Your strSQL looks questionable. My recommendation would be to put a stop statement after you set the value of strSQL. That will cause the code to stop before it tries to open the rst. Once you are stopped, type ?strSQL into the immediate window and it will print out (to the immediate window) the value of strSQL. Copy the string from the immediate window and paste it into a new blank query (in the SQL view window). Then try switching to design view... if there is a problem with the SQL statement, it will give you an error and will not allow you to switch to design view.

I know that there are several different ways to accomplish the same goal in Access, but I always use (for example):

strSQL = &quot;INSERT INTO tblTemp1(Value1, Value2, Value3) SELECT xValue1, xValue2, xValue3 FROM tblSummary1 WHERE ShipDate=#&quot; & temp2 & &quot;# OR BatchMakeDate = #&quot; & temp2 & &quot;#&quot;

Whenever I get really stuck, I first build the query in the query builder, then I copy the string from SQL view into my code and edit it so that it works.

Let me know how you progress. If this doesn't work, I'll dig into it further with you.

Rock ON!

Kevin

 

[Trevoke]

ha. I got it. It was something really, really stupid.

It was the &quot;INTO tblTemp1&quot; of the strSQL.. not needed for a regular recordset, but needed for a report.

I took it out. Now fighting with more VBA code but that's another problem

Thanks. Catapultam habeo. Nisi pecuniam omnem mihi dabis, ad caput tuum saxum immane mittam.