Reference field by it's order

J

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
 
S

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?
Click to expand...
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 <--
 
J

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
 
S

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
Click to expand...
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
 
J

John J.

Thanks! that did it...

Stefan Hoffmann said:
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
Click to expand...
 
Top