Update an array in a access table

[DeonO]

Hi all,
I have a table in Access 2016 with an array of fields, example Field1, Field2, Field3, etc.

I need to update these fields with data from a form. The data is stored in an Array (FieldArray(5)). Each Field in the table needs to be updated with the corresponding data in the Array "FieldArray").
I know one can use "rst.Fields("Field" & i), but it does not work for me as I am not retrieving any dta, I only want to update.

So I was thinking in the line of the following code:

for i = 1 to 5
SQL = UPDATE Tbl_MyTable SET Fields("Field" & i) = " & FieldArray(i) & " WHERE IDNumber = " & MyID & ";"
docmd.runsql sql
Next i

But the above does not work.

Any suggestions will be appreciated.

Thanks

 

[arnelgp]

use a Bound form, so you don't need an array nor an update query.

 

[Gasman]

Hard code the field names and use the Values clause?
Debug.Print the SQL?
It does not start with a " and having the " inside the string is going to complicate matters?

Always Debug.print your sql if you are not the greatest sql writer in the world.

 

[MajP]

Can you explain why you are doing this? This sounds like a really poor design and does not make much sense. Is this form unbound? If so why? If so why an array? Do you really have fields named Field1, Field2,.... That screams of a non normal table structure.

 

[Isaac]

Yes, much more explanation is needed. You stated you are OK using a form to update the data. Then just use a bound form. An Array is a VBA coding structure/component and has nothing to do with forms.

 

[gemma-the-husky]

I was going to say the same as the last two posts.
This all sounds like the table is not normalised correctly.