using dynamic label/variable name

[Eef]

Hi, as a beginner in VBA and Access I created a table with 6 fields named:
Lid_Name_01
Lid_Name_02
.......
Lid_Name_06

I also created a new function in a module to process the table.


I wrote code to process the 6 fields indivually but it must be possible to do this in a loop.
My statements lookes like:
In1![Lid_Name_01] = <new value>
In1![Lid_Name_02] = <new value>
...............
In1![Lid_Name_06] = <new value>

Now I want to create a loop where the last digit of the fieldname (1-6) must be substituted by a variable. Like:
For iCount = 1 to 6
In1![Lid_Name_0<value of iCount>] = <new value>
Next iCount

I tried several things, but without success so far.

Who can help me solve this problem?

[ranman256]

for iCount = 1 to 6

 In1.fields("Lid_Name_0" & iCount).value = NewValue

next

[ssanfu]

Just be aware that this is not a normalized table structure. This type of table structure is called "Committing Spreadsheet".
Currently, your structure is wide and short (like Excel). A normalized table will be narrow and tall.


A normalized structure would look like
Lid_Name
Lid_Sequence



In a normalized table, you would have records like:
In1![Lid_Name] = "Big"
In1![Lid_Sequence] = 1

In1![Lid_Name] = "Medium"
In1![Lid_Sequence] = 2

In1![Lid_Name] = "Small"
In1![Lid_Sequence] = 3


Good luck with your project........

[Eef]

Thanks, I was looking for this solution. It works!

Eef

for iCount = 1 to 6

 In1.fields("Lid_Name_0" & iCount).value = NewValue

next

[Eef]

Hi, I know what you mean. This is not the ultimate solution, but it is the way it is now.
After 40years of ICT (System Software and Databases (IDMS, DB2) I know about normalization.

Eef
It's not my fault, it's the default........!