Update Query gives error

[proballin]

Does anyone know why I get the error "Operation must use an updateable query" when I run this query.

UPDATE Food_Table SET Food_Table.Delivery_Size = (SELECT Delivery_Size
FROM Delivery_Table
WHERE ((Delivery_Table.Region=Forms.Formulation_Combo.Region_Combo) AND (Delivery_Table.SubRegion=Forms.Formulation_Combo.SubRegion_Combo) AND (Delivery_Table.Segment=Forms.Formulation_Combo.Segment_Combo) AND (Delivery_Table.Sector=Forms.Formulation_Combo.Sector_Combo) AND (Delivery_Table.Product=Forms.Formulation_Combo.Product_Combo)));

When I run only the sub SELECT query portion all is well, but not in the update query.

 

[proballin]

Its based on a table, there are no linked tables in this particular database.

 

[gemma-the-husky]

all tables involved in an update query MUST have a primary key - is that the problem?

 

[proballin]

They do have primary keys...this does not fix the problem however.

 

[proballin]

To give a bit more info I want all the locations in the "Food_Table" that match my conditions in the WHERE statement to be updated to the new "Delivery_Size" when a user puts in a new delivery size in my form.

 

[GolfProRM]

Just want to make sure it's not a copy/paste error, but in your original post, there's a space where it looks like there shouldn't be one.

Formulation_Combo.Se gment_Combo) AND

May not actually be a problem, but want to make sure.

 

[proballin]

Nah...those were just pasting errors. I wish that was the issue though, lol...it would be easier to figure that out.

 

[GolfProRM]

Question: when you run the select query and view the results, does the query allow you to add new records or change the data shown in the query results? If not, your select query isn't an updateable query.

If that's the case, go to Access Help and search troubleshoot queries - under the select query type, it helps explain why a query might not be updateable.

 

[proballin]

Yep, I can change/add new records or data...any more possible solutions?

 

[Brianwarnock]

Since none of the experts have commented on it I am probably wrong, but i thought that if you where updating one table with data from another the tables had to be joined.

Brian

 

[proballin]

Joined you say? Can you elaborate a bit.

 

[GolfProRM]

I just realized what you don't have is a Where statement in the actual update query. You're telling it to update FoodTable.DeliverySize, but you don't tell it which account in FoodTable to update.

 

[Brianwarnock]

I just realized what you don't have is a Where statement in the actual update query. You're telling it to update FoodTable.DeliverySize, but you don't tell it which account in FoodTable to update.

The join would do that.

Brian

 

[proballin]

Can u give me an example of how to use the join in my statement Brian?

 

[Brianwarnock]

I will have to search throgh my memory as its a while since I did this but Access help on updating one table with data from another says

If the tables aren't already joined, join them on the fields that have related information

.


Brian

 

[Brianwarnock]

The Join would be after the UPDATe but then I've never done this with a sub query so perhaps I should bow out.

Brian

 

[GolfProRM]

I don't believe a Join would work based on the way the query is setup. Since the update value is a query, it doesn't contain a unique identifier to pass through to the FoodTable. You simply need a WHERE statement on the main update query to tell it which record to update.

Simplified Code

UPDATE FoodTable 
SET FoodTable.DeliveryType = (*SELECT QUERY CODE* )
WHERE FoodTable.UniqueID = Something;

 

[proballin]

Man...it still gives me that error even after adding the WHERE statement Ryan wrote of to address where to place the update.

 

[GolfProRM]

Any chance you could upload a copy of your DB with some sample data?