Select then Append Query

[rushilc]

I have a form which with the push of a button opens a select query. After I have the select query open, is there a way to make a new table with these records directly from the form. I know I can do this by using a "Make Table" query, but I need to make a new table each time, but Access only lets you overwrite the table. Is there anyway you can rename the table each time you run the query in the form itself? I am new to Access, thank you!

 

[billmeye]

You could use a button and in the On Click event use an Input Box to get the name of the new table and then run the Make Table query from VBA:

Dim GetName As String
GetName = InputBox("Please enter new name")
DoCmd.RunSQL "SELECT qryYourQuery.* INTO " & GetName & " FROM qryYourQuery;"

 

[David R]

Depending on how often you run the new table, you could also automate a datestamp/timestamp being appended to the new table name.

Why are you saving them to a separate table anyway? Seems like overkill, but I don't know your business model.

 

[rushilc]

It's for a monthly mailing list, I need to use a select query from the main table and mail people each month. How would you go about making an automated date-stamp onto the table?

Thanks!

 

[billmeye]

You can either create a new table for logging or add a field when creating these tables you want:

Dim GetName As String
GetName = InputBox("Please enter new name")
DoCmd.RunSQL "SELECT qryYourQuery.*, Now() AS TimeStamp INTO " & GetName & " FROM qryYourQuery;"

 

[rushilc]

This code gives me the error, "an action query cannot be used as a row source." In your code, the two qryYourQuery is the same correct?

 

[billmeye]

You will need to replace qryYourQuery with the same name of the Select query as you stated:

I have a form which with the push of a button opens a select query

but instead of having the form open the Select query, just have it run the code.

 

[David R]

Also you may want to explore the Format() function, so you can stamp it with 20130607 for example.