UPDATE SQL 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 MenuInfo 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 Vinson]

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 MenuInfo 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

Your query is joining MenuInfo to MenuInfo and not including the
MenuDetails table - shouldn't the start of the query be

LevelUpSQL = "UPDATE MenuInfo LEFT JOIN MenuDetails ON
MenuDetails.MenuID = MenuInfo.MenuID...


John W. Vinson[MVP]