Copy a record via a form button

[Nicholas Scarpinato]

I need to write a code to copy a record selected in a form. This is a little
more complicated than just copying the record, however. I need to copy it to
a second table, use a user-inputted amount as the amount in the original
record, and use the difference between the user-inputted amount and the
original amount as the amount in the copied record.

For example: The database contains a customer payment coming in from the
current check, which looks a little like this:
John Doe 4567890-0 8174561234 260.00 NEW ACTIVATION COMMISSION

However, our records show that the customer only owes $180.00. What I need
to do in that case is copy that customer information to a new table,
replacing the amount in the original record with the correct amount, and
moving the difference to the new table:

John Doe 4567890-0 8174561234 180.00 NEW ACTIVATION
COMMISSION <--- Original Record

John Doe 4567890-0 8174561234 80.00 NEW ACTIVATION COMMISSION
<--- New record in second table (also known as the overpayment table)

I'm pretty sure I know how to do the copy process, but I'm not sure how to
A) ensure that I'm pulling the correct record, and B) do the math and the
ensuing updates to the records in a fail-safe way so that it can be reversed
if necessary (i.e. the rep working on the incoming check splits a payment and
then realizes it was not necessary... I need to reverse the process and
remove that line from the overpayment table.)

Anyone who might have some insights on this, please help! Thanks!

 

[Klatuu]

I assume you will be putting this code behind a command button. If so, then
I would suggest an Append query that will append to the overpayments table.
It should be filtered on customer. For the amount field, you can create a
calculated field that will be the difference between the amount in the
original record and the amount entered on the form. All the other fields
should come from your form.

 

[Nicholas Scarpinato]

That makes sense. The problem then becomes updating the original record with
the correct amount, although I suppose an update query using the user entered
amount would do this well enough. Or I could use code to insert that amount
into that amount on the form and save the record. I'll have to try that and
see if it works the way I want. Thanks!

 

[Klatuu]

If you are going to change the original amount to the user entered amount,
you don't really need another query. In the code behind the command button
that runs the query, after all the calculations are done and the record in
the overpayments table has been created, modify the original value on the
form.

 

[Nicholas Scarpinato]

I can't get this to work. I can't figure out how to copy the row via code to
the overpayment table, and I don't know how to run an append query on just
the one record. (There isn't a solid criteria in the data that I can use to
run the query.) Is there a way to pass the CurrentRecord value to the query?

 

[Nicholas Scarpinato]

I found a way to copy the records, the only problem is it's copying the first
record in the table instead of the current one. I think I know how to fix
that though. Thanks for your help!