Changing select query to append query with VBA

[ser01]

I have a select query that uses a criteria to select some records and puts them in a table for the user to modify them. The SQL for the query is being changed with VBA when the form is launched because the criteria is dynamic Once it is done being changed i would like to be able to put the records in a different table than the one the records came from. The only way to do that (than i seem to think of) is to use VBA and change the SQL of the query making it an append query. So this query would be changed back and forth in VBA. Is this the right way to do this and common practice or do I need to look for a different solution?

[maximus]

Let me get this straight you have a select query which you use to populate a form. You want to use the return values of the same query to insert into another table using an insert query.

I have two simple tables
Give={ID,A,B}
Take={ID,A,B}

I have used a command button to insert the result of a select query into Table Take from Table Give. Here is the code.

Private Sub Command0_Click()
Dim strSQLInsert As String
strSQLInsert = "Insert Into take (ID,A,B) SELECT ID,A,B FROM Give WHERE ID = 1"
DoCmd.RunSQL strSQLInsert
End Sub

So now you can use the same select query to get the values for an Append query and insert the record in your table.

If this solves your problem please mark this thread solved.

[blah]

Let me get this straight you have a select query which you use to populate a form. You want to use the return values of the same query to insert into another table using an insert query.

I have two simple tables
Give={ID,A,B}
Take={ID,A,B}

I have used a command button to insert the result of a select query into Table Take from Table Give. Here is the code.

Private Sub Command0_Click()
Dim strSQLInsert As String
strSQLInsert = "Insert Into take (ID,A,B) SELECT ID,A,B FROM Give WHERE ID = 1"
DoCmd.RunSQL strSQLInsert
End Sub

So now you can use the same select query to get the values for an Append query and insert the record in your table.

If this solves your problem please mark this thread solved.

He didn't ask (and I didn't google) how to insert records. He asked how to programatically change a query from "select" to "append."

Completely different animals, comprende?

OP: given the way the query designer works, one would think this is a property that you could edit and therefore have the sql changed automatically BUT I'm afraid I'm on the same hunt as you. Gluck.