OpenRecordset in Access

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")
 
RS1.MoveFirst
               
Do Until RS1.EOF
                   
     RS2.AddNew
                   
     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
                   
     RS2.Update
                             
Loop
 
RS1.Close
RS2.Close
               
Set RS1 = Nothing
Set RS2 = Nothing
Set DB = Nothing

Where am I going wrong please?
MikeDTEAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
You are using a parameter: your query refers to a form.

1) Re-write the query as:

PARAMETERS [jdwb_BookingID] Long;
SELECT tJDWEvents.jdwe_EventID, tJDWBookings.jdwb_Member, tJDWBookings.jdwb_BookingID, tJDWPayments.jdwp_Payment, tJDWBookings.jdwb_Start, tJDWPayments.jdwp_Amount, tJDWPayments.jdwp_Date, tJDWPayments.jdwp_Refund, tJDWPayments.jdwp_RcptNo, tJDWBookings.jdwb_Status
FROM (tJDWBookings INNER JOIN tJDWEvents ON tJDWBookings.jdwb_Event = tJDWEvents.jdwe_EventID) INNER JOIN tJDWPayments ON (tJDWPayments.jdwp_Event = tJDWEvents.jdwe_EventID) AND (tJDWBookings.jdwb_BookingID = tJDWPayments.jdwp_BookingRef)
WHERE (((tJDWBookings.jdwb_BookingID)=[jdwb_BookingID]) AND ((tJDWBookings.jdwb_Status)='OK'));

2) Now, in your code, do something like this...


Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset

Set qdf = CurrentDb.QueryDefs("name_of_query")
qdf.Parameters("jdwb_BookingID").Value = [forms]![fJDWBookings]![jdwb_BookingID]
Set rs = qdf.OpenRecordset

Open in new window

0
 
Patrick MatthewsCommented:
MikeDTE,

Two things:

1) When you Dim the recordset variable, always explicitly set what type of recordset it is.  In this case:

Dim RS1 As DAO.Recordset
Dim RS2 As DAO.Recordset

Open in new window


If you were using ADO:

Dim RS1 As ADODB.Recordset
Dim RS2 As ADODB.Recordset

Open in new window


2) Does that query take parameters?
0
 
MikeDTEAuthor Commented:
UH-OH

These lines are above my code but omitted from the code sent to you

Dim DB As DAO.Database
Dim RS1 As DAO.Recordset
Dim RS2 As DAO.Recordset

No query has embedded filters - here's the SQL

SELECT tJDWEvents.jdwe_EventID, tJDWBookings.jdwb_Member, tJDWBookings.jdwb_BookingID, tJDWPayments.jdwp_Payment, tJDWBookings.jdwb_Start, tJDWPayments.jdwp_Amount, tJDWPayments.jdwp_Date, tJDWPayments.jdwp_Refund, tJDWPayments.jdwp_RcptNo, tJDWBookings.jdwb_Status
FROM (tJDWBookings INNER JOIN tJDWEvents ON tJDWBookings.jdwb_Event = tJDWEvents.jdwe_EventID) INNER JOIN tJDWPayments ON (tJDWPayments.jdwp_Event = tJDWEvents.jdwe_EventID) AND (tJDWBookings.jdwb_BookingID = tJDWPayments.jdwp_BookingRef)
WHERE (((tJDWBookings.jdwb_BookingID)=[forms]![fJDWBookings]![jdwb_BookingID]) AND ((tJDWBookings.jdwb_Status)='OK'));
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Jeffrey CoachmanMIS LiasonCommented:
Is that the "Entire code?
Where are you declaring "DB"?

If "JDWEventsII_be.mdb" is the current database, then simply use this:

Set RS1 = currentDB.OpenRecordset("qCanxEventAudit")
Set RS2 = currentDB.OpenRecordset("tJDWAudit")

The "Too few Parameters. Expected 1." error is typically indicative of a query that needs a parameter, or a form is being used to provide the parameter, and the form is not open (or the form is open but the parameter has not been entered)
0
 
MikeDTEAuthor Commented:
Code is as originally written

Dim DB As DAO.Database 'dimension DB
Dim RS1 As DAO.Recordset
Dim RS2 As DAO.Recordset

Set DB = CurrentDb("JDWEventsII.mdb") 'declare DB                
Set RS1 = DB.OpenRecordset("qCanxEventAudit")
Set RS2 = DB.OpenRecordset("tJDWAudit")  

The declare statement was remmed out because it wouldn't work - so I replaced it with

Set DB = DBEngine(0)(0)  

So I will now change to

