FindFirst error - Operation not supported for this type of object 1

[BenjaminLim]

I encounter the above run-time error : 3251 with the following codes.

Specifically at the line where : rst1.FindFirst "rst1!Date1 = rstd1!Date1"

Please advice on what I should do to correct it. Thanks.

Code List
========================

Sub Forecast()
Dim SeqA As String, SeqB As String, NumA As String, NumB As String
Dim DateA As Date, DateB As Date, OrigA As String, OrigB As String
Dim PriceA As Integer, PriceB As Integer
Dim db As Database, rstd1 As Recordset, rstd2 As Recordset, rstd3 As Recordset
Dim rst As Recordset, rst1 As Recordset, rst2 As Recordset, rst3 As Recordset
Dim varBookmark As Variant
Dim varBookmark1 As Variant
Dim varBookmark2 As Variant
Dim DDate1 As Date
Dim Str1 As String

Set db = CurrentDb

Set rst = db.OpenRecordset("Forecast&quot
Set rstd1 = db.OpenRecordset("First Date Set&quot
Set rstd2 = db.OpenRecordset("Second Date Set&quot
Set rstd3 = db.OpenRecordset("Third Date Set&quot
Set rst1 = db.OpenRecordset("4d&quot
Set rst2 = db.OpenRecordset("4d&quot
Set rst3 = db.OpenRecordset("4d&quot

With rstd1
.MoveFirst
Do Until .EOF
With rst1
DDate1 = rstd1!Date1
varBookmark = .Bookmark
.MoveLast
rst1.FindFirst "rst1!Date1 = rstd1!Date1"
Do While True
SeqA = rst1!Seq
NumA = rst1!Num1
DateA = rst1!Date1
OrigA = rst1!OrigNo
PriceA = rst1!Price
If .NoMatch Then
.Bookmark = varBookmark
Exit Do
End If
With rstd2
.MoveFirst
Do Until .EOF
With rst2
.MoveLast
varBookmark1 = .Bookmark
.FindFirst rst2!Date1 = rstd2!Date1
Do While True
SeqB = rst2!Seq
NumB = rst2!Num1
DateB = rst2!Date1
OrigB = rst2!OrigNo
PriceB = rst2!Price
If .NoMatch Then
.Bookmark = varBookmark1
Exit Do
End If
With rstd3
.MoveFirst
Do Until .EOF
With rst3
.MoveLast
varBookmark2 = .Bookmark
.FindFirst rst3!Date1 = rstd3!Date1
Do While True
rst.AddNew
rst!SeqA = SeqA
rst!SeqB = SeqB
rst!SeqC = rst3!Seq
rst!NumA = NumA
rst!NumB = NumB
rst!NumC = rst3!Num1
rst!DateA = DateA
rst!DateB = DateB
rst!DateC = rst3!Date1
rst!OrigA = OrigA
rst!OrigB = OrigB
rst!OrigC = rst3!OrigNo
rst!PriceA = PriceA
rst!PriceB = PriceB
rst!PriceC = rst3!Price
rst.Update
If .NoMatch Then
.Bookmark = varBookmark2
Exit Do
End If
.FindNext rst3!Date1 = rstd3!Date1
Loop
End With
.MoveNext
Loop
End With
.FindNext rst2!Date1 = rstd2!Date1
Loop
End With
.MoveNext
Loop
End With
.FindNext rst1!Date1 = rstd1!Date1
Loop
End With
.MoveNext
Loop
End With

rst.Close
rst1.Close
rst2.Close
rst3.Close
rstd1.Close
rstd2.Close
rstd3.Close

End Sub
'=============================

Regards
Benjamin

 

[Durkin]

The criteria parameter in the findfirst function is supposed to be like a SQL where clause so it has to be a string. This means that within the string you cannot refer to variables in your routine. You need to say:

"Date1 = #" & rstd1!Date1 & "#"

This is assuming that the field in the 4d table is called Date1. The # signs are how you denote dates in access. You need to do this for all your other findfirst's as well. Durkin