Simple macro to copy fields

[Jay Pee]

OK, I'll try to be as brief as possible since I've been Googling for two days and whatever comes up is not suitable or viable with my limited skills.

I have a form frmCustomer, based on tblCustomer via qryCustomer. In this form is a drop down selection field which selects PK CustomerID from tblCustomer.
In this frmCustomer is a sub form frmCustomerRelation, based on tblCustomerRelation via qryCustomerRelation. tblCustomerRelation has CustomerRelationID as PK and CustomerID as FK. Form and sub form are linked via CustomerID. It will be a one-on-one relation although the entire tblCustomerRelation is still empty and has yet to be filled in for every customer. tblCustomer already contains some 1000+ records.



Now what I want to achieve is this: when I run a macro, three fields from the sub form frmCustomerRelation have to be copied into a new record into tblLogBook. I want to run this macro on the event of a change of either three fields in the sub form. tblLogBook will have its PK LogBookID along with the three fields from tblCustomerRelation and the CustomerID from tblCustomer. There will be no relations to/with tblLogBook so none of the latter four fields have to be a FK.

How do I do this?

Note 1: an append query does not work. When I select my first customer in frmCustomer, it does work. Yet when I select another customer in the drop down, the append query keeps copying the values related to the customer I selected initially. Also, when running the append query AFTER UPDATE of one of the fields, it still copies the values which where in the field before the user edited it.
Note 2: everybody seems to want to push me into making an Audit Trial. I know next to nothing about VBA, nor do I have time to learn it. So please point me towards a simple macro along the lines of: copy field1, field2, field3 of the SELECTED customer to a new record in tblLogBook. I'll figure out how to run/trigger this macro on my own.

Thank you very much in advance.

[June7]

A macro can run an APPEND query to insert record into table. You are doing this and should work. Exit the combobox so the new selection is committed before running the APPEND query.

A macro can use SetValue method to populated fields of a form.

[Jay Pee]

Exit the combobox so the new selection is committed before running the APPEND query

How do I do this?

[Jay Pee]

I noticed the APPEND query keeps a history of what has been selected in the combobox since I open the database. So if five different customers have been selected, first time running the query will copy values of the first customer, second time of the second customer, and so on... Once I've ran it for the most recently selected customer and I run it again, it either copies values for that customer or it turns back to the first customer, haven't really been able to figure that behaviour out. Anyway, please elaborate on how I tell the APPEND query to ONLY copy the values of the most recent selected or added customer since I think that would fix my issue!

[June7]

Don't understand the behavior you describe. Post the SQL of the APPEND query you attempt.

Exit the combobox by Tab or Enter key or click something else.

[Jay Pee]

The APPEND query keeps copying the fields of ALL records that have been called up by the combobox in the past, not just the most recently selected one. I only want it to copy the fields of the record I've added or called up to edit.

INSERT INTO tblLog ( PipelineStatus, PipelineDatumOpvolging, KlantID )
SELECT tblPipeline.PipelineStatus, tblPipeline.PipelineDatumOpvolging, tblPipeline.KlantID
FROM tblPipeline;

[June7]

Need some filter criteria. Include WHERE clause in the SELECT subquery.

[Jay Pee]

Could you elaborate a bit on what I should put in that WHERE clause? I take it something that at least makes the query apply to the most recently selected record or something along those lines...

Greatly appreciate your help!

[June7]

Reference whatever field will uniquely identify the record.

INSERT INTO tblLog ( PipelineStatus, PipelineDatumOpvolging, KlantID )
SELECT tblPipeline.PipelineStatus, tblPipeline.PipelineDatumOpvolging, tblPipeline.KlantID FROM tblPipeline WHERE [fieldname]=Forms!formname!fieldname;

Otherwise:
INSERT INTO tblLog ( PipelineStatus, PipelineDatumOpvolging, KlantID ) VALUES('" & Forms!formname!PipelineStatus & "', '" & Forms!formname!PipelineDatumOpvolging & "', '" & Forms!formname!KlantID & "')"

That example assumes data are all text values. If not then remove the apostrophe delimiters.

[Jay Pee]

The first one worked.

The second one constantly keeps filling in '" & Forms!formname!PipelineStatus & "' in tblLog and not the referenced fields. However, since the first one worked I consider this issue solved!

Thanks