Dim RS1 As DAO.Recordset
Dim RS2 As DAO.Recordset
           
Set RS1 = currentDB.OpenRecordset("qCanxEventAudit")
Set RS2 = currentDB.OpenRecordset("tJDWAudit")  

Is that right?



0
 
MikeDTEAuthor Commented:
Posts crossed - now got your second - I'm on the case.  Many thanks
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
try:

'Dim DB As DAO.Database
Dim RS1 'As DAO.Recordset
Dim RS2 'As DAO.Recordset

Set RS1 = CurrentDB.OpenRecordset("qCanxEventAudit")
                         
Set RS2 = CurrentDB.OpenRecordset("tJDWAudit")
0
 
MikeDTEAuthor Commented:
'Dim DB As DAO.Database
Dim RS1 'As DAO.Recordset
Dim RS2 'As DAO.Recordset

'Set DB = CurrentDB("JDWEventsII_be.mdb")
Set RS1 = CurrentDB.OpenRecordset("qCanxBookingAudit")
Set RS2 = CurrentDB.OpenRecordset("tJDWAudit")

Above results in Parameter error as previous post

I will replace the SQL statement as you suggest
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
MikeDTE:
Try wrapping your Forms reference in the Eval() function. This should resolve your Too Few Parameters issue ...

SELECT tJDWEvents.jdwe_EventID, tJDWBookings.jdwb_Member, tJDWBookings.jdwb_BookingID, tJDWPayments.jdwp_Payment, tJDWBookings.jdwb_Start, tJDWPayments.jdwp_Amount, tJDWPayments.jdwp_Date, tJDWPayments.jdwp_Refund, tJDWPayments.jdwp_RcptNo, tJDWBookings.jdwb_Status
FROM (tJDWBookings INNER JOIN tJDWEvents ON tJDWBookings.jdwb_Event = tJDWEvents.jdwe_EventID) INNER JOIN tJDWPayments ON (tJDWPayments.jdwp_Event = tJDWEvents.jdwe_EventID) AND (tJDWBookings.jdwb_BookingID = tJDWPayments.jdwp_BookingRef)
WHERE (((tJDWBookings.jdwb_BookingID)=EVAL([forms]![fJDWBookings]![jdwb_BookingID])) AND ((tJDWBookings.jdwb_Status)='OK'));

mx

0
 
MikeDTEAuthor Commented:
Re-wrote SQL Statement - also added tJDWBookings.jdwb_Notes to field list (as it was missing - oops!)

Changed Code to:

Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset

Set qdf = CurrentDb.QueryDefs("qCanxBookingAudit")
qdf.Parameters("jdwb_BookingID").Value = [Forms]![fJDWBookings]![jdwb_BookingID]

Set RS1 = qdf.OpenRecordset
Set RS2 = CurrentDb.OpenRecordset("tJDWAudit")

RS1.MoveFirst

Do Until RS1.EOF
   
    RS2.AddNew
   
    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
   
    RS2.Update

Loop

RS1.Close
RS2.Close

Set RS1 = Nothing
Set RS2 = Nothing

The Recordsets open and the query shows 1 record matching BUT ....

"Do Until RS1.EOF" does not work and the loop continues and it had reached 350,000 records by the time I stopped it.  It was the right data but repeated 350,000 times in the Audit table.

Unless you can see a better way: I think I should check how many records are in the query result and then use X=X+1 manual counter to exit the loop. Also I should check for no matches.
 
Thanks for the help - I'll sign-off the points.  I'm "off to feather" now as we say over here - well some of us do (particularly if your from 'up north').  Goodnight.

Mike
 
0
 
MikeDTEAuthor Commented:
BTW didn't need the EVAL() wrap - SQL code worked like a dream.
0
 
MikeDTEAuthor Commented:
Excellent help - many thanks.  VB is not like VBA and I'm having to learn new ways of doing some simple things.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Seems you are missing a MoveNext:


Do Until RS1.EOF
   
    RS2.AddNew
   
    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
   
    RS2.Update
    If Not RS2.EOF Then RS2.MoveNext
Loop
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
BTW didn't need the EVAL() wrap -
EVAL() eliminates the need for that extra code :-)

mx
0
 
MikeDTEAuthor Commented:

Postscript:

Was about to add "If Not RS2.EOF Then RS2.MoveNext" but felt it shoudl be "If Not RS1.EOF Then RS1.MoveNext" - so did so and it is all working now.

Do not understand you last post about EVAL()

Thanks for the guide though.

0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"but felt it shoudl be "If Not RS1.EOF Then RS1.MoveNext""
You're right ... I had a typo ... seeing too many RS2's

mx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.