Run-time error 3021. No current record. I can't see why. Help!

[malibu65k]

Am I missing something here. I have used this code many times before. I tested the fields and there is data in them but when I try to add it to recPrint - "tblPrintTemp" It says no current record. Am I not seeing the problem. I can't find it.


Function CreateAndLoadPrintTable()
Dim strSQL As String
Dim vTypes As String
Dim vTemp As String
Dim vNameItm As Variant
Dim rec As dao.Recordset
Dim recPrint As dao.Recordset
Dim vMSF As String

Set db = CurrentDb()
Set recPrint = db.OpenRecordset("tblPrintTemp")

If TableExists("tblPrintTemp") Then
DoCmd.RunSQL "DELETE * FROM tblPrintTemp"

For Each vNameItm In Me.lstNames.ItemsSelected

strSQL = "SELECT * FROM qryMembershipCard WHERE MemberId = " & Me.lstNames.Column(0, vNameItm)
Set rec = db.OpenRecordset(strSQL)

MsgBox strSQL

If rec("MSFBRCDate") > rec("MSFERCDate") Then
vMSF = CStr(Format(rec("MSFBRCDate"), "dd mmm yyyy"))
Else
vMSF = CStr(Format(rec("MSFERCDate"), "dd mmm yyyy"))
End If

Do Until rec.EOF

If rec("Type") <> "" Then
If vTemp <> rec("Type") Then
vTypes = vTypes & rec("Type") & "/"
vTemp = rec("Type")
End If
End If

rec.MoveNext
Loop

If vTypes <> "" Then
vTypes = Left(vTypes, Len(vTypes) - 1)
End If

With recPrint
recPrint.AddNew
recPrint!Name = rec("Name")
recPrint!MemberId = rec("MemberId")
recPrint!MembershipDate = rec("MembershipDate")
recPrint!MSFDate = vMSF
recPrint!Types = vTypes
recPrint.Update
End With

Next vNameItm

End If

End Function

 

[PHV]

...
Do Until rec.EOF
...
Loop
' now rec has no current record ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[malibu65k]

I added this after I open the recordset rec

vName = rec("Name")
vMemberId = rec("MemberId")
vMembershipDate = CStr(Nz(rec("MembershipDate"), ""))


and it works great when I add the vName,vMemberId and the vMembershipDate to the table.

Is that wierd?

 

[xicana]

i was looking for solutions as I'm getting the same issue when I'm using vba to delete the current record...I searched on microsoft's website and found this:

pertains to access 2002.

http://support.microsoft.com/kb/885235

Sandy