programatic field reference

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Fredrated - 31 Jul 2007 19:40 GMT

Could someone point me to the part of the access object model that allows me
to refer to fields in a table programatically by their name? A code example
would be greatly appreciated.

Reply to this Message

Douglas J. Steele - 31 Jul 2007 19:59 GMT

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

Set dbCurr = CurrentDb()
For Each tdfCurr In dbCurr.TableDefs
If (tdfCurr.Attributes And dbSystemObject) = 0 Then
Debug.Print tdfCurr.Name
For Each fldCurr In tdfCurr.Fields
Debug.Print fldCurr.Name
Next fldCurr
Debug.Print
End If
Next tdfCurr
Set dbCurr = Nothing

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Could someone point me to the part of the access object model that allows
> me
> to refer to fields in a table programatically by their name? A code
> example
> would be greatly appreciated.

Reply to this Message

Fredrated - 31 Jul 2007 23:36 GMT

Thanks, that's what I asked for, but I can't quite seem to extract what I
need from it, so let me explain a little more.

I am importing data that I am told may vary in field order, so the first
record is the field list of imported data, the rest is the data.
I import it into a table that has 80 fields, field1, field2, field3... field80

What I need to do is cycle through the 80 fields like so, to figure out
where the data in each column belongs:

dim rs as recordset
.........
for i = 1 to 80
x = rs!field[i]
<look up where the data in this column goes...>
etc.
next i

Is it possible to refer to a field in a recordset indirectly in some way
like this?

Thanks
Fred

> Dim dbCurr As DAO.Database
> Dim tdfCurr As DAO.TableDef
[quoted text clipped - 17 lines]
> > example
> > would be greatly appreciated.

Reply to this Message

pietlinden@hotmail.com - 01 Aug 2007 00:05 GMT

> Thanks, that's what I asked for, but I can't quite seem to extract what I
> need from it, so let me explain a little more.
[quoted text clipped - 16 lines]
> Is it possible to refer to a field in a recordset indirectly in some way
> like this?

The hard part of this, IMO, is matching up which field in the table
belongs with which column in the spreadsheet, not the import or
anything else. If the columns are named, then you could just use
that. If you link to the Excel file, you could treat it like a normal
Access table.

rsFinal.AddNew '---add a new record to the table
'set field values...
rsFinal.Fields("FieldNameX")=rsXL.Fields("FieldName")
rsFinal.Fields("FieldNameY")=rsXL.Fields(4) '<---fifth column of the
table. (Count from zero.)
rsFinal.Update

check out AddNew in the help... maybe that will help a little.

Reply to this Message