Update Query Problem

[DS]

I Have an update query that is updateing one table called MenuInfo...the
problem is that part of the criteria is based on a second table
MenuDetails. The two tables are joined on MenuID...the problem is that
it's not increasing the ModGroupLevel1 field by 1.

Dim LevelUpSQL As String
DoCmd.SetWarnings False
LevelUpSQL = "UPDATE MenuDetails LEFT JOIN MenuInfo ON
MenuDetails.MenuID = MenuInfo.MenuID SET MenuInfo.ModGroupLevel1 =
MenuInfo.ModGroupLevel1 + 1 " & _
"WHERE (((MenuDetails.TerminalID)=[Forms]![MenuCreator]![TxtStation]) " & _
"AND ((MenuInfo.MenuID)=[Forms]![MenuCreator]![TxtMenu]) " & _
"AND ((MenuInfo.PriceID)=[Forms]![MenuCreator]![TxtPriceID]) " & _
"AND ((MenuInfo.MenuCatID)=[Forms]![MenuCreator]![TxtSection]) " & _
"AND ((MenuInfo.ItemID)=[Forms]![MenuCreator]![TxtItem]) " & _
"AND ((MenuInfo.ModGroup1)=[Forms]![MenuCreator]![TxtModGroup]));"
DoCmd.RunSQL (LevelUpSQL)
DoCmd.SetWarnings True
Me.ListGroups.Requery

 

[John Nurick]

Are you certain that the query is updatable? What does it tell you when
you don't SetWarnings False?

I Have an update query that is updateing one table called MenuInfo...the
problem is that part of the criteria is based on a second table
MenuDetails. The two tables are joined on MenuID...the problem is that
it's not increasing the ModGroupLevel1 field by 1.

Dim LevelUpSQL As String
DoCmd.SetWarnings False
LevelUpSQL = "UPDATE MenuDetails LEFT JOIN MenuInfo ON
MenuDetails.MenuID = MenuInfo.MenuID SET MenuInfo.ModGroupLevel1 =
MenuInfo.ModGroupLevel1 + 1 " & _
"WHERE (((MenuDetails.TerminalID)=[Forms]![MenuCreator]![TxtStation]) " & _
"AND ((MenuInfo.MenuID)=[Forms]![MenuCreator]![TxtMenu]) " & _
"AND ((MenuInfo.PriceID)=[Forms]![MenuCreator]![TxtPriceID]) " & _
"AND ((MenuInfo.MenuCatID)=[Forms]![MenuCreator]![TxtSection]) " & _
"AND ((MenuInfo.ItemID)=[Forms]![MenuCreator]![TxtItem]) " & _
"AND ((MenuInfo.ModGroup1)=[Forms]![MenuCreator]![TxtModGroup]));"
DoCmd.RunSQL (LevelUpSQL)
DoCmd.SetWarnings True
Me.ListGroups.Requery

 

[SteveS]

Also, if the query is updatable, why would you expect ModGroupLevel1 to
increment when it is inside a string?

Add "MsgBox LevelUpSQL" before the line "DoCmd.RunSQL (LevelUpSQL)". What is
displayed in the message box?

You should have something like:

LevelUpSQL = "UPDATE MenuDetails LEFT JOIN MenuInfo ON
MenuDetails.MenuID = MenuInfo.MenuID SET MenuInfo.ModGroupLevel1 = " &
MenuInfo.ModGroupLevel1 + 1 & _
--snip--


Note that

MenuInfo.ModGroupLevel1 + 1

is outside of the double quotes.


HTH
--
Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)

Are you certain that the query is updatable? What does it tell you when
you don't SetWarnings False?

I Have an update query that is updateing one table called MenuInfo...the
problem is that part of the criteria is based on a second table
MenuDetails. The two tables are joined on MenuID...the problem is that
it's not increasing the ModGroupLevel1 field by 1.

Dim LevelUpSQL As String
DoCmd.SetWarnings False
LevelUpSQL = "UPDATE MenuDetails LEFT JOIN MenuInfo ON
MenuDetails.MenuID = MenuInfo.MenuID SET MenuInfo.ModGroupLevel1 =
MenuInfo.ModGroupLevel1 + 1 " & _
"WHERE (((MenuDetails.TerminalID)=[Forms]![MenuCreator]![TxtStation]) " & _
"AND ((MenuInfo.MenuID)=[Forms]![MenuCreator]![TxtMenu]) " & _
"AND ((MenuInfo.PriceID)=[Forms]![MenuCreator]![TxtPriceID]) " & _
"AND ((MenuInfo.MenuCatID)=[Forms]![MenuCreator]![TxtSection]) " & _
"AND ((MenuInfo.ItemID)=[Forms]![MenuCreator]![TxtItem]) " & _
"AND ((MenuInfo.ModGroup1)=[Forms]![MenuCreator]![TxtModGroup]));"
DoCmd.RunSQL (LevelUpSQL)
DoCmd.SetWarnings True
Me.ListGroups.Requery