What is the correct procedure for inserting records in multiple tables from query results

[dbh]

I have existing tables for bank records, expenses and payments. I have a query I can manipulate to show expenses and summary of payments to enable me to calculate what expenses have an outstanding amount not equal to zero. I'm looking to create something whereby I can configure my query to show the expense records I wish to use, such as pay outstanding expenses, then adjust amounts if necessary to be paid, add a payment reference (check number) and possibly a payment id then have access update the payments table with the data from the query and my input plus update the bank record with the total of the individual amounts also including the payment reference and id I added manually.

I'm guessing this is going to be using a form to display the data from my query, allowing me to change the amount to pay values, then have inputs for my payment reference and payment id with an update button to execute. Is this the correct procedure for this type of task?

[CJ_London]

Broadly, yes. It depends on your business process and how you want to record your expenditure and payments. But typically you would have a table of expenses which would have a field called say paymentFK which is blank for unpaid expenses. You would have another table of payments which would contain fields like paymentPK, paymentReference, paymentDate, paymentAmount, paidFromAccount

When you want to make a payment, you would list unpaid expenses (i.e. paymentFK is null) presumably filtered on some basis such as employee or supplier, perhaps using a multiselect listbox, perhaps using filters, to select the expenses you want to pay and then insert a new record to the payments table and update the relevant records in the expenses table paymentFK field with the paymentPK just created.

You say you want to be able to change the amount to pay. If this is the case, you might need a different process and more tables so you need to be clear about what this means - it may mean you can't assign a payment to your expenses or you need additional records or another table to record the under/over payment.

Suggest you make sure you understand the concepts of double entry book keeping as it is important you keep the two (or more) tables in balance. Also suggest you google the vba begintrans/committrans functions as a means of protecting the data from only completing one side of the transaction before there is an interruption of some sort (powercut/loss of connection to server) which prevents the other side being completed.