return column number by VBA

[ice051505]

Hi, can anyone tell me how to return number of field by VBA
Not the FieldCount, but I want to return the single number of field from the table
example:
I have a table with the fields like below
|id | continent | country | province | city | area |......



I don't know if there is a statement like fieldNumber=2 will represent the column [continent] in the table

Hope anyone can help me out here, Thanks!

[pbaldy]

It might help to know what you're actually trying to accomplish. In a recordset, you can use a number to refer to a field. I don't like to do it because it isn't easy to follow later. I think you can also do it with a TableDef.

[ice051505]

Can you tell me about how to do it with recordset or TableDef? Just a small sample code would help a lot! Many thanks!

It might help to know what you're actually trying to accomplish. In a recordset, you can use a number to refer to a field. I don't like to do it because it isn't easy to follow later. I think you can also do it with a TableDef.

[pbaldy]

This type of thing:

  Dim strSql                  As String
  Dim db                      As DAO.Database
  Dim rs                      As DAO.Recordset

  Set db = CurrentDb()

  strSql = "SELECT * FROM tblNumbers"
  Set rs = db.OpenRecordset(strSql, dbOpenDynaset)
  MsgBox rs.Fields(0)
  Set rs = Nothing
  Set db = Nothing

[ice051505]

I will try this, Thanks A Lot!

strSql = "SELECT * FROM tblNumbers"
Set rs = db.OpenRecordset(strSql, dbOpenDynaset)
MsgBox rs.Fields(0)
[/CODE]