using a variable in a recordset

Smaak · January 25th, 2012, 04:03 PM

I want to put data from a table in an array T(z,i,x). But when I try to read the data with a variable b for the fieldname, I got an error message. How can I use a variable in this case?

Thanks!

Dim T(100, 100, 20) As Integer
Set RecSet = CurrentDb.OpenRecordset("Sch", dbOpenDynaset)

For z = 1 To at
b = "T" + CStr(z)

For x = 1 To ap
For i = 1 To am
T(x, i, 1) = RecSet![b]
RecSet.MoveNext
Next i
Next x
Next z
Do Until RecSet.EOF

RecSet.Close

Set RecSet = Nothing

HiTechCoach · January 26th, 2012, 12:58 AM

Looks like part of your code is missing.

Where do you Dim the variables at, ap and am?

Where do you set the values for at, ap and am before you use then in the For....Next loops?

Smaak · January 26th, 2012, 05:14 AM

Thank you very much for your help. Perhaps my question is a litlle stupid, but I just starting with Access2010. I want to rebuild a Paradox programme in Access, so sometimes I need the help of an expert.
The table Sch contains the score the panellists give for the products. T1 is the first attribute, for instance 'sweet', T2 the second attribute, for instance 'bitter'. Each test have different attributes, so I prefer to use T1 etc.
Hereby I give the complete code.

The problem is in this statement:

T(x, i, 1) = RecSet![kolom(z)].

The content of kolom(1) is T1, that's the name of the first field to examine. If I replace this statement to:

T(x, i, 1) = RecSet![T1]

I found no problems.

This is the complete code:

Option Compare Database

Sub Krimprek()
Dim varMyArray As Variant
Dim RecSet As Recordset
Dim a As Integer
Dim x As Integer
Dim T(100, 100, 20) As Integer
Dim am As Integer
Dim ap As Integer
Dim i As Integer
Dim kolom(20) As String

Set RecSet = CurrentDb.OpenRecordset("Sch", dbOpenDynaset)
RecSet.MoveLast
am = RecSet![Nr] 'am = number of samples
a = RecSet.RecordCount
ap = a / am 'ap = number of persons
RecSet.Close
MsgBox am
MsgBox ap

For a = 1 To 20
kolom(a) = "T" + Str(a)
Next a

Set RecSet = CurrentDb.OpenRecordset("Sch", dbOpenDynaset)
Do Until RecSet.EOF

'For z = 1 To 20 'there are 20 fields, T1, T2 ... T20.

For x = 1 To ap
For i = 1 To am

T(x, i, 1) = RecSet![kolom(1)] 'no problems with T(x, i, 1) = RecSet![T1]

RecSet.MoveNext

Next i
Next x
'Next z
Loop
RecSet.Close

Set RecSet = Nothing

MsgBox T(40, 8, 1)

End Sub

Thanks again!

HiTechCoach · January 26th, 2012, 11:45 AM

Ah ... looks like a syntax issue.

To reference a field in a recorset by a string or variable use:

Code:

RecSet("field name")

or

Code:

Dim strMyVariableName  as string

strMyVariableName  = "T1"

RecSet( strMyVariableName )

Try:

Code:

T(x, i, 1) = RecSet( kolom(1) )

Smaak · January 27th, 2012, 04:05 AM

Thank you very much for your help.

But.... it still don't work. If I use this statement: T(x, i, 1) = RecSet("T1"), I have no problems, but T(x, i, 1) = RecSet(kolom(1)) gives a error message. The Dim declaration is OK. When I put the pointer of the mouse at kolom(1) is see that the content of kolom(1) is "T1". Perhaps you have a suggestion what I can try?

HiTechCoach · January 28th, 2012, 12:35 AM

What is the error message?

Smaak · January 28th, 2012, 06:37 AM

The error message is in Dutch 'Kan het element niet vinden in deze collectie'. My translation is: can't find the element in this collection. It is possible that the American version gives an other text for this error message than my poor translation.

Thanks again for your patience!

Bob.

Smaak · January 29th, 2012, 03:09 PM

I think I found the solution. I used this syntax: term = "T" + str(x). It result in temp = "T1". But it don't work. If I put the mouse pointer on term I got the answer "T1".
But... when I use this syntax: term = "T" + Cstr(x) (something like convert to string) it works! Again I get the answer "T1" when I put the mouse pointer on term. There must be a difference, but it is not visible.

The program works now very fine. Thanks again for your help finding the answer!

Bob.

HiTechCoach · January 29th, 2012, 03:28 PM

Glad to here you got it working.

The issue may be that you don't notice there is an extra space when you use: = "T" + str(x).

Why?
when convert a singed numeric value to a string the sign is added to the value. Positive number will have an extra space. Using the Trim() will remove the extra space.

This may also be caused by you using the + not & for the string incantation. When doing string concatenations the & should be used most of the time. The + actually works different. When using the + if the value to the right of the + is Null then the value to the left NOT concatenated (dropped/ignored).

This would probably fix the issue with your original syntax:

Code:

  = "T" &  Trim(str(x))