To be honest, I still do not see the need for the list table. You can accomplish the same thing with just your charges table. If you want to go that route, I would add a simple yes/no field to the charges table and set its default property to yes/true. This way when anyone enters data, the record is flagged. You would then create a query that only selects records where the yes/no field=true and base your entry form on that. If no records are flagged as yes, the form opens up ready to enter new records.
You can then base your report on a query that pulls only those flagged records. Once the OK is given, run an update query to change the yes/no field from yes to no. Your form will be "cleaned" of any records and ready for the next week.
If you still want to pursue the two table approach, you would need to run an append query to select the records from the list table and add them to the charges table. The query would look like this:
INSERT INTO CHARGES ( SITE, PDATE, PAMOUNT, [DESC], DESC1, ACHGROUP )
SELECT List.SITE, List.PDATE, List.PAMOUNT, List.DESC, List.DESC1, List.ACHGROUP
FROM List;
Then you would need to run a delete query on the list table to remove the records; that query would look like this:
DELETE *
FROM List;
I would be hesitant to put the button to run the queries on the same for where your users are entering data since they might click the button at the wrong time and mess up your process. I would run the queries directly or from a separate form that only you have access to.
If you wanted to run the two queries automatically with the click of a button, the code would look something like this:
Code:
Private Sub Command0_Click()
Dim myAppendQuery As String
Dim myDeleteQuery As String
myAppendQuery = "INSERT INTO CHARGES ( SITE, PDATE, PAMOUNT, [DESC], DESC1, ACHGROUP) "
myAppendQuery = myAppendQuery & " SELECT List.SITE, List.PDATE, List.PAMOUNT, List.DESC, List.DESC1, List.ACHGROUP FROM List"
myDeleteQuery = "DELETE *FROM List"
CurrentDb.Execute myAppendQuery, dbFailOnError
CurrentDb.Execute myDeleteQuery, dbFailOnError
End Sub