Fieldnames

[Guest]

I am trying to handle Ms-Access fiednames with a variable name in VBA programs.
Name's field in MS-ACCESS Table: POOL(1), POOL(2), POOL(3), POOL(4),
POOL(5), POOL(6) and in VBA: me!pool(id) and id is a variable integer form 1
to 6.
Is thit possible? Can anyone help?

 

[Dirk Goldgar]

I am trying to handle Ms-Access fiednames with a variable name in VBA
programs. Name's field in MS-ACCESS Table: POOL(1), POOL(2), POOL(3),
POOL(4),
POOL(5), POOL(6) and in VBA: me!pool(id) and id is a variable integer
form 1 to 6.
Is thit possible? Can anyone help?

You have to build the control name as a string, and use that string as
the index into the Controls collection of the form (assuming this is a
form we're talking about).

Are your fields, and the controls bound to them, really named "POOL(1)"
and so on? That's really a bad idea, as it's going to cause you no end
of problems. However, if that's so, you can do what I think you're
asking like this:

Dim id As Integer
Dim strControlName As String

For id = 1 to 6
strControlName = "POOL(" & id & ")"
Debug.Print strControlName, Me.Controls(strControlName)
Next id

Further comment: Numbered, repeating field names like this generally
reflect a faulty table design. These fields should be stored as
separate records in a related table of Pools.

 

[Allen Browne]

In which context?

If you are trying to enumerate the Fields of the TableDef, see:
http://allenbrowne.com/func-06.html
While you can use tdf.Fields(i) to refer to the field by number, the For ...
Each ... construct is usually simpler.

If you are working with the Fields of the Recordset, you can also use:
For Each fld in rs.Fields
Debug.Print fld.Name
Next
or if you prefer:
For i = 0 to rs.Fields.Count -1
Debug.Print rs.Fields(i).Name
Next

 

[Guest]

Help, Help. Is the second script a good thing, but that is not working.
This is a vba script
[table fieldname] = "KL"
me!pool1 = "KL"
me!pool2 = "oj"
me!pool3 = "oj"
me!pool4 = "oj"

But i wish there is a better solution
[table fieldname] = "KL"
id = 1
me!pool(id) = "KL"
id = 2
me!pool(id) = "oj"
id = 3
me!pool(id) = "oj"
id = 4
me!pool(id) = "oj"

 

[Douglas J. Steele]

[table fieldname] = "KL"
id = 1
me.Fields("pool" & id) = "KL"
id = 2
me.Fields("pool" & id) = "oj"
id = 3
me.Fields("pool" & id) = "oj"
id = 4
me.Fields("pool" & id) = "oj"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Help, Help. Is the second script a good thing, but that is not working.
This is a vba script
[table fieldname] = "KL"
me!pool1 = "KL"
me!pool2 = "oj"
me!pool3 = "oj"
me!pool4 = "oj"

But i wish there is a better solution
[table fieldname] = "KL"
id = 1
me!pool(id) = "KL"
id = 2
me!pool(id) = "oj"
id = 3
me!pool(id) = "oj"
id = 4
me!pool(id) = "oj"

I am trying to handle Ms-Access fiednames with a variable name in VBA
programs.
Name's field in MS-ACCESS Table: POOL(1), POOL(2), POOL(3), POOL(4),
POOL(5), POOL(6) and in VBA: me!pool(id) and id is a variable integer
form 1
to 6.
Is thit possible? Can anyone help?