Append and update

[Homegrownandy]

This is a continuation from the problem described here:



https://www.accessforums.net/showthread.php?t=58337

Im following a few guides/tutorials but I can't achieve what I want. Before I make this too complicated Just getting the basics right would help. I can then work from there.

This relates to one table. The "jobs" table contains a field called "Job_Notes". I have other test tables and other methods of gathering notes, but for the purpose of this we can asssume thats all I have.

I want the user to input notes into a blank form, then these notes to be added to that field when the user has finished. Basically I need the new information to be appended to the existing and then that field should be updated. This can be done through another table I just cant get it to work. If anyone has a sugestion or a reference to somthing I would appreciate it.

Thanks!

[ranman256]

after user enters data and hits save button, execute an append query using the record key.
docmd.openquery "quPostNote"

the sql of which would be like
update table set [NoteField] = [NoteField] & txtBox where [key] = txtKey

[Homegrownandy]

I've been on this all day and I'm still clueless. This is the SQL code.

INSERT INTO JobT ( Job_Notes )
SELECT z_append_1.[append_1] & " " & [job_notes] AS test
FROM JobT INNER JOIN z_append_1 ON (JobT.Job_ID = z_append_1.Job_ID) AND (JobT.Job_ID = z_append_1.Job_ID)
WHERE (((z_append_1.Note_ID)=[Forms]![z latest]![z_append_1 Subform].[Form]![Text4]));

I CAN get the data I want in the query but if the query is closed when I press the command button, I get a message that says It cant add the record because of a key violation.

But even having the data in the query (when its open) isnt enough. I need it to update the table.

Im sure its not meant to be this hard but it's one of the most difficult things ive done in access haha. If you could offer any more help i would appreciate it.

[Homegrownandy]

Im not moving any data relating to primary or secondary keys so how is there a key violation :/

[Homegrownandy]

done it! only tood a week