Getting Values out of a recordset

[theracer06]

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

Code:
' 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?

[Rawb]

What are _Country Filter and ID COUNTRY? If they're Fields in a Table/Query, you should use dot notation instead of bang notation:

sqlstr = "Select [_Country Filter].[ID COUNTRY] from [_Country Filter]"

Also, is this a Linked Table (in a Split database) or a Local Table? Because you're not specifying a Recordset type in the .OpenRecordset Method, you may be getting a type that doesn't support .MoveFirst or .MoveLast (like dbOpenTable which uses .Seek instead). If all else fails, try changing your OpenRecordset line to the following:

Set rsread = db.OpenRecordset(sqlstr, dbOpenDynaset)

[NTC]

My design would be to use DLookUp method to set a value. Something like:
Dim Whatever As String
Whatever = DLookUp, "etc etc" etc

then you have a value for Whatever that you can use in another vb or sql statement.

you might want to explore the DLookUp method in vb help.

Hope this helps.

[theracer06]

Hi Rawb & NTC,

thanks for the suggestions! Tried the dot notation and the dbOpenDynaset, unfortunately Access is still not able to determine the item on that particular line...

Also looked into DLookup but I did not figure out how to use it in a loop which gets out the value of the record in first row first column then move on to second row first column then third and so on...

Help I've been trying to solve this for three days now...

[pbaldy]

I'm pretty sure the Fields collection is zero based, but I don't see the purpose of looping the fields, since you only have one ([ID COUNTRY]). Were you meaning to loop records? Looping the fields would only make sense if you returned more that one.

[Rawb]

Wow, I didn't even notice that.

pbaldy's right: What you're doing is running the exact same Query repeatedly. This Query is returning a Recordset with a single Field in each Record. You're then trying to read (from the same Record every time), Fields 2 through NC+1. Which don't exist since you only have one Field in each Record.

If you're trying to run through each Record returned, try running the Query before the loop starts. Then, at the end of each loop iteration, just use .MoveNext to go to the Record in the Recordset.

  sqlstr = "SELECT [ID COUNTRY] FROM [_Country Filter]"
  rsread = db.OpenRecordset(strsql, dbOpenDynaset)

  If Not rsread.RecordCount = 0 Then
    Do Until rsread.EOF
      CountryCurveSelect = rsread("ID COUNTRY")

      . . . ' Do your stuff here

      rsread.MoveNext
    Loop
  End If

Of course without knowing more of your Code, Table setup, Form, etc. I can't tell you if the above code is what you're looking for or not. It does the following:

1. Loads a list of every COUNTRY ID in the _Country Filter Table into the rsread Recordset
2. It assigns the first COUNTRY ID returned into your CountryCurveSelect variable.
3. Performs the rest of your code on it
4. Starts the whole process over (from Step 2) on the next COUNTRY ID in rsread
5. Repeats steps 2-4 until all of the COUNTRY IDs have been read.

[theracer06]

Thanks Rawb, now you nailed with precision!
One more step into learning how to use recordsets!

Best,TR