Recorsetclone problem

[Pieter Wijnen]

You are not closing the recordset per chance?
Recordsetclones should not be closed

Pieter

 

[Ian King]

I have a subform that displays records filtered by a combo box on the main
form.

A button on the main form uses the Recordsetclone of the sub form to copy
data to another table.

This works fine on the first selection from the combo box, but then fails
when making subsequent choices. It seems as if the recordsetclone variable
can't be re-initialised to point at the new data.

What's wrong? Please help.

Ian King

 

[Ian King]

Pieter, thanks for the suggestion; but still no go.

This is the code on the button if anyone can help.

Private Sub Command25_Click()
Dim Rst As Recordset

Set Rst = Me.ClientAuditItemSelectSub.Form.RecordsetClone
If Rst.EOF Then
Beep
MsgBox "There is nothing to select."
Else
Rst.MoveFirst
DoCmd.SetWarnings False
Do While Not Rst.EOF
DoCmd.RunSQL "INSERT INTO ClientAuditItem ( AuditItemID,
AuditID, AuditType," _
& " Sectn, ParaNo, SubParaNo, AuditItem )" _
& " SELECT DefaultAudit.AuditItemID, " & AuditID & " AS
Expr1," _
& " DefaultAudit.AuditType, DefaultAudit.Sectn," _
& " DefaultAudit.ParaNo, DefaultAudit.SubParaNo,
DefaultAudit.AuditItem" _
& " FROM DefaultAudit" _
& " WHERE AuditItemID = " & Rst!AuditItemID
Rst.MoveNext
Loop
'Rst.Close: Set Rst = Nothing
DoCmd.SetWarnings True
Forms!ClientAuditSection!ClientAuditSectionSub.Requery
Forms!ClientAuditSection!Sectn.Requery
End If

End Sub


"Pieter Wijnen"

 

[Pieter Wijnen]

The reason is that you're at EOF after the first call.
Try:

Rst.BOF And Rst.EOF

You can also try Recordset.Clone instead (dependant on Access version).

I'd recommend to do a CurrentDb.Execute instead of RunSQL while you're at it
as you then can remove the Docmd.setwarnings statements


Pieter