Recordset problem

[t9914]

Hello,
I have problem retrieving data from recordset.
code sample:
Sub TrialCodes()
Dim R As Variant
Dim record As Recordset
Dim SQL As String
Dim data As Database
Set data = CurrentDb
SQL = &quot;SELECT DISTINCT Data.[TrialCode] FROM data WHERE Data.[TrialCode] Is Not Null And Data.[TrialCode]<>'';&quot;

Set record = data.OpenRecordset(SQL)

record.MoveLast
record.MoveFirst

R = record.GetRows(record.RecordCount)

For intJ = 1 To UBound(R, 1)
DoCmd.RunSQL &quot;INSERT INTO testi (TrialCodes) VALUES (&quot; & R(intJ) & &quot&quot;
Next intJ
record.Close
Set data = Nothing
End Sub

Problem here is that R doesn't get any data from record.GetRows. I can see with debug that record.RecordCount is 8, so there must be data.

Any help would be appreciated!!!

Thanks...

 

[LittleSmudge]

R needs to be an ARRAY not a variant

So change the Dim to

Dim R As Variant()

And, as you are after ALL of the records that the recordset returns you don't need the record.RecordCount. Leaving all three parameters blank gets you ALL records from the recordset.


( Also, though it doesn't stop the thing working, when you come back to look at this in years to come - having the Database object name set to the same thing ( Data ) as the table name, will be very confusing to you. - It was to me for a while, until I realised what you were doing. )


'ope-that-'elps.

G LS

 

[Steve101]

You're really pretty close, except that you're looking at the first dimension of R (which is the number of columns), where you should be looking at the second dimension (which is the number of rows)

Try replacing your line of code:

For intJ = 1 To UBound(R, 1)

With the following line:

For intJ = 0 To UBound(R, 2)

FReckon that will do the trick in as far as recognising the number of rows being returned by GetRows;

Hope that this helps,
Cheers,
Steve