[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 371
  • Last Modified:

Filtering a Recordset

I am trying to filter a record set using the following code:

Dim RSA As DAO.Recordset
Dim RSB As DAO.Recordset
                   
Set RSA = CurrentDb.OpenRecordset("tJDWBookings")
RSA.Filter = "jdwb_Event = me.jdwe_EventID"
Set RSB = RSA.OpenRecordset  'error on this line

jdwb_Event is an unique reference for an event which I want to filter to find bookings that match the currently displayed event.  tJDWBookings is not the table that is bound to the form.

Once I have the records isolated I am going to run something like:
       
RSA.MoveFirst                    
Do Until RSA.EOF
                       
     RSA!jdwb_Status = "CANX"
                       
     If Not RSA.EOF Then RSA.MoveNext
                   
Loop

This will set all bookings, attached to an cancelled event, as "CANX" (cancelled).

I am getting an error message "Too few parameters. Expected 2." on the line indicated above.

Any ideas out there?  I'm out for an hour or two now so don't worry if I don't reply for a while.
0
MikeDTE
Asked:
MikeDTE
3 Solutions
 
mbizupCommented:
Hi Mike,

<<
Set RSA = CurrentDb.OpenRecordset("tJDWBookings")
RSA.Filter = "jdwb_Event = me.jdwe_EventID"
Set RSB = RSA.OpenRecordset  'error on this line
>>

Change the above lines to:

Set RSA = CurrentDb.OpenRecordset(" SELECT * FROM tJDWBookings WHERE jdwb_Event = " & me.jdwe_EventID )
Set RSB = RSA.OpenRecordset


0
 
mbizupCommented:
Sorry - it should just be this:

Set RSA = CurrentDb.OpenRecordset(" SELECT * FROM tJDWBookings WHERE jdwb_Event = " & me.jdwe_EventID )


(Remove the "SET RSB" line altogether)
0
 
peter57rCommented:
The problem with your original code...
RSA.Filter = "jdwb_Event = " & me.jdwe_EventID
0
 
Dale FyeCommented:
You are also going to have to modify the rest of the code.  Change:

Do Until RSA.EOF
                       
     RSA!jdwb_Status = "CANX"
                       
     If Not RSA.EOF Then RSA.MoveNext
                   
Loop

to:

While Not RSA.EOF
    RSA.Edit
    RSA!jdwb_Status = "CANX"
    RSA.Update
    RSA.MoveNext
Wend

But you could also do all of this with a simple update query, unless you are doing something else inside the loop.  That would look something like:

strSQL = "UPDATE tJDWBookings SET jdwb_Status = 'CANX' WHERE jdwb_Event = " & me.jdwe_EventID
Currentdb.Execute strSQL, dbfailonError
0
 
MikeDTEAuthor Commented:
Mbizup solution worked but Fyed solution used far less code.  I suspect that if I went back to my original code and defined the filter more carefully then peter57r solution would have helped.  In the end I went with the SQL Update query.  Thanks all for teh help
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now