Concatenation Issue

[chromachem]

We use Field7 thru Field34 to store the tests to be performed on a certain sample.
Sort on LAB ID.
We have a recordset for one sample that is selected from a combo box.

The first MsgBox rst.Field7 shows the value from the table for Field7 for the selected sample - no problem.
When we make the words rst.Field7, rstField8, rstField9 etc.. by concatenation:
MsgBox strRST shows the text "rst.Field7" in the MsgBox, not the value rst.Field7 from the table.

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM tblToDoByID WHERE LABID ='" & [cmbFirst].[Column](0) & "';")


MsgBox rst.Field7
intA = 7
strRST = "rst.Field" & Trim(Str(intA))
MsgBox strRST

For intA = 7 To 34
strField = "Field" + Trim(Str(intA))
strField = strRST
Next intA


Set rst = Nothing
Set dbs = Nothing

[CJ_London]

it will do, the first msgbox is referring to the rst object, the second to a string

try msgbox rst.fields("field" & inta)

[chromachem]

That works great. Now I can't get the value to appear in the form.
In the code below

strField = "Field" & intA
strField = rst.Fields("Field" & intA)

If I put in a test line of code (where intA =7)

Field7 =  rst.Fields("Field" & intA)

The value does appear in the form.

[CJ_London]

Now I can't get the value to appear in the form.
The value does appear in the form.

sorry - it does or it doesn't?

and why not just use bound controls in a subform?

[chromachem]

Everything works except the values do not appear on the form. The msgbox shows what it should. The values just don't show up on the form.
I have no clue about subforms. I would have thought the form was already bound to the table.

Option Compare Database


Dim strField As String
Private Sub cmbFirst_LostFocus()




Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM tblToDoByID WHERE LABID ='" & [cmbFirst].[Column](0) & "';")


For intA = 7 To 33
   strField = "Field" & intA
   strField = Nz(rst.Fields("Field" & intA).Value, "")
   MsgBox strField
   
   
      If strField = "" Then
        intA = 33
      End If
Next intA


Set rst = Nothing
Set dbs = Nothing


End Sub

[CJ_London]

your code does not assign a value to a control so I'm not at all clear what you are trying to do - perhaps you can explain in plain English.

[chromachem]

I think if you look at the working line of code below, you will see what I was trying to do.
In English, I was trying to take the value in a field, and make it into a caption. The problem I was running into was two fold.
1) I needed to get the value out of the table. You gave me the line of code that allowed me to get the value of "Field7" out of the table. For instance Ag or Cr or Radon or whatever.
2) I needed to be able to do a concatenation on the left side of the = sign so that I could use that value as a caption of a label (lbl7,8,9,...)

Forms("frmIDONX")("lbl" & intA).Caption = Nz(rst.Fields("Field" & intA).Value, "")

We have 20 check boxes and labels (ck7,8,9..., lbl7,8,9...) on the form. This line does exactly what I have been trying to do. Pick a sample with the combo box, and all the tests for that sample populate the labels next to the associated check box.
When the form is finished, we will check off the tests for a report, and produce the report based on the selected tests.

[chromachem]

Btw - thank you!!!!!!!!!!!!!!!!