Any easy way to look up Table Fields in the Access VBA IDE?

Question

At present, I have to keep hopping in and out of the IDE to open my Tables and look up the names of the Fields. It would be wonderful not to have to leave the IDE every time I want to do this. 

Does anyone know an easy way to look up the names of Fields in my Tables from within the IDE?

---

DragonForest

Answer 1

The IntelliSense feature will do that on a form/report module if you can just remember the first few letters of a field name.  If you need to, you can activate IntelliSense in the middle of typing by hitting Ctrl+Space.

OTOH, if you have the screen real estate, you can have the form/report/table design window open next to the IDE window and use Copy/Paste.  Not practical for everyone, but I have dual monitors to help make this easy.

Answer 2

At present, I have to keep hopping in and out of the IDE to open my Tables and look up the names of the Fields. It would be wonderful not to have to leave the IDE every time I want to do this. 

Does anyone know an easy way to look up the names of Fields in my Tables from within the IDE?

In the Immediate window, you can get a quick list of all the fields in a table by executing the following line of code:

    Set d = CurrrentDb : Set t = d.TableDefs("YourTableName") : For Each f In t.Fields : ?f.Name : Next f

In the above, you would substitute your table's name for YourTableName.  NOTE: The code should all be on one line in the Immediate window, though it may have been wrapped to two lines in this message as you view it.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 3

Many thanks, Dirk. That's extremely helpful.

Just out of curiosity, is there any way to add that code to a toolbar/ribbon button to automate the process further?  Thanks again!

---

DragonForest

Answer 4

Many thanks. That's helpful. 

---

DragonForest

Answer 5

Just out of curiosity, is there any way to add that code to a toolbar/ribbon button to automate the process further? 

I'm sure there is, but I've never worked out the details of placing an add-on button in the VBA IDE.  It's *very* easy to create an Access form that lists the fields in a table or query whose name you enter:  just put a list box on the form with its RowSourceType property set to "Field List", and then have code set the list box's RowSource property to the name of a table or Select query.  But that doesn't stay inside the VBA IDE, which I think is what you want.

It may be possible to create a custom command-bar button for the VBA IDE that either runs a function to list fields in the Immediate window or opens a form such as I described above.  However, I don't immediately see the method to do that.  I may look into it a bit in the next few days, but can't give you an immediate answer now.  Failing that, it may be necessary to create an add-in for the VBA environment, and that may involve using functions in the Microsoft Visual Basic for Applications Extensibility library.  However, that is a bigger can of worms than I am inclined to open at the moment.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 6

Many thanks again, Dirk. I tried this, but I keep getting an error message "Compile error: Expected Function or variable." The following are the various code bits I tried:

Set d = CurrentDb : Set t = d.TableDefs("OrderEntryTABLE") : For Each f In t.Fields : ? f.Name : Next f

Set d = CurrentDb : Set t = d.TableDefs("OrderEntryTABLE") : For Each f In t.Fields : ?f.Name : Next f

Set d = CurrentDb : Set t = d.TableDefs("OrderEntryTABLE") : For Each f In t.Fields : debug.print f.Name : Next f

For each bit, all the code was on one line. I tried entering this directly in the immediate window, and also with a "?" at the front of the line. Any idea what's wrong? Many thanks in advance!

---

DragonForest

Answer 7

Many thanks again, Dirk. I tried this, but I keep getting an error message "Compile error: Expected Function or variable." The following are the various code bits I tried:

Set d = CurrentDb : Set t = d.TableDefs("OrderEntryTABLE") : For Each f In t.Fields : ? f.Name : Next f

Set d = CurrentDb : Set t = d.TableDefs("OrderEntryTABLE") : For Each f In t.Fields : ?f.Name : Next f

Set d = CurrentDb : Set t = d.TableDefs("OrderEntryTABLE") : For Each f In t.Fields : debug.print f.Name : Next f

For each bit, all the code was on one line. I tried entering this directly in the immediate window, and also with a "?" at the front of the line. Any idea what's wrong? Many thanks in advance!

All three of those example variatons work fine for me, using a table name of my own.  Am I safe in assuming that "OrderEntryTABLE" is the name of a table in your database?  You're entering each attempt on one line in the Immediate Window, correct?

I wonder if there's something else wrong in your database.  If you explicitly compile it by clicking Debug -> Compile, are any errors reported?

Is there something odd about that particular table? If you pick a different table, does it work?

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 8

Many thanks. For some reason, the system didn't like the variable "f." (must have been used somewhere else in the module). I changed "f" to "z" and now it works fine. Many thanks again!

---

DragonForest

Answer 9

you could pack this into a function and call this in the immediate window.

Function would look like this:

Public Function getFieldNames(TableName As String, Optional Separator As String = vbCrLf) As Variant
  On Error GoTo PROC_ERR
  Dim varR As Variant
  Dim db As Database
  Dim rs As Recordset
  Dim fld As Field
  varR = Null
  Set db = CurrentDb()
  Set rs = db.OpenRecordset("SELECT TOP 1 * FROM [" & TableName & "]", dbOpenSnapshot)
  For Each fld In rs.Fields
    varR = (varR + Separator) & fld.Name
  Next fld
PROC_EXIT:
  On Error Resume Next
  rs.Close
  Set rs = Nothing
  db.Close
  getFieldNames = varR
  Exit Function
PROC_ERR:
   varR = Err.Description
   Resume PROC_EXIT
End Function

I the immediate window you now simply can enter:

? getFieldNames("YourTable")

Optional you also can provide the separator (default CRLF) to be used. If you want to get a CSV list of your fieldnames, you then would enter:

? getFieldNames("YourTable", ";")

HTH

Henry

Answer 10

The only disadvantage I see here is that this is limited to tables. This may be ok but during coding in the IDE I often have to know the fieldnames of queries, not only tables.

That's why I prefer to get the fieldnames out of a recordset. I packed this myself into a function. See other posting.

Henry

Answer 11

Many thanks. That's very helpful, and much appreciated! 

---

DragonForest