Reference field by it's order

[John J.]

In a form I need to run a query in vba which counts the unique number of
values of the first field of a table which is selected by the user. Is it
somehow possible to make the query generic, so that I can reference the
first field by for instance "1" in stead of the actual field name?
Thank you,
John

 

[Stefan Hoffmann]

hi John,

In a form I need to run a query in vba which counts the unique number of
values of the first field of a table which is selected by the user. Is it
somehow possible to make the query generic, so that I can reference the
first field by for instance "1" in stead of the actual field name?

No. It's not possible. Indeed there is no such thing like an order of
fields.

But you can simply get the fields name:


Dim td As DAO.TableDef

Set td = CurrentDb.TableDefs.Item("yourTableName")

MsgBox "First fields name: " & td.Fields.Item(0).Name

Set td = Nothing



mfG
--> stefan <--

 

[John J.]

Thanks Stefan. However, the code doesn't work.

The yellow line is on:
td.Fields.Item(0).Name

and the error message is:
invalid object or or object not set

DAO reference is checked.

John

 

[Stefan Hoffmann]

hi John,

Thanks Stefan. However, the code doesn't work.
The yellow line is on:
td.Fields.Item(0).Name
and the error message is:
invalid object or or object not set

The reference on CurrentDb is invalid, it's a typo. I use CurrentDbC as
proposed by Michael Kaplan:

http://groups.google.com/group/comp.databases.ms-access/msg/9fe98bb5d7cba5ea

Or you need an explicit Database variable:

Dim db As DAO.Database
Dim td As DAO.TableDef

Set db = CurrentDb
Set td = db.TableDefs.Item("Tabelle1")

MsgBox "First fields name: " & td.Fields.Item(0).Name

Set td = Nothing
Set db = Nothing


mfG
--> stefan <--

--
Option Compare Database
Option Explicit

Private m_CurrentDb As DAO.Database

Public Property Get CurrentDbC() As DAO.Database

If m_CurrentDb Is Nothing Then
Set m_CurrentDb = CurrentDb
End If

Set CurrentDbC = m_CurrentDb

End Property

 

[John J.]

Thanks! that did it...

hi John,

The reference on CurrentDb is invalid, it's a typo. I use CurrentDbC as
proposed by Michael Kaplan:

http://groups.google.com/group/comp.databases.ms-access/msg/9fe98bb5d7cba5ea

Or you need an explicit Database variable:

Dim db As DAO.Database
Dim td As DAO.TableDef

Set db = CurrentDb
Set td = db.TableDefs.Item("Tabelle1")

MsgBox "First fields name: " & td.Fields.Item(0).Name

Set td = Nothing
Set db = Nothing


mfG
--> stefan <--

--
Option Compare Database
Option Explicit

Private m_CurrentDb As DAO.Database

Public Property Get CurrentDbC() As DAO.Database

If m_CurrentDb Is Nothing Then
Set m_CurrentDb = CurrentDb
End If

Set CurrentDbC = m_CurrentDb

End Property