SQL UPDATE Problem

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 = ""
 
D

DS

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 = ""
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
 
D

Douglas J Steele

DS said:
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.
 
D

DS

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

UPDATE SQL Problem 1
Update Query Problem 2
SQL UPDATE on the fly 5
Syntax error in Update statement 3
Select From 1
Write to other DB 7
SQL SubQuery 10
SQL Date/Time Update 2

Top