Update query with inner join?

[Guest]

I have an update query to change supplier's name and date of delivery in the
table. Problem is - it's changing all of the records, not the specific ones.
Table is invoices with job #, po #, description, qty, name of supplier. The
best I can do right now is to have a select query bring up the right records,
but it also involves inputting the job # and po # again. Trying to get away
from that. Thanks!

 

[Ken Snell [MVP]]

Post the SQL statement of the query that is not doing what you want. Tell us
what it should be doing.

 

[Guest]

UPDATE PurchaseOrder SET PurchaseOrder.Name =
[Forms]![QuoteAcceptForm1]![Name], PurchaseOrder.PO = Yes, PurchaseOrder.QR =
No, PurchaseOrder.NeedMaterialBy =
[Forms]![QuoteAcceptForm1]![NeedMaterialBy];

I would like for it to only update certain records i.e. Job# 5100,
PurchaseOrder#01. It is currently updating all records.

 

[John Vinson]

UPDATE PurchaseOrder SET PurchaseOrder.Name =
[Forms]![QuoteAcceptForm1]![Name], PurchaseOrder.PO = Yes, PurchaseOrder.QR =
No, PurchaseOrder.NeedMaterialBy =
[Forms]![QuoteAcceptForm1]![NeedMaterialBy];

I would like for it to only update certain records i.e. Job# 5100,
PurchaseOrder#01. It is currently updating all records.

You need a WHERE clause, or (equivalently) something on the Criteria
line of the query to tell Access which records you want to update.
Assuming that the Form has textboxes JobNumber and PONumber, perhaps
something like

UPDATE PurchaseOrder SET PurchaseOrder.[Name] =
[Forms]![QuoteAcceptForm1]![Name], PurchaseOrder.PO = Yes,
PurchaseOrder.QR = No, PurchaseOrder.NeedMaterialBy =
[Forms]![QuoteAcceptForm1]![NeedMaterialBy]
WHERE [JobNumber] = [Forms]![QuoteAcceptForm1]![JobNumber]
AND [PONumber] = [Forms]![QuoteAcceptForm1]![PONumber];

Do note that Name is a reserved word, and as such should not be used
as a field or control name. You may want to change this field!

John W. Vinson[MVP]

 

[Guest]

Thank you VERY much for the help. Any idea how to not show the "Write
message" that comes up following the update query?

UPDATE PurchaseOrder SET PurchaseOrder.Name =
[Forms]![QuoteAcceptForm1]![Name], PurchaseOrder.PO = Yes, PurchaseOrder.QR =
No, PurchaseOrder.NeedMaterialBy =
[Forms]![QuoteAcceptForm1]![NeedMaterialBy];

I would like for it to only update certain records i.e. Job# 5100,
PurchaseOrder#01. It is currently updating all records.

You need a WHERE clause, or (equivalently) something on the Criteria
line of the query to tell Access which records you want to update.
Assuming that the Form has textboxes JobNumber and PONumber, perhaps
something like

UPDATE PurchaseOrder SET PurchaseOrder.[Name] =
[Forms]![QuoteAcceptForm1]![Name], PurchaseOrder.PO = Yes,
PurchaseOrder.QR = No, PurchaseOrder.NeedMaterialBy =
[Forms]![QuoteAcceptForm1]![NeedMaterialBy]
WHERE [JobNumber] = [Forms]![QuoteAcceptForm1]![JobNumber]
AND [PONumber] = [Forms]![QuoteAcceptForm1]![PONumber];

Do note that Name is a reserved word, and as such should not be used
as a field or control name. You may want to change this field!

John W. Vinson[MVP]

 

[Guest]

I'm sorry - I meant the "Write conflict" message that pops up.

Thank you VERY much for the help. Any idea how to not show the "Write
message" that comes up following the update query?

UPDATE PurchaseOrder SET PurchaseOrder.Name =
[Forms]![QuoteAcceptForm1]![Name], PurchaseOrder.PO = Yes, PurchaseOrder.QR =
No, PurchaseOrder.NeedMaterialBy =
[Forms]![QuoteAcceptForm1]![NeedMaterialBy];

I would like for it to only update certain records i.e. Job# 5100,
PurchaseOrder#01. It is currently updating all records.

You need a WHERE clause, or (equivalently) something on the Criteria
line of the query to tell Access which records you want to update.
Assuming that the Form has textboxes JobNumber and PONumber, perhaps
something like

UPDATE PurchaseOrder SET PurchaseOrder.[Name] =
[Forms]![QuoteAcceptForm1]![Name], PurchaseOrder.PO = Yes,
PurchaseOrder.QR = No, PurchaseOrder.NeedMaterialBy =
[Forms]![QuoteAcceptForm1]![NeedMaterialBy]
WHERE [JobNumber] = [Forms]![QuoteAcceptForm1]![JobNumber]
AND [PONumber] = [Forms]![QuoteAcceptForm1]![PONumber];

Do note that Name is a reserved word, and as such should not be used
as a field or control name. You may want to change this field!

John W. Vinson[MVP]

 

[John Vinson]

I'm sorry - I meant the "Write conflict" message that pops up.

That message suggests that you're trying to run the update query on a
record which you currently have open for editing, perhaps on a Form.
Make sure that you're not viewing or editing any of the records which
might be getting updated.

John W. Vinson[MVP]

 

[Guest]

That's what I'm meaning to do. Wanting to update all of SupplierNames for all
of the records in the form that I am viewing. Any ideas how I could do it
differently? I need to update other items in each record, too (PriceEach,
DeliveryDate, etc.).

 

[John Vinson]

That's what I'm meaning to do. Wanting to update all of SupplierNames for all
of the records in the form that I am viewing. Any ideas how I could do it
differently? I need to update other items in each record, too (PriceEach,
DeliveryDate, etc.).

Umm... if you're updating *just the one record* that's being
displayed, you don't need any query or any code at all; just use a
bound form and type!

I guess I'm not visualizing this correctly. Do you have multiple table
items being displayed on a Form, and they all have the same
SupplierName, and they all need a different SupplierName? Are all of
the records being updated to the same delivery date?


John W. Vinson[MVP]

 

[Guest]

Yes - multiple table items displayed. The information first comes from a
QuoteRequest form which is saved into the PurchaseOrder table as a Quote
Request. Used can query for, say, Quote Request #4126-01. User then sees all
records in that Quote Request. Another form is used to change the Quote
Request to a Purchase Order. When user changes SupplierName in record #1, all
the rest of the records displayed on that form change, also. Still have to
change Quantity, DateDelivered, etc. before the whole thing is saved.
Sometimes DateDelivered is different for each item ordered. Couldn't figure
out a better way to do this. Any ideas?
Thanks!