Update query with multiple select queries nested inside

Question

I have an Access DB that I am connecting to in Excel with VBA *

I have an update query (in vba) where a user can go to an order that was placed, and update the route that the order must go on. It has multiple criteria.

Each criteria has a foreign key that get it's value from an Int Id field, but the user selects the criteria from it's description that is a String.

This means that I first have to get all the ID's for the string criteria before I can do the update query due to the fact that the foreign key field takes the ID (Int) and not the description (String). But now I am trying to do it all in one pass, and make the update query with the select queries nested inside of it.

The following query does not work and gives an error Operation must use an update-able query

updateOrdersRoute = "UPDATE tbl_orders " & _
    "SET tbl_orders.route = (SELECT tbl_deliveryRoutes.ID FROM tbl_deliveryRoutes WHERE tbl_deliveryRoutes.routes = '" & routeNameNew & "') " & _
    "WHERE ([tbl_orders.route] = (SELECT [tbl_deliveryRoutes.ID] FROM [tbl_deliveryRoutes] WHERE [tbl_deliveryRoutes.routes] = '" & routeNameOld & "') " & _
    "AND [tbl_orders.weekNo] = " & weekNo & " " & _
    "AND [tbl_orders.loadingDay] = (SELECT [tbl_weekday.ID] FROM [tbl_weekday] WHERE [tbl_weekday.weekDay] = '" & weekday & "') " & _
    "AND [tbl_orders.CustomerName] = '" & shopName & "')"

But the following query does work: (Only the second line changed)

updateOrdersRoute = "UPDATE tbl_orders " & _
    "SET tbl_orders.route = " & routeNumberNew & " " & _
    "WHERE ([tbl_orders.route] = (SELECT [tbl_deliveryRoutes.ID] FROM [tbl_deliveryRoutes] WHERE [tbl_deliveryRoutes.routes] = '" & routeNameOld & "') " & _
    "AND [tbl_orders.weekNo] = " & weekNo & " " & _
    "AND [tbl_orders.loadingDay] = (SELECT [tbl_weekday.ID] FROM [tbl_weekday] WHERE [tbl_weekday.weekDay] = '" & weekday & "') " & _
    "AND [tbl_orders.CustomerName] = '" & shopName & "')"

But then I have to get the route ID number first with an extra query, so the problem seems to be in the beginning of the query, but I can not figure out where - or if there is even any way that it will work. I did try this and other solutions but they do not seem to come near what I am trying to do.

Tag your question with the database you are using. – Gordon Linoff May 11 '18 at 14:44 — Gordon Linoff, May 11 '18 at 14:44

score 0 · Answer 1 · answered May 11 '18 at 15:45

0

Try this for the line that causes you the problem:

SET tbl_orders.route = Nz(DLookup(""ID"",""tbl_deliveryRoutes"",""routes='"" & routeNameNew & ""'""))

These are double "double quotes" so they will translate as regular double quotes inside the string, with single quotes immediately after = and in the middle of the two sets of double "double quotes" at the end.

answered May 11 '18 at 15:45

SunKnight0

3,331
1
10
8

Hi, thank you for you help, but after checking out Nz and DLookup I realize now that I should have said that I AM using an Access DB, but I am doing all my code in Excel through VBA and then hooking up to the DB, so the Nz and DLookup did not work. Soz. – Alfa Bravo May 11 '18 at 19:43

Update query with multiple select queries nested inside

1 Answers1