make a table from an input box

[gal4y]

I would like to make a table of a predefined query. An input box (text box) on a form asks the user for the table name and hits a command button to make the table. This is to archieve the query each year.

Is there an easy way to do that using code?

Thanks in advance for any help.

Greg

 

[clapper62]

I don't know if this is the easy way but...

Dim FldName(10) As String: 'set the 10 to the max # of Fields in your query
Dim FldType(10) As String: 'set the 10 to the max # of Fields in your query
Set dbs = CurrentDb
Set tdfNew = dbs.CreateTableDef("Contacts&quot: 'Substitute "Contacts" with the Value in the textbox on your form
Set qdf = dbs.QueryDefs("qryData&quot: 'Substitute "qryDate" with the name of the query you want to create the table from
qField = qdf.DateCreated: 'Get date query was created
FldCnt = qdf.Fields.Count: 'get Field count of query
For fldLoop = 0 To FldCnt - 1
FldName(fldLoop) = qdf.Fields(fldLoop).Name: 'get Field Name
FldType(fldLoop) = qdf.Fields(fldLoop).Type: 'get Field Data Type
With tdfNew
.Fields.Append .CreateField(FldName(fldLoop), FldType(fldLoop)): 'Add The field to the Table Collection
End With
Next fldLoop
dbs.TableDefs.Append tdfNew: 'Update the new table
Set qdf = Nothing
Set tdfNew = Nothing
'dbs.TableDefs.Delete "Contacts":' ######### Example Code to Delete new Table ########
Set dbs = Nothing

This Code only creates the table it doesn't populate it with data. if you need help with this reply on this thread

Hope this works for you