accessing a table's field when they are an unknown name

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

Guest

I use different tables of data all the time with my project, and so I have to
either change the field name or else change my code everytime. For example,
one table might have a field called "Home Phone" and in the next table it
might be called "Phone". Is there some code that will retreive all the
fields in a table and asign them to a string (strData) and then asign another
value (strDataField) to the field name that goes along with it? This would
be a big help. THanks,
Charlie
 
I didn't quite follow the bit about assigning to strings, but here's an
example that demonstrates walking the Fields collection of a recordset.
Perhaps you can adapt it to your needs.

Public Sub LoopFields()

'Using DAO

Dim db As DAO.Database
Dim rstD As DAO.Recordset
Dim fldD As DAO.Field

Set db = CurrentDb
Set rstD = db.OpenRecordset("tblTest")
For Each fldD In rstD.Fields
Debug.Print fldD.Name,
Next fldD
Debug.Print
Do Until rstD.EOF
For Each fldD In rstD.Fields
Debug.Print fldD.Value,
Next fldD
Debug.Print
rstD.MoveNext
Loop
rstD.Close
Debug.Print

'Using ADO

Dim rstA As ADODB.Recordset
Dim fldA As ADODB.Field

Set rstA = New ADODB.Recordset
rstA.Open "tblTest", CurrentProject.Connection
For Each fldA In rstA.Fields
Debug.Print fldA.Name,
Next fldA
Debug.Print
Do Until rstA.EOF
For Each fldA In rstA.Fields
Debug.Print fldA.Value,
Next fldA
Debug.Print
rstA.MoveNext
Loop
rstA.Close

End Sub
 
Charlie,

In addition to Brendan's information, you can also access the recordset's
Fields collection. For example, the following code prints out (in the
Immediate window), the name and value of every field in every row of the
Customers table, using the recordset's Fields collection.

Dim db As Database
Dim rs As DAO.Recordset
Dim iCount As Integer

Set db = CurrentDb

Set rs = db.OpenRecordset("Customers")
Do While Not rs.EOF
For iCount = 0 To rs.Fields.Count - 1
Debug.Print rs.Fields(iCount).Name & vbTab &
rs.Fields(iCount).Value
Next iCount

rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing


Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Back
Top