Update an array in a access table (1 Viewer)

DeonO

Novice Programmer
Local time
Today, 10:51
Joined
Sep 15, 2011
Messages
31
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

..forever waiting... waiting for jellybean!
Local time
Today, 16:51
Joined
May 7, 2009
Messages
19,170
use a Bound form, so you don't need an array nor an update query.
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:51
Joined
Sep 21, 2011
Messages
14,050
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

You've got your good things, and you've got mine.
Local time
Today, 04:51
Joined
May 21, 2018
Messages
8,463
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

Lifelong Learner
Local time
Today, 01:51
Joined
Mar 14, 2017
Messages
8,738
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

Super Moderator
Staff member
Local time
Today, 08:51
Joined
Sep 12, 2006
Messages
15,614
I was going to say the same as the last two posts.
This all sounds like the table is not normalised correctly.
 

Users who are viewing this thread

Top Bottom