How do i transfer data ? (1 Viewer)

[sconly]

Can anybody out there help me with this one ?

i have a table 'subcons' and through a form and code i create a copy of 'subcons' table, lets call it 'test'.

now thru a form a select certain subcons in the 'subcons' table by typing 'S' in a field called 'Tag'

HERES WHAT I WANT TO DO:

copy the selected subcons in the 'subcons' table to the 'test' table but not delete them from the 'subcons' table.

HOW??!!


PS. confused ???

 

[antomack]

It sounds like an append query would solve your problem. You need to append from subcons to test where your criteria is met. If you want to do it in code then just create an Insert SQL statement to do what you want.

 

[ColinEssex]

Can't you use a make-table query?

Bet its not that simple!!!

Col

 

[sconly]

i'v tried the insert sql statement (see below) but i get an expected end of statement compile error.

i don't think the append query is possible either because as you can see that a table is created thru code and named thru an input box. then i am trying to transfer the data from the 'subcons' table to the newly created table where there is an 'S' in the 'tag' field.

i hope you can inderstand what i want/am doing, and can help me with my sql.

cheers !!


Function CreateTable()
Dim TableName As String
Dim strSQL As String

TableName = InputBox("Please type a project name")

DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentDb.Name, acTable, "subcons", "tbl" & TableName, True

strSQL = "INSERT INTO 'TableName' * SELECT * FROM Subcons WHERE (((Subcons.Tag)="S")));"

End Function

 

[sconly]

i've changed my sql insert to:

strSQL = "INSERT INTO TableName * select * FROM Subcons WHERE ([Subcons.Tag])='S'));"

the good thing is i don't get the compile error, which is nice. but nothing gets transferred .