Recordset & Variables

[doma23]

I have something like this:

Dim ctlName(5) As String
Dim TableField(5) As String
 
ctlName(1) = "txtTextA"
TableField(1) = "texta"
 
'dim rs....
'dim cn...
 
rs.Open "tblTest", cn, adOpenDynamic, adLockOptimistic
 rs!TableField(1) = "Form_frmTest." & ctlName(1)
rs.AddNew
 

Obviously, this doesn't work (run-time error 3625; item cannot be found in the collection...), highlighted line is the one which I bolded.
Adding new records would go through the loop, this is just testing code.

Anybody knows if this is possible and if so, the right syntax?
Thank you!

[MikeTheBike]

Hi

To get the code working I suggest this, assuming you are adding a new record as implied

Dim ctlName(5) As String 
Dim TableField(5) As String 
 
ctlName(1) = "txtTextA" 
TableField(1) = "texta" 
 
'dim rs.... 
'dim cn... 
 
rs.Open "tblTest", cn, adOpenDynamic, adLockOptimistic 
rs.AddNew 
rs(1) = "Form_frmTest." & ctlName(1)
rs.Update

Note; the recordset field collection is zero based, so the first field is accessed with rs(0) (or to be even more explicit rs.Fields(0)). Also, arrays are zero based unless specified otherwise (pardon the sermon if you already know this!).

HTH

MTB

[doma23]

Hi, I don't think that will help me.
The problem is that I don't know when I will be adding new fields in the database and what the exact sequence of the fields will be.
So, what I want is when I add a new control on my form, I will just add a new field in the database and put the name of the control and name of the field in one module. And the writing procedure (adding data in the database through the loop) would remain the same.

So this:
rs!TableField(1) = "Form_frmTest." & ctlName(1)

Should represent this:
rs!txtTextA = (some value in textbox in Form_frmTest.txtTextA)