Append Query - 2 Issues (1 Viewer)

[Lissa]

I ran with this idea that I grabbed off this board and now I ran into a dead end...

I wanted to track the history of 2 specific fields on a subform. I saw an example that used an append query to do this. I created an append query that actually appends the entire record into a history table.
The fields in the append query look like this:
Field: Expr1: Forms!sbfPartsInventory_PS!SerialNumber
Append To: SerialNumber
Field:Expr2: Forms!sbfPartsInventory_PS!Date
Append To: TransactionDate
and so on....Expr3 is Status... Expr4 is PartInvID...Expr5 is the PONumber.

On the subform, in the OnChange event of the Date field of the subform
I added code to run the append query. (The user will change the date first and then change part status which is the next field on the subform).
DoCmd.SetWarnings False
DoCmd.OpenQuery "appAppendHistory_PartStatus"
DoCmd.SetWarnings True

Issue #1
When testing this by opening the subform itself....
The append query enters 8-9 entries in the history table. I'm only expecting 1 record to be appended. Not every field is changing. I can't figure out why it's appending that many records at a time. All entries are essentially duplicates except for the date_changed field which is defaulted to now(). Is OnChange the wrong event to use? Or is the append query the wrong approach for this?

Issue #2
When I test this from the parent form, when I try to change the date field I get the "Enter Parameter Value" dialog for each entry in the append query. Do I have to tell the parent form how to reference the subform
in the append query or something? by altering this statement in the subform? Forms!sbfPartsInventory_PS!Date


Any ideas would help!
Thanks!

 

[pbaldy]

1) It would help to see the full SQL.

2) Yes:

http://www.mvps.org/access/forms/frm0031.htm

 

[Lissa]

Issue #1
Here is the SQL from the append query....
INSERT INTO tblPART_STATUS_HISTORY ( SerialNumber, TransactionDate, StatusId, PartInvId, PO_Number )
SELECT Forms!sbfPartsInventory_PS!SerialNumber AS Expr1, Forms!sbfPartsInventory_PS!Date AS Expr2, Forms!sbfPartsInventory_PS!StatusId AS Expr3, Forms!sbfPartsInventory_PS!PartInvId AS Expr4, Forms!sbfPartsInventory_PS!PO_Number AS Expr5;
Issue #2
I'll try changing the reference...
Update: This is always confusing to me... these variations didn't work:
Form!frmPartInventory!sbfPartsInventory_PS.Form!SerialNumber
Me.sbfPartsInventory_PS!Form.SerialNumber

 

[Lissa]

Issue #1
This problem still exists... not to sure why it continues to append several records when I expect only one record to be appended....
I appreciate any help as I keep trying to solve this issue.


Issue #2
I finally figured out issue #2 by refering to each field as follows from the main form...
Forms!frmPartInventory!sbfPartsInventory.Form.SerialNumber

 

[pbaldy]

I just noticed that you're using the change event. This would fire with every keystroke. Try the after update event instead.

 

[Lissa]

pbaldy - that was it! I didn't realize the on change event would fire with every keystroke - I thought it would only fire after the user tabbed to the next field or saved the record.
I moved the code to the on Dirty event to capture the data before it was modified (since I'm tracking history) and that seems to be working the way I want it to.

Thanks for your help!!