How do I the substitute a field name with a memvar? 1

[steve728]

How can I substitue a field name with a memvar below?

m_tempa = "H1" ' I store the value in a memvar
rs.Fields(m_tempa).VALUE ' I get an error when I try this.

Should be the same as
rs.Fields("H1&quot.VALUE

Below is some of the actual code:
mCtr = 1
Do While mCtr <= 10 ' Get all 10 salry entries

If mCtr = 10 Then
m_junk = 0
Else
m_junk = mCtr
End If

m_temph = &quot;H&quot; & Str(m_junk)
m_tempr = &quot;R&quot; & Str(m_junk)
m_tempg = &quot;G&quot; & Str(m_junk)
m_tempa = &quot;A&quot; & Str(m_junk)
m_tempw = &quot;W&quot; & Str(m_junk)

If rsPRTCards.Fields(m_tempa).VALUE > 0 Then
' Do process...
End If
Loop

Thanks in advance,

STeve

 

[TLady]

m_tempa = &quot;H1&quot; ' I store the value in a memvar
rs.Fields(m_tempa).VALUE ' I get an error when I try this.

If you set m_tempa as a Variable - you suppose to not assign
value to it. Value should be calculated

What you have is like

Apples = &quot;Green&quot;
rsFields(Apples).Value

Error ! Bzzzzzz - you've just determined that it is Green !
Why are you asking again ?

Good luck

 

[JeremyNYC]

Steve,

That sounds like a fine method. Are you _sure_ that the variable is getting assigned the correct value? Try putting in
debug.print m_tempa
just before the if/then line.

Also, you don't need the .Fields or .Value--they're both defaults. You should be able to use:
rsPRTCards(m_tempa)

I have a junk database set up just for testing. I just wrote this sub:
Sub Yadda()
Dim db As DAO.Database
Dim strSql As String
Dim rst As DAO.Recordset
Dim strFieldName As String

Set db = CurrentDb
strSql = &quot;select id, three from tblthree&quot;
Set rst = db.OpenRecordset(strSql, dbOpenSnapshot)
strFieldName = &quot;three&quot;
Debug.Print rst(&quot;three&quot
Debug.Print rst(strFieldName)
End Sub

and got this in the debug window:
dsf
dsf

My guess is that the value you're putting into the variable is not what you expect it to be.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[steve728]

Thank you for replying so quickly and providing so much!
Yes, I used the immediate window and displayed with ? m_tempa. I got what I expected - &quot;A1&quot; after the first pass. Jeremy, what I want to do is use the loop to check each (10) fields individually in a table rather than:
if rs.fields(&quot;A1&quot.value > 0 then
if rs.fields(&quot;A2&quot.value > 0 then
if rs.fields(&quot;A3&quot.value > 0 then

then the H1... R1...G1...W1...
etc...

By the way, I work with ADO instead of DAO. Thank you so much for replying to my question.

Steve

 

[JeremyNYC]

Hmm. I don't do ADO, so it may be that my logic doesn't hold there, but I would expect that it would...hmm...my other thought, and if you're this far into coding I assume you've considered it, and I'm sure you don't want to do so again...is that I wonder if you would be better off with a different table structure. If you're cycling through ten fields, it sounds like you've got ten _very_ similar fields in your table. If that's the case, you might be better creating a separate field to hold those data.

What happens if you adapt my simple test to your data and ADO? Can you get supplying the variable like that to work under _any_ circumstances?

Sorry I'm not getting you where you need to be.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[DaltonVB]

Here is a line from one of my apps.
Debug.Print rs.Fields.Item(&quot;PRODUCT_COST&quot.Value

Here is how you may want to apply it.
m_temp(0) = &quot;H&quot; & Str(m_junk)
m_temp(1) = &quot;R&quot; & Str(m_junk)
m_temp(2) = &quot;G&quot; & Str(m_junk)
m_temp(3) = &quot;A&quot; & Str(m_junk)
m_temp(4) = &quot;W&quot; & Str(m_junk)

iCount = rs.Fields.Count
For i = 0 To iCount - 1
Debug.Print rs.Fields.Item(m_temp(i)).Value
Next

 

[steve728]

Now that's what I'm talkin bout! Great idea! You deserve a star!

Steve

 

[DaltonVB]

Here is another look
My table has field names in sequence:
A1, A2, A3, A4, B1, B2, B3, B4, C1, C2, C3, C4
The values in the table are the same as the field name. This helped me debug.

Here is the code:

Private Sub Text0_KeyUp(KeyCode As Integer, Shift As Integer)
    Dim rs As ADODB.Recordset
    Dim cnn As ADODB.Connection
    Dim i As Integer
    Dim j As Integer
    Dim iCount As Integer
    Dim m_temp(5) As String
    
    Set cnn = CurrentProject.Connection
    Set rs = New ADODB.Recordset
    rs.Open "tblTemp", cnn, adOpenStatic, adLockBatchOptimistic
    m_temp(0) = "a"
    m_temp(1) = "b"
    m_temp(2) = "c"

    iCount = rs.Fields.Count
    For i = 0 To 2
        For j = 1 To iCount / 3
            Debug.Print rs.Fields.Item(m_temp(i) & j).Value
        Next
    Next
End Sub

Here is the immediate window output:
a1
a2
a3
a4
b1
b2
b3
b4
c1
c2
c3
c4