SQL UPDATE Problem

[DS]

I have this code. The problem is that I can go from "F" to "U"
But I can't go from "U" to"F"
Any help appreciated.
Thnaks
DS

Dim TypeUSQL As String
DoCmd.SetWarnings False
TypeUSQL = "UPDATE MenuInfo SET MenuInfo.ModAction1 = ""F"" " & _
"WHERE MenuInfo.TerminalID = Forms!MenuCreator2!TxtStation " & _
"AND MenuInfo.MenuID = Forms!MenuCreator2!TxtMenu " & _
"AND MenuInfo.MenuCatID = Forms!MenuCreator2!TxtSection " & _
"AND MenuInfo.ItemID = Forms!MenuCreator2!TxtItem " & _
"AND MenuInfo.ModGroup1 = Forms!MenuCreator2!TxtModGroup " & _
"AND Menuinfo.ModAction1 = ""U"";"
DoCmd.RunSQL (TypeUSQL)
DoCmd.SetWarnings True
Me.ListGroups.Requery
Me.ListGroups = ""
Me.ListMods.RowSource = ""
Me.ListMods = ""

Dim TypeFSQL As String
DoCmd.SetWarnings False
TypeFSQL = "UPDATE MenuInfo SET MenuInfo.ModAction1 = ""U"" " & _
"WHERE MenuInfo.TerminalID = Forms!MenuCreator2!TxtStation " & _
"AND MenuInfo.MenuID = Forms!MenuCreator2!TxtMenu " & _
"AND MenuInfo.MenuCatID = Forms!MenuCreator2!TxtSection " & _
"AND MenuInfo.ItemID = Forms!MenuCreator2!TxtItem " & _
"AND MenuInfo.ModGroup1 = Forms!MenuCreator2!TxtModGroup " & _
"AND Menuinfo.ModAction1 = ""F"";"
DoCmd.RunSQL (TypeFSQL)
DoCmd.SetWarnings True
Me.ListGroups.Requery
Me.ListGroups = ""
Me.ListMods.RowSource = ""
Me.ListMods = ""

 

[DS]

I have this code. The problem is that I can go from "F" to "U"
But I can't go from "U" to"F"
Any help appreciated.
Thnaks
DS

Dim TypeUSQL As String
DoCmd.SetWarnings False
TypeUSQL = "UPDATE MenuInfo SET MenuInfo.ModAction1 = ""F"" " & _
"WHERE MenuInfo.TerminalID = Forms!MenuCreator2!TxtStation " & _
"AND MenuInfo.MenuID = Forms!MenuCreator2!TxtMenu " & _
"AND MenuInfo.MenuCatID = Forms!MenuCreator2!TxtSection " & _
"AND MenuInfo.ItemID = Forms!MenuCreator2!TxtItem " & _
"AND MenuInfo.ModGroup1 = Forms!MenuCreator2!TxtModGroup " & _
"AND Menuinfo.ModAction1 = ""U"";"
DoCmd.RunSQL (TypeUSQL)
DoCmd.SetWarnings True
Me.ListGroups.Requery
Me.ListGroups = ""
Me.ListMods.RowSource = ""
Me.ListMods = ""

Dim TypeFSQL As String
DoCmd.SetWarnings False
TypeFSQL = "UPDATE MenuInfo SET MenuInfo.ModAction1 = ""U"" " & _
"WHERE MenuInfo.TerminalID = Forms!MenuCreator2!TxtStation " & _
"AND MenuInfo.MenuID = Forms!MenuCreator2!TxtMenu " & _
"AND MenuInfo.MenuCatID = Forms!MenuCreator2!TxtSection " & _
"AND MenuInfo.ItemID = Forms!MenuCreator2!TxtItem " & _
"AND MenuInfo.ModGroup1 = Forms!MenuCreator2!TxtModGroup " & _
"AND Menuinfo.ModAction1 = ""F"";"
DoCmd.RunSQL (TypeFSQL)
DoCmd.SetWarnings True
Me.ListGroups.Requery
Me.ListGroups = ""
Me.ListMods.RowSource = ""
Me.ListMods = ""

It doesn't seem to be the code. If I switch them I can go from "U" to
"F" but not "F" to "U".

Does SQL not run One statement after another. If so how can I fix this.
Thanks
DS

 

[Stefan Hoffmann]

hi,
Use CurrentDb.Execute instead of DoCmd.RunSQL.


mfG
--> stefan <--

 

[Douglas J Steele]

Does SQL not run One statement after another. If so how can I fix this.

The problem is that they DO run one after the other. Your first statement is
setting all of the Fs to U. That means that everything that used to be an F
will be a U afterwards (plus whatever else was a U), and they'll all be
changed to F by the second run.

Try changing all the Fs to Xs (or some other value that doesn't really
exist), then changing all the Us to Fs, then change all the Xs to Us.

 

[DS]

hi,


Use CurrentDb.Execute instead of DoCmd.RunSQL.


mfG
--> stefan <--

Thanks, I'll try it.
DS

 

[DS]

The problem is that they DO run one after the other. Your first statement is
setting all of the Fs to U. That means that everything that used to be an F
will be a U afterwards (plus whatever else was a U), and they'll all be
changed to F by the second run.

Try changing all the Fs to Xs (or some other value that doesn't really
exist), then changing all the Us to Fs, then change all the Xs to Us.

Your right, I see the error of my ways. I fixed it by recoding and
adding a Textbox which grabs the value from the listbox then runibg the
appropiate one from an if statement.
Thanks
DS