OpenRecordset in Access
Posted on 2011-09-30
I have a Query with fields from several tables and multiple filters and have a recordset that I would then like to move into an Audit table. This is when an Event is cancelled that has several bookings or when a single booking is cancelled. The Audit table is then used to control the issue of refunds.
Here's the code (see comments within for the 2 problems)
'Set DB = CurrentDb("JDWEventsII_be.mdb") 'PROBLEM - will not work so had to use legacy method below
'Error "Item not found in this collection."
Set DB = DBEngine(0)(0)
Set RS1 = DB.OpenRecordset("qCanxEventAudit") 'PROBLEM - the recordset I'm trying to open is query
'based and is the Record Source for the form
'Error "Too few Parameters. Expected 1."
Set RS2 = DB.OpenRecordset("tJDWAudit")
Do Until RS1.EOF
RS2!jdwa_ContactID = RS1!jdwb_Member
RS2!jdwa_EventID = RS1!jdwe_EventID
RS2!jdwa_BookingRef = RS1!jdwb_BookingID
RS2!jdwa_PaymentRef = RS1!jdwp_Payment
RS2!jdwa_RcptNo = RS1!jdwp_RcptNo
RS2!jdwa_StartDate = RS1!jdwb_Start
RS2!jdwa_Amount = RS1!jdwp_Amount
RS2!jdwa_DatePaid = RS1!jdwp_Date
RS2!jdwa_Refund = RS1!jdwp_Refund
RS2!jdwa_Notes = RS1!jdwb_Notes
RS2!jdwa_DateCANX = Date
Set RS1 = Nothing
Set RS2 = Nothing
Set DB = Nothing
Where am I going wrong please?