accessing fieldnames programatically

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
Back
Top