Fields Collection

[crowegreg]

I'm trying to learn using the fields collections. If I have a table, I'd like to retrieve the count of fields and retrieve the name of each field.

 

[boblarson]

Where do you want the output? I'll use the Immediate Window in the VBA window for you with this:

Function GetAllFieldNames()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
 
Set db = CurrentDb
 
For Each tdf In db.TableDefs
   If Instr(1, tdf.Name, "MSys") = 0 Then
      Debug.Print "Table: " & tdf.Name & "  FieldCount: " & tdf.Fields.Count
         For Each fld In tdf.Fields
              Debug.Print fld.Name
         Next
   End If
Next
End Function

 

[gemma-the-husky]

although you can do that, why would you want to do that? normally you design formsd and queries knowing the fields in the tables.

The need to programmatically examine field names is quite unusual.

 

[Galaxiom]

The need to programmatically examine field names is quite unusual.

That depends entirely on the designer. I use loops though the fields collection to create recordsets holding a list of fieldnames, which I then use as the recordset of a listbox.

 

[boblarson]

That depends entirely on the designer. I use loops though the fields collection to create recordsets holding a list of fieldnames, which I then use as the recordset of a listbox.

And I sometimes use the field names for documentation purposes as I don't like the built-in Documenter as it doesn't provide the output I desire, so I make my own.

 

[Galaxiom]

Besides, every developer needs to know how to enumerate the collections in a database.

 

[crowegreg]

Thanks guys for the discussion. Here's the reason why. I have a table which contains a list of topics. These topics are then used within a data record as a yes/no response. The DB user will be able to add more topics. I need for the code to evaluate the number of topics, and then have all of them displayed on a form so a yes/no can be set for each data record.

 

[boblarson]

Thanks guys for the discussion. Here's the reason why. I have a table which contains a list of topics. These topics are then used within a data record as a yes/no response. The DB user will be able to add more topics. I need for the code to evaluate the number of topics, and then have all of them displayed on a form so a yes/no can be set for each data record.

Sorry, but that sounds like a not so normalized, nor good design. You don't add fields for that, you use ROWS of data.

 

[crowegreg]

How would you recommend to do this. Let me give you the full scoop. Table A contains a list of topics. Table B contains inventory items. Each inventory item record will have a true/false field based on the topics within Table A. If the end user adds more topics, how is the best way to get the new topic to be displayed on the form when these topics are displayed? I've created a form with controls and labels on the form. When this form is opened, all the controls are set visible to false. It looks at Table A, then sets the controls True for the count of topics.

 

[boblarson]

The table structure would have a junction table for storing the Inventory Item and the topic.

tblItemsTopics
TopicID - Long Integer (FK from topics table)
ItemID - Long Integer (FK from Inventory table)

and you normally would assign the topics to the Item by use of a subform. So you would have a main form with the Items and a subform with tblItemsTopics as the recordsource. You would then use a combo box to select a topic to go with that item. The ItemID would be populated automatically by Access because of the Master/Child links.

That way new topics can be added to the topics table and they will become available immediately for use with that combo. Now it would potentially be possible to set up a form to use checkboxes but it becomes a bit limiting and also difficult to code. But it can be done.

 

[crowegreg]

I've done everything you've recommended. Your last paragraph is what created my question regarding field collection. I have everything working like the client requested. Thanks for your assistance!!