Copy record data to another table through form

[MangoFruit]

Hi all,

I have a form with 2 subforms, each based on their own table. One displays categories of invoices (e.g. rent, electricity, etc.) including some details like monthly costs.

I would like users to be able to select one of those categories and copy this to another table, after which they can enter on what date the invoice was paid, to make a history of payments.
At first I based fields in the history table on fields in the categories table so that you could simply pick a category from a combobox. I had an after update event on that combobox that also automatically set the 'costs' combobox to the matching price.
Worked fine, but had one snag: if I update the costs of a category in the categories table (e.g. the rent goes up) then all the costs in the history table was also updated because the fields got their info from that table.

So now I no longer have field from the history table based on the other table, and want to use 'set value' to copy values from one table to the other. I'd like the user to be able to somehow select a category with a single click on a button, and getting the info for that category copied. However, how can I get access to know from which record I want the fields copied?

Or is there a completely different way to get a history of payments that works much better?

Thanks!

 

[Uncle Gizmo]

Where is the invoice number?

 

[MangoFruit]

Hi,

Each table has it's own autonumber as ID. I'm not storing any 'real' invoice numbers because it's not for business purposes.

 

[Uncle Gizmo]

>>>>*they can enter on what date the invoice was paid,<<<

Okay so there's no in invoice number. How do you propose to reconcile the information in the new table with the old table then?

 

[MangoFruit]

I don't think I explained the problem well enough, but by now I found the answer. I found out Access automatically selects the correct/current record when you place a button in the details section of a form. I assumed I somehow had to tell Access which record I wanted the data from so I was stupid enough not to try it. So with just the push of that button and some code I was able to transfer data of all the fields of that record to fields in another table.

Thanks anyway.