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.

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

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


"Fredrated" <(E-Mail Removed)> wrote in message
news:686419E5-DDA1-4094-A0FF-(E-Mail Removed)...
> 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.

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

"Douglas J. Steele" wrote:

> 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
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Fredrated" <(E-Mail Removed)> wrote in message
> news:686419E5-DDA1-4094-A0FF-(E-Mail Removed)...
> > 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.
>
>
>

Fredrated wrote:
> 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?

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.