Solved

OpenRecordset in Access

Posted on 2011-09-30
16
282 Views
Last Modified: 2012-05-12
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?
0
Comment
Question by:MikeDTE
  • 8
  • 4
  • 2
  • +2
16 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36894330
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
 

Author Comment

by:MikeDTE
ID: 36894358
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36894371
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
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 250 total points
ID: 36894396
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
 

Author Comment

by:MikeDTE
ID: 36894399
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
 

Author Comment

by:MikeDTE
ID: 36894405
Posts crossed - now got your second - I'm on the case.  Many thanks
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 36894408
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
 

Author Comment

by:MikeDTE
ID: 36894443
'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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 75
ID: 36894618
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
 

Author Comment

by:MikeDTE
ID: 36894679
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
 

Author Comment

by:MikeDTE
ID: 36894687
BTW didn't need the EVAL() wrap - SQL code worked like a dream.
0
 

Author Closing Comment

by:MikeDTE
ID: 36894694
Excellent help - many thanks.  VB is not like VBA and I'm having to learn new ways of doing some simple things.
0
 
LVL 75
ID: 36894715
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
 
LVL 75
ID: 36894724
BTW didn't need the EVAL() wrap -
EVAL() eliminates the need for that extra code :-)

mx
0
 

Author Comment

by:MikeDTE
ID: 36895793

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
 
LVL 75
ID: 36895945
"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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now