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.