Opening a recordset based upon a variable

[1ofakind]

Hi All,

Simple example is I have say 3 recordsets open (they are opened once as they are refered to many times) - they are open early in the form (in this example 3 price lists that are applicable for a customer) (if the syntax of the select is slightly wrong I'm just showing to 'prove' the concept).

Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim rs3 As Recordset

Set db = CurrentDb()
Set rs1 = db.OpenRecordset("SELECT * FROM Prices WHERE (((Prices.ID)=1));")
Set rs2 = db.OpenRecordset("SELECT * FROM Prices WHERE (((Prices.ID)=2));")
Set rs3 = db.OpenRecordset("SELECT * FROM Prices WHERE (((Prices.ID)=3));")

What I want to do is have a central function that I can pass a list I wish to process/do something with aka. The 3 recordsets are the same except for the where criteria.

private sub GETPRICEFROMLIST(pricelist as long)

dim rs as recordset

set rs = Recordset("rs" & pricelist)
.....

is there someway I can actually do this ? I put the above to show what I'm trying to do but of course that doesn't work There won't be any updates to these recordsets only reading of data.

Hope I've explained myself.

Regards and many thanks

 

[Rx_]

Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim rs3 As Recordset
Dim X as integer
Dim Y as integer
Dim Z as integer
' You could have a Text box to enter the number too
' e.g X = Me.MyTextBox

Set db = CurrentDb()
Set rs1 = db.OpenRecordset("SELECT * FROM Prices WHERE (((Prices.ID)=" & X & "));")
Set rs2 = db.OpenRecordset("SELECT * FROM Prices WHERE (((Prices.ID)=" & Y & "));")
Set rs3 = db.OpenRecordset("SELECT * FROM Prices WHERE (((Prices.ID)=" & Z & "));")

Let me suggest that on this website, go to the Sample Databases.
There are many great examples to download. Use that to get started or to copy something already working and modify it to your need.

 

[1ofakind]

Sorry it might be so apparent what I'm asking at the moment it works like this

open the 3 recordsets

read a record from rs1

lots of code to deal with the pricing, calculations and various other stuff

read a record from rs2

duplication of the above code to deal with pricing (because its exactly the same)

read a record from rs3

duplication of the above code to deal with pricing (....)

what I want to do is basically be able to say

call getpricefromlist(1)
some other criteria exit
call getpricefromlist(2)
some other criteria exit
call getpricefromlist(3)

I'm trying to stop the replication of the code which is exactly the same but of course refers to rs1.price, rs1.cost, rs1.blah etc
then repeated for rs2.price , rs2.cost, rs2.blah etc

just trying to make it more elegant

I know the above is pseudo code but I'm trying to find out if I can open a recordset based upon a variable/dynamic name (aka rs & variable) and if so what is the command to do it.

Many thanks

 

[Mihail]

Set rs3 = db.OpenRecordset("SELECT * FROM Prices WHERE (((Prices.ID)=3));")

What I want to do is have a central function that I can pass a list I wish to process/do something with aka. The 3 recordsets are the same except for the where criteria.

Private Sub GETPRICEFROMLIST(pricelist As Long)
Dim rs As Recordset
  Set rs = db.OpenRecordset("SELECT * FROM Prices WHERE (((Prices.ID)=" & pricelist  & "));")

 

[1ofakind]

Hi,

Yes I know I could do that - thanks. The code opens those 3 recordsets and leaves them open because they are accessed many many many times to retrieve data based upon various other criteria and (this is what I've been told) it's easier to leave them open than keep opening and shutting the recordsets to retrieve records and process.

Within the application a customer has 3 price lists which are checked sequentially to retrieve certain data which is then used to calculate a price (amongst other things). The actually calculations etc are quite complex but are the same for each of the 3 record sets so we wanted to consolidate the code and use 1 routine to calculate the price and rather than passing xyz fields or having the routine open a new 'specific' recordset we wanted to have a generic RS which was based upon the relevant 'price list'.


Regards

 

[Mihail]

Is not a shame to say that I don't fully understand your requirement, especially because my English.
But, from what I understand, I think that some DLookup(s) into a function will solve the problem without all this stuff.

 

[spikepl]

The title of your post is confusing, in that you open your multiple recordsets, and you wnat to do that, irrespective of any variables.

You can pass a recordset like any other variable.

Private Sub HandleMultiple

   Dim rs1 as DAO.Recordset
   Dim rs2 as DAO.Recordset


   HandleRs rs1 'do something with rs1
   HandleRs rs2 'do something with rs2


End Sub

Private Sub HandleRs( myRs as DAO.Recordset)

    'here you do whatever you want to do with the recordset passed through the    header

    msgbox myRs.RecordCount

End Sub