Using Variables in Field Names

[ekoto]

I am gathering information from a user form, using it in calculations and writing it to a table. The fields I am writing to are named F1, F2, F3... F24. Instead of writing add record code 24 times (many times over) I would like to use a variable in the field name and iterate the variable. I have tested the following code but receive "object not found in this collection" error messages:

dummy = 1
For i = 1 to 24
Table1!["F"+str(dummy)] = calculation
dummy = dummy + 1
Next i

Because of the "STR" function I've also tried this code using field names with a space between the F and the number (ie F 1). I've heard this is possible but my numerous variations on the above test have all failed. I'm working in Access 97 and don't have much experience with advanced VB programming. Any solutions would save me writing hundreds of lines of code. Thanks

 

[KDg]

I don't believe that you can do this by trying to concatenate into a field name. What you are trying to do sounds more like either an INSERT INTO query if all your calcs are done by the time you're writing the record* or an UPDATE if you want to do it one at a time.

For example, if you have 24 text boxes on your form, named T1..Tn. Your INSERT query would go

strSQL="INSERT INTO mytable (F1, F2,...F24)
VALUES (" & T1 & ",..." & Tn & ");"

dbs.execute strSQL

As a footnote its better to use & for concatenation than + as its more readable later and can give different results

HTH

drew


*this would be preferable if it's multi-user as you can use 1 query instead of 24

 

[Jeff]

You can use variables and concatenation to reference field names. The following code is an example that puts the words "It works!" into a text box named "Text" on the current form.

Private Sub Command0_Click()
Dim testVar As String
testVar = "xt"
Me("Te" & testVar) = "It works!"
End Sub

Hope this helps,
Jeff

 

[Zeich]

Jeff is correct- However, it seems to me that the code must be attached as an event procedure and cannot be called from a module. Let me know if this is not correct. Jeff, thanks for this info - I had looked everywhere for this and couldn't find any clues.

 

[belton]

How would it be done, if T1 ... TN were table fields? (ie values (" & Table1.field1 & ", " & table1.field2) & ");" . . .

 

[Rich]

Surely storing any calculated field in a table is not recommended but 24???