Accessing fields of a table in a database

[RohanWest]

Hi, I was wondering if anyone knows how to refer to a field
in a table by using a String?

I can refer to a TABLE by its name with a String, but I need to know how to do it with fields!

[Richyrich]

I have done this before using the fields collection. Not sure if this is what you want but this procedure takes a string argument for a table and a field and outputs the contents of that field.

If you are a bit clearer on why you want to reference a field with a string I might be able to help further.

Sub refir_to_field(strTable, strName)
Dim db As Database, rst As Recordset, fld As Field, tblDef As TableDef


Set db = DBEngine(0)(0)

Set rst = db.OpenRecordset(strTable, dbOpenDynaset)
With rst
.MoveFirst
.MoveFirst
Do While Not .EOF
Set fld = rst.Fields(strName)
Debug.Print fld
.MoveNext
Loop
End With

End Sub

[RohanWest]

Cheers, I think that's what I need, but here is the code so
far, just to show you what I've been trying to do:

Option Explicit
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
Private Const LB_FINDSTRING = &H18F

Public Sub displayFieldValues(data_in As Data, list_in As ListBox, ByVal table_in As String, ByVal field_in As Integer)
Dim RecCount As Integer
Dim Counter As Integer

data_in.Refresh
For Counter = 0 To getRecords(data_in, table_in) - 1
list_in.AddItem data_in.Recordset.Fields(field_in)
data_in.Recordset.MoveNext
Next Counter
End Sub

Public Function getRecords(data_in As Data, table_in As String) As Integer
data_in.RecordSource = table_in
With data_in.Recordset
.MoveLast
getRecords = .RecordCount
.MoveFirst
End With
End Function

So you see, I want the field_in parameter of the displayFieldValues Procedure to be a String!

[Richyrich]

I am not an expert in VB more VBA/DAO and at a quick glance I don't see why replacing


Public Sub displayFieldValues(data_in As Data, list_in As ListBox, ByVal table_in As String, ByVal field_in As Integer)

with

Public Sub displayFieldValues(data_in As Data, list_in As ListBox, ByVal table_in As String, ByVal field_in As String)

should not work, does it?

What is data_in is this an Ado recordset? sheesh really need to move over to this technology asap.

[RohanWest]

Nice one it works, before we were trying to use some other format