Append or Update query (1 Viewer)

[Jon123]

I have a form that I use to edit some information on a part. So I have a combo box that the user selects the part they want to edit. Once they have the correct part there are 3 yes/no boxes that would be updated, a Date field and a comment field. Once modified I want to run the query to update the current record not create a new record. I'm getting an error my query want to null out some fields and I don't know why????

jon

 

[pbaldy]

The simplest solution would be to bind the form to the table, so that any changes made to the form are reflected in the table. That said, of the 2 you want an update query; an append query adds new records. Hard to say why you're getting an error without knowing what the error is or what the SQL of the query is.

 

[Jon123]

So on the update query grid what do I put in the "update to" part of the field? If there is a check in the box for that field?

 

[pbaldy]

A form reference, if you want the value to come from a form.

 

[Jon123]

so something like this ?

[Forms]![Frm-updating damaged Qtz]![PartAvail]

???

 

[pbaldy]

Yes. Have you tried it?

 

[Jon123]

yes, the text box field updates but the Yes/No field does not???

 

[pbaldy]

Again, hard to say why without seeing the query.

 

[Jon123]

UPDATE [Tble-Qtz Kits All parts] SET [Tble-Qtz Kits All parts].[Part AvailToUse] = [Forms]![Frm-updating damaged Qtz]![PartAvail], [Tble-Qtz Kits All parts].History = [Forms]![Frm-updating damaged Qtz]![comment]
WHERE ((([Tble-Qtz Kits All parts].[Part ID])=[Forms]![Frm-updating damaged Qtz]![Part ID]));

 

[pbaldy]

Is the first one the one failing? What kind of control is that, and what does it contain? Have you moved focus off the control before running the query?

 

[Jon123]

yes I have moved control. It is a check box If I check that box I want it to update to the table same as if It was checked and I unchecked it

 

[pbaldy]

I would expect that to work. Can you attach the db here?

 

[Jon123]

found it. I had a typo in my code.
It works now. However, after I make the changes and update the record and then try to close the form is gives me a error that the record can not be saved because it would create a duplicate value in the primary key? Why?

better yet how do I stop that from happening?

thank you for your help

 

[pbaldy]

It sounds like the form is bound to the table. You want that or the query, not both.

 

[Jon123]

is there a way to add to fields together and then add it to a field in a table.
example. If I have a date field and a comment field on a form can I combine them to 1 field? Date field is 9/24/17 and the comment is "Part returned from repair" So I want this to be added to the table field History as 9/24/17 Part returned from repair.

 

[pbaldy]

In the query? Yes, just concatenate the two form references together, with a space between if desired.

 

[Jon123]

Could you explain a bit more?

 

[pbaldy]

[Forms]![Frm-updating damaged Qtz]![one] & " " & [Forms]![Frm-updating damaged Qtz]![Two]

 

[Jon123]

YeS that works AWESOME thank you. Last question I promise. When I do the Update can I add this to the already (if there is already a comment there) rather than over writing the comment that is there? Just separate them with a comma?

jon

 

[pbaldy]

Concatenate the field name and comma with the form references.