SQL UPDATE Problem

  • Thread starter Thread starter DS
  • Start date Start date
D

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 said:
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 = ""
Click to expand...
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
 
DS said:
Does SQL not run One statement after another. If so how can I fix this.
Click to expand...

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.
 
Douglas said:
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.
Click to expand...
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
 
Back
Top