Build table using Combo Box field list

[MikeNatch]

The Combo Box Row Source Type has a Field List option. I want to use this option to create a field list in a table, then use the values in that talbe to build a new table or query. If not possible, what use is the Field List option?

 

[GingerR]

?Not sure what you mean?

If you set the combo box RowSourceType to FIELD LIST, then RowSource to one of your tables or queries, the field name of that table or query will show up in the combo box list in FORM VIEW.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[MikeNatch]

Thanx Ginger, I understand that part. What I want to do once I have those field names selected is to use them to build queries or tables. This effort to support users who are not Access savy.

 

[GingerR]

Oh.

Is this a shared db? Or each user has their own?

Well, seems like you'd have a table called

TableFieldNames

and this would be the rowsource for your combo box, right? a list of field names that a user can pick from? Or, what I would do, is make this instead be a ListBox. The user picks one or many items, and hits a button that says Make Table. Or maybe they won't be picking from a list, but instead you want them to build their own list of field names? In that case, make a table called TableFieldNames where there is a field:

CustomFieldName

Make a form with this table as it's RecordSource (as datasheet). Make a main form, and put this other form in as a subform. The user types in various field names and they go into this table. The default for UserID is the Windows ID. Even if you use a list box that they pick from, you'd loop thru the ItemSelected and put the info into the same table.

Also on the main form, maybe they also put in what they want the Table Name to be?

THEN--they hit a button that says MAKE TABLE and code runs to loop thru the table for items with their UserID in them, and builds a table. Here's my code, you'd have to tweak but you should be able to get the gist:

     Dim tdf As DAO.TableDef
     Dim db As DAO.Database
     Dim fld As DAO.Field
                
     Set db = CurrentDb
                
     Set tdf = db.CreateTableDef("NewTable")
                
                
    'Loop through CustomFieldName from table TableFieldNames and make these into new fields in the table
                Set rs = CurrentDb.OpenRecordset("Select * from TableFieldNames")
                    rs.MoveFirst
                    While Not rs.EOF
                        Set fld = tdf.CreateField(rs!CustomFieldName, dbText, 255)
                        tdf.Fields.Append fld
                        rs.MoveNext
                    Wend
                 Set rs = Nothing
            
            
                db.TableDefs.Append tdf
                db.TableDefs.Refresh

So try that out and let us know how it goes.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html