creating a table and adding data

[jcatt]

Based on a prior post I created the following procedure, which creates a table (GREAT!!) but I can't get the data per the "where" statement to transfer over.

I need all the fields populated (name, social, term, sumofamt) where I choose the term and the amount. I was trying to start by choosing only one field (term) but nothing comes accross.


Private Sub Command3_Click()


Dim TableName As String

Dim strSQL As String

TableName = InputBox("Please type a table name, per semester&quot

DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentDb.Name, acTable, "Addchangename", TableName, True
MsgBox "Table " & TableName & " created"

strSQL = "INSERT INTO [" & TableName & "]SELECT AddChangeName.* From AddChangeName where (([AddChangeName]![term]= 200301));"

MsgBox "Data Transferred to " & TableName

End Sub

Any help is greatly appreciated.

Judy

 

[vb6novice]

Judy,

Your concept appears sound. It looks like you need a space before the word SELECT and you should change the way the parentheses are arranged in the WHERE clause to

WHERE ((([AddChangeName]![term])= 200301))

Have you tried this in a query to ensure it works before you do it in code?

You could also save a step by dropping the TransferDatabase step and have the query make the table by changing the SQL to:

strSQL = "SELECT AddChangeName.* From AddChangeName INTO [" & TableName & "] WHERE ((([AddChangeName]![term])= 200301));"

Hope this works for you.

 

[jcatt]

Hi VB6Novice;

Thank you for your help. I found the answer to be the last argument on the DoCmd transfer... was supposed to be false.

Thanks Again,


Judy