Shawn
asked on
update query problem with sql server be
I'm trying to run a query with a liked table in sql server 2005 and the -1 doesn't seem to be working. OptionSelected is a bit data type. A similar query with a table within access works.
Any ideas?
Dim strSQL As String
strSQL = "Update tbltrad_commandes_FPdetail s set OptionSelected = 0 " & _
"where tbltrad_commandes_FPdetail s.OptionSe lected <>" & Me.OptionSelected
DoCmd.RunSQL strSQL
Any ideas?
Dim strSQL As String
strSQL = "Update tbltrad_commandes_FPdetail
"where tbltrad_commandes_FPdetail
DoCmd.RunSQL strSQL
In SQl Server bit accepts 1 for true and 0 for false values.
in sql table, bit data type have values 1 for true and 0 for false
try
strSQL = "Update tbltrad_commandes_FPdetail s set OptionSelected = 0 " & _
"where tbltrad_commandes_FPdetail s.OptionSe lected <>" & (Me.OptionSelected) * -1
try
strSQL = "Update tbltrad_commandes_FPdetail
"where tbltrad_commandes_FPdetail
Try:
& Abs(Me.OptionSelected)
& Abs(Me.OptionSelected)
ASKER
sorry, neither of those worked.
when I hard code it to 0 it works as long as I am cheching the box rather than unchecking.
strSQL = "Update tbltrad_commandes_FPdetail s set OptionSelected = 0 " & _
"where tbltrad_commandes_FPdetail s.OptionSe lected <> 0"
When I uncheck it I first get a write confilct asking if I want to copy to clipboard or drop changes. Then the error says it can't find the field ' '. Very strange.
when I hard code it to 0 it works as long as I am cheching the box rather than unchecking.
strSQL = "Update tbltrad_commandes_FPdetail
"where tbltrad_commandes_FPdetail
When I uncheck it I first get a write confilct asking if I want to copy to clipboard or drop changes. Then the error says it can't find the field ' '. Very strange.
Try to upload a sample access database with just objects to show the issue.
Type the script to create the sql table.
Type the script to create the sql table.
ASKER
Here is a sample DB and a script to create 2 tables in sql server. I am getting the same error with the sample db.
testBITmdb.zip
testBITmdb.zip
Check your SQL Server table's design and ensure that your OptionSelected field does not allow nulls, and that it has a default value set to False (0) .
If you have any existing NULLs in this field, update them to false (0).
Once you do that, try your original code again:
If you have any existing NULLs in this field, update them to false (0).
Once you do that, try your original code again:
strSQL = "Update tbltrad_commandes_FPdetails set OptionSelected = 0 " & _
"where tbltrad_commandes_FPdetails.OptionSelected <>" & Me.OptionSelected
Also, what exactly do you want to see when the user chooses "OptionSelected"?
The way you currently have your code written, it will do nothing at all... the syntax you have will look for all cases where OptionSelected is false and update it to false (so there will be no change to your data at all if the user sets Me.OptionSelected to true).
Do you want to toggle this field according to the user selection? Make a backup and try this:
The way you currently have your code written, it will do nothing at all... the syntax you have will look for all cases where OptionSelected is false and update it to false (so there will be no change to your data at all if the user sets Me.OptionSelected to true).
Do you want to toggle this field according to the user selection? Make a backup and try this:
Dim strSQL As String
strSQL = "Update tbltrad_commandes_FPdetails set OptionSelected = " & Me.OptionSelected & _
" where tbltrad_commandes_FPdetails.OptionSelected <>" & Me.OptionSelected
DoCmd.RunSQL strSQL
ASKER
>>Check your SQL Server table's design and ensure that your OptionSelected field does not allow nulls, and that it has a default value set to False (0) .
this is the case. no nulls allowed and default value set to false
>>Also, what exactly do you want to see when the user chooses "OptionSelected"?
I want only that selection to be checked all others should be unchecked.
tried your code and same error
this is the case. no nulls allowed and default value set to false
>>Also, what exactly do you want to see when the user chooses "OptionSelected"?
I want only that selection to be checked all others should be unchecked.
tried your code and same error
Your write conflict error is occurring because your update code is trying to change the record at the same time that the user is updating it through the form. You need to *exclude* the current record from your update.
Also from your notes in the form, my understanding is that your goal is simply to allow one selection at a time (unchecking previous selections).
Try this:
Also from your notes in the form, my understanding is that your goal is simply to allow one selection at a time (unchecking previous selections).
Try this:
Private Sub OptionSelected_AfterUpdate()
DoCmd.SetWarnings False
Dim strSQL As String
strSQL = "Update dbo_TMPtbltrad_commandes_FPdetails set OptionSelected = 0 " & _
"where dbo_TMPtbltrad_commandes_FPdetails.OptionSelected = true AND trad_commandes_FPdetailsID <> " & Me.trad_commandes_FPdetailsID
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Requery
End Sub
ASKER
there are no longer write conflicts but it doesn't uncheck the other records
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Or if you are only allowing the current item to be checked:
strSQL = "Update dbo_TMPtbltrad_commandes_FPdetails set OptionSelected = 0 " & _
"where trad_commandes_FPdetailsID <> " & Me.trad_commandes_FPdetailsID
ASKER
the first one here worked. thanks for persevering!