how to get the value of a custom object properties with name stored in a variable?

[ktchan]

I know very little about vba codes.

I am using disconnected ADO recordset with unbounded form to update a back end MDB.

Have a table, tblTask, with 100+ fields in Single datatype: sngA1, sngA2, sngB1, sngB2, etc...

An unbound form with 100+ textbox controls for tblTask: txtA1, txtA2, txtB1, txtB2, etc...

Created a custom object, objTask, using loops and arrays to Get and Let those 100+ data fields, objTask.A(1), objTask.A(2), objTask.B(1), objTask.B(2), etc...

Been fine using loops to Populate objTask from disconnected ADO recordset and unbound form. Also ok in populating the unbound form controls.

Met big hiccups in update/insert data from disconnected ADO recordset to the backend table. Problem met as:
1. If I hard code the 100+ properties in building the SQL statement, the code work, e.g. strSQL = "INSERT INTO tblTask (fieldlist) Value(" & objTask.fld1 & ", " & objTask.fldn & ", " & objTask.A(1) & ", " & objTask.A(2) & ", " & objTask.B(1) & ", " & objTask.B(2) & ... & objTask.L(15) & ")"

2. When I try to use loop to generate the object properties name and store in temp string variable, it does not work. For example:
For intName = 0 To 15
For intCounter = intLBound to intUBound
strTemp = "objTask." & ary(intName) & "(" & intCounter & ")"
strSQL = strSQL & strTemp
.
.
Next intCounter
Next intName

Using immediate debug window, I know the strTemp stored the correct property names objTask.A(1), objTask.A(2), objTask.B(1)...

However, the final strSQL by using ?strSQL give me INSERT INTO tblTask (fieldnames) Value(...objTas k.A(1), objTask.A(2)... .) rather than the desirable one: INSERT INTO tblTask (fieldnames) Value(... 1, 0.5, 0, 1, 1, ...).

I tried to use val(strTemp) or pass strTemp as AccessObjectPro perty to a function to find the value of strTemp, and all other method I research in the internet and couldn't get a clue to how to proceed.

I hope I explained myself clear enough. Is there a way to make this work?

Thanks
kt

[patjones]

Hi,

I think the problem here is that when you make the assignment "objTask." to strTemp, the compiler sees it as a string assignment and nothing more. It's one thing to say strTemp = objTask.A(1) and another to say strTemp = "objTask.A( 1)". In the first instance the compiler recognizes objTask.A(1) as the actual array. In the second instance it's just a string.

I don't think you can accomplish your objective in this manner, but I would have to think about it some more. And of course, others here on the forum might be able to add input as well.

Pat

[ktchan]

Hi,

I think the problem here is that when you make the assignment "objTask." to strTemp, the compiler sees it as a string assignment and nothing more. It's one thing to say strTemp = objTask.A(1) and another to say strTemp = "objTask.A( 1)". In the first instance the compiler recognizes objTask.A(1) as the actual array. In the second instance it's just a string.

I don't think you can accomplish your objective in this manner, but I would have to think about it some more. And of course, others here on the forum might be able to add input as well.

Pat

Thanks Pat, I suppose strTemp = "objTask" & "." & ... won't help either, right? Thanks, Kin.

[ADezii]

If you could somehow change your logic and pass a varied number of Arguments to a Public Function, you could then use the Eval() Function to evaluate the Expresion and return an actual Value. the Public Function, of course, can also be used in an SQL Statement. Case in point:

Public Function fSquare(lngNumToSquare As Long)
  fSquare = FormatNumber(lngNumToSquare ^ 2, 0)
End Function

MsgBox Eval("fSquare(125)")

returns

15,625

[ktchan]

If you could somehow change your logic and pass a varied number of Arguments to a Public Function, you could then use the Eval() Function to evaluate the Expresion and return an actual Value. the Public Function, of course, can also be used in an SQL Statement. Case in point:

Public Function fSquare(lngNumToSquare As Long)
  fSquare = FormatNumber(lngNumToSquare ^ 2, 0)
End Function

MsgBox Eval("fSquare(125)")

returns

15,625

ADezii, don't know how to do that, been sending objTask.Mand(2) or strTemp that hold the string "objTask.Mand(2 ) to eval(), got errors in both cases. You can send array members to eval()?

Thanks
Kin.

[ADezii]

ADezii, don't know how to do that, been sending objTask.Mand(2) or strTemp that hold the string "objTask.Mand(2 ) to eval(), got errors in both cases. You can send array members to eval()?

Thanks
Kin.

You can send array members to eval()?

To the best of my knowledge, you cannot.