Use Command button to clear one table and amend another

[HotelierDream]

Hello All,



I have two tables in my database one is names AP Transmittal and one is Invoices.

The AP Transmittal table is used to generate a weekly report that is sent to pay invoices, I would like to create a button on the form I use to add date to AP Transmittal to clear the AP table and move all the records over to the invoices table. The fields of each table are identical, I am thinking the best option is a query? What are the ways I could accomplish this?

[Micron]

I couldn't say it better myself so here's a link
https://support.microsoft.com/en-us/...8-8ef649cf3596

[HotelierDream]

I figured it out, I had to make two separate Queries (One to add to the Invoices table and one to Delete from the AP Transmittal Table) then I set the query specifications on each of course then I made a Command button and added the following code. (The SQL code was found by taking the SQL generated by the queries).

INVADD = The Query Used to add records from the AP Transmittal table to the invoices table.
APDEL = The Query used to delete all records from the AP Transmittal Table.

Private Sub Command58_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "INVADD"
DoCmd.RunSQL "INSERT INTO Invoices ( [Vendor Name], [Invoice Number], [Invoice Date], [GL CODE], TOTAL ) SELECT [AP Transmittal].[Vendor Name], [AP Transmittal].[Invoice #], [AP Transmittal].[Invoice Date], [AP Transmittal].[GL Account], [AP Transmittal].Amount FROM [AP Transmittal];"
DoCmd.OpenQuery "APDEL"
DoCmd.RunSQL "DELETE [AP Transmittal].ID, [AP Transmittal].[Vendor Name], [AP Transmittal].[Invoice #], [AP Transmittal].[Invoice Date], [AP Transmittal].[Due Date], [AP Transmittal].[GL Account], [AP Transmittal].Amount, [AP Transmittal].Received, [AP Transmittal].Initals FROM [AP Transmittal];"
DoCmd.Requery
End Sub


Posting so if anyone has this issue they can use the format provided.

[Micron]

a) you forgot to turn warnings back on.
b) if you turn warnings off and code errors, code will cease and warnings will remain off. You should always have an error trap that will turn them back on if it fails.
c) the above is why the .Execute method is better for running action queries or sql statements. You don't affect warnings by using that method.

Please try to remember to use code tags (# on forum menu bar) and indentation for more than a couple of lines of posted code.

[Welshgasman]

You do not really move records from A to B, but mark them them as processed in some way.?

[HotelierDream]

No the first Queryappends items from the AP transmittal Table to the Invouce table and the second Query deletes all records in the AP transmittal table so that it is ready for the next week since it is a weekly report.

Also, I did turn warnings back on thank you for catching that! @Micron.

[Welshgasman]

No the first Queryappends items from the AP transmittal Table to the Invouce table and the second Query deletes all records in the AP transmittal table so that it is ready for the next week since it is a weekly report.

Also, I did turn warnings back on thank you for catching that! @Micron.

Be prepared for a quickly growing DB.

[Micron]

I agree the record movement seems like an odd thing here. Whatever processing is going on, it can probably be eliminated by a simple date or some kind of status field. I originally took it that this was going to be a one time thing.