Getting Values out of a recordset

[theracer]

Hi,

I need to get values out of a recordset in order to filter a second sqlstring

I constructed a filter query and get the records out of it in the first SQLstring in order to use the values to filter the second SQLstring. Unfortunately, when I set CountryCurveSelect = recordset.Fields(C).Values, there are no items to be found...I also tried
with recordset.getrows but that also gives problems probably due to the
multidimensionality of the variant.

Here is what I set up

' Loop through the countries until predefined Number of Countries (NC) is
reached
For C = 1 To NC
   sqlstr = "Select [_Country Filter]![ID COUNTRY] from [_Country Filter]"
   Set rsread = db.OpenRecordset(sqlstr)
   rsread.MoveFirst
   CountryCurveSelect = rsread.Fields(C).Values
   rsread.Close

' Original curvesmoothing procedure   
   sqlstr = "Select * from [myTable] where [ID Country] = " &
CountryCurveSelect & ";"
   Set rsread = db.OpenRecordset(sqlstr)
   If rsread.BOF Then Resume Next   ' No records retrieved
   rsread.MoveLast
   ValueCount = rsread.RecordCount

etc...

Next C

What am I doing wrong?

 

[SydneyGeek]

Do you want to use the top N country ID's to filter another recordset? If so, you could try...

Sub Filter2()
    Dim sSQL As String
    Dim rst As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim prm As DAO.Parameter
    Dim NC As Long
    
    NC = InputBox("How many countries?")
    
    sSQL = "SELECT * FROM [My Table] WHERE [CountryID] In(SELECT TOP " & NC & " [CountryID] FROM [My Table])"
    Set qdf = DBEngine(0)(0).CreateQueryDef("", sSQL)
    For Each prm In qdf.Parameters
        prm.Value = Eval(prm.Name)
    Next prm
    Set rst = qdf.OpenRecordset
    
    With rst
        'do stuff in here
    End With
End Sub

Denis

 

[theracer]

Hi Denis,

thanks for your reply.

What I am trying to do is to filter out a country and then do a fairly complex procedure that modifies the data in the filtered recordset. I would like to run the procedure on filtered country one, then move on to filtered country two etc... until I reach the NC parameter.

Since this is part of a fairly lenghty simulation that runs for hours I do not want to input any data parameters on the fly but have it all run automatically.

Would you know how to read the value of the filter in a looping procedure?

 

[SydneyGeek]

You could put the second loop inside the first. Something like...

' Loop through the countries until predefined Number of Countries (NC) is reached
'assuming that NC is defined earlier...
   sqlstr = "Select TOP " & NC & " [_Country Filter]![ID COUNTRY] from [_Country Filter] "
   Set rst = db.OpenRecordset(sqlstr)
   rst.MoveFirst
   Do Until rst.EOF
        ' Original curvesmoothing procedure
        sqlstr2 = "Select * from [myTable] where [ID Country] = " & rst![ID COUNTRY] & ";"
        Set qdf = db.createrecordset("", sqlstr2)
        For Each prm In qdf.Parameters
            prm.Value = Eval(prm.Name)
        Next prm
        Set rsread = qdf.OpenRecordset
        With rsread
            .MoveFirst
            Do Until .EOF
                'do your processing of the second recordset in here
                
                .MoveNext
            Loop
        End With
    rst.MoveNext
    Loop

Denis

 

[Norie]

What exactly are you trying to do here?

rsread.MoveFirst
   CountryCurveSelect = rsread.Fields(C).Values
   rsread.Close

Also why are you opening the recordset rsread on every iteration of the loop?

What value(s) are you actually trying to put in CountryCurveSelect and are you sure whatever it is will work as criteria for the 2nd query?

Where is NC defined?

 

[xenou]

Denis' idea looks worthwhile. In your original post:

CountryCurveSelect = rsread.Fields([COLOR="Red"]C[/COLOR]).Values

There is only one field in the select. The 1 to NC is for moving through the records, not the fields - Denis' sql addresses this, I think. (@Norie: NC is defined as a number entered in an input box).

This line looks funny to me:

If rsread.BOF Then Resume Next   ' No records retrieved

I've never seen a resume next used like this - does it actually work?

 

[Norie]

xenou

I think I was actually more interested in what the purpose of the loop was and hoped to maybe get some insightabout it from the OP.

There is no inputbox in the OP's code, looks like Denis added it.

Denis also seems to have got the gist of what was needed, though I don't know where the parameter thing comes into it.

 

[xenou]

xenou

I think I was actually more interested in what the purpose of the loop was and hoped to maybe get some insightabout it from the OP.

There is no inputbox in the OP's code, looks like Denis added it.

Denis also seems to have got the gist of what was needed, though I don't know where the parameter thing comes into it.

I'm getting lazy I think. Third time this week I've misread a post. {sigh}.

 

[SydneyGeek]

xenou

I think I was actually more interested in what the purpose of the loop was and hoped to maybe get some insightabout it from the OP.

There is no inputbox in the OP's code, looks like Denis added it.

Denis also seems to have got the gist of what was needed, though I don't know where the parameter thing comes into it.

The parameter loop comes from long habit. I've found with DAO recordsets based on SQL strings that use a variable, I more often than not had a "Too few parameters: expected nn" message. Evaluating the parameters first fixes the problem.
So, now I add it anyway to avoid that particular runtime error.

Denis

 

[Norie]

Denis

Interesting, whenever I've got that error I've assumed that there was problem with the SQL or if it was an existing query then a problem with that.

Does the same happen when using ADO?