What sequence to update multiple tables from a query or form

[dbh]

I have a single database where a regular routine is to query outstanding amounts from a purchases table (purchases table cross referenced against purchase payments table). I have a query that shows the records required as I adjust the date range of the query.
What I'm looking to achieve is to be able to run a process where I can identify the records in the purchases table for payment then allocate the necessary outstanding amounts into new records in the purchases table and also create a new record in the bank table with the date, reference, notes (text input) and amount automatically calculated from the query.
I have read up on APPEND and it doesn't seem to fit my need.
I have a form which displays the data from my query, automatically displays the subtotal required and has input fields for the data and transaction reference but can't find any support on creating SQL from a submit button (macro) as I thought I would just right the APPEND for all the tables required in SQL.


Thanks to anyone who can point me in the right direction.

[ranman256]

youd want a form , put in the 2 text boxes: txtStartDate, txtEndDate
your tPurchase query, qsPurchsDteRng, would use these as criteria: select * from tPurchase where [purchDate] between forms!myForm!txtStartDate and forms!myForm!txtEndDate

Q2, would use

qsPurchsDteRng and join to tPayments query that has the data needed.

depending on your db design, you could use APPEND to write the data to a 'working' table for you to set values/etc...then another query would take this table and UPDATE the main tables when you are finished with the update.