automatic Field list table

[sha7jpm]

is it possible to do the following through VB code..

automate a table to grab the field headings from another table and list them in the first column?

ie.

table with field headings to be grabbed..
sname, fname, dob, address1, address2 etc.

table 2 grabs the field headings and lists them in the first column.
Column A.
sname
fname
dob
address1
address2

******************

Many thanks in advance..

ta

john.

 

[Jack Cowley]

The answer to your question is yes. Below is ADO code that will do this:

Dim rs As New ADODB.Recordset
Dim fld As ADODB.Field
Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection

rs.Open "YourTableName", CurrentProject.Connection, adOpenDynamic

For Each fld In rs.Fields
conn.Execute "INSERT INTO Table2 (FieldNameHere) VALUES ('" & fld.Name & "' );"
Next fld

Jack

 

[Pat Hartman]

You can also show a list of table/query columns in a combo or list box. The wizard can't build this for you since it doesn't offer the option you need. So build a dummy one and then modify it after the wizard ends. Say "I will type ....." but just click next on the next form. Then edit the list box and change the RowSourceType to Field List and choose the table whose columns you want to display for the RowSource property.

 

[sha7jpm]

Many Many Thanks.

Pat & Jack,

Many thanks for the help. I could not get the code to work at first, but then realised that the column I was inserting into was in number format and not text!

but I sorted that out and the code runs perfectly.

Pat. thanks for the combo box idea, I am looking to use this with some type of data entry form, so this is certainly a way to also do this. we get files in many different formats and layout,so it will be really useful to have both approaches.

kind regards

john.

 

[Jack Cowley]

I am glad the code worked for you but I have to admit that in my humble opinion Pat's response better suits your needs. After reading her reply and your question again I can see where her solution is easier and does not require another table. As always Pat has come through with an excellent solution!

Jack