accessing fieldnames programatically

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MYKLIV - 27 Jul 2005 00:07 GMT

I am trying to access the fields in an access table, through an ado
recordset, (which works), but I am trying to loop through the filenames in
the table progamatically. The fieldnames are as follows...

Mon_type1
Mon_type2
Mon_type3
Tues_type1
Tues_type2
Tues_type3
Wed_type1
Wed_type2
Wed_type3

I have tried doing it by filling a couple of array's as...

Text(2)
text1
text2
text3
...for the textbox name...

Day(2)
Mon_
Tues_
Wed_

and

Field(2)
type1
type2
type3

concatenating the day & field from the array's together

fieldname = Day(x) & Field(x)

and then looping through and painting the form with something like...

forms!frmInfo!!(text) = (fieldname)

and looping through something like that, but access is having problems with
(fieldname) even though it is enclosed with paren's. I have looped through
the textboxes on a form like this successfully and thought that looping
thfieldnames would be just as easy, but it doesn't seem to be working out.

Does anyone know how to refer to this ? Is it even possible ??

Ideas ?

TIA !!!

Michael

Reply to this Message

Brendan Reynolds - 27 Jul 2005 00:52 GMT

Just walk the Fields collection ...

Public Sub ListFieldNames()

Dim rst As ADODB.Recordset
Dim fld As ADODB.Field

Set rst = New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection

'replace 'tblSource' with name of your table
.Source = "SELECT * FROM tblSource"

For Each fld In .Fields
Debug.Print fld.Name
Next fld
End With

End Sub

Signature

Brendan Reynolds (MVP)

>I am trying to access the fields in an access table, through an ado
> recordset, (which works), but I am trying to loop through the filenames in
[quoted text clipped - 51 lines]
>
> Michael

Reply to this Message

MYKLIV - 27 Jul 2005 03:05 GMT

Brendan,
Thanks, it wasn't the exact solution, but it did put me on the right path !!

I have 5 sections of information on the form, and I have to loop through
each one. some are textboxes, some are combo's and some are checkboxes. Also,
as it turns out, I didn't need the .Name after all, I just used the .Value
instead.

Here's a small section of the code I used. It works great. Thanks again for
all of your help !!

Michael

Dim rstPaintAAR As ADODB.Recordset
Dim fld As ADODB.field
Dim sect As Integer, a As Integer
Dim x As Integer, xx As Integer
Dim check As String

Set rstPaintAAR = New ADODB.Recordset
rstPaintAAR.Open "qryDisplayAARByAARID", CurrentProject.Connection,
adOpenDynamic, adLockOptimistic

If sect = 4 Then
x = 100
xx = 1

Do Until x = 900
Do Until xx = 13

z = x + xx
check = "check" & CStr(z)
Forms!frmEditAAR!(check) = rstPaintAAR(a).Value

xx = xx + 1
a = a + 1
Loop

x = x + 100
Loop

sect = sect + 1
End If

> Just walk the Fields collection ...
>
[quoted text clipped - 72 lines]
> >
> > Michael

Reply to this Message