update query for continous form

[Guest]

Hello,

with the following code i am trying to uncheck a box on my continous form as
it's closing and this does not work. my recordsource is ProdHistory and in
that table the record that i need to disselect everytime the form closes is
Select. can anyone help?

Private Sub Form_Close()

DoCmd.SetWarnings False
If Me.Dirty Then Me.Dirty = False 'Save first.
strSQL = "UPDATE ProdHistory SET Select = false " & _
"WHERE Select = true;"
DoCmd.RunSQL strSQL
Me.requery
DoCmd.SetWarnings True

End Sub

 

[Marshall Barton]

with the following code i am trying to uncheck a box on my continous form as
it's closing and this does not work. my recordsource is ProdHistory and in
that table the record that i need to disselect everytime the form closes is
Select. can anyone help?

Private Sub Form_Close()

DoCmd.SetWarnings False
If Me.Dirty Then Me.Dirty = False 'Save first.
strSQL = "UPDATE ProdHistory SET Select = false " & _
"WHERE Select = true;"
DoCmd.RunSQL strSQL
Me.requery
DoCmd.SetWarnings True

End Sub

It would help you provided more details about whatever
happens when "this does not work". Lacking those details, I
can make a couple observations.

First Select is an obvious SQL reserved word and using it
like that in a query should cause trouble. Until you change
the field name in the table, try using a fully qualified
reference.

Second, requerying the form as it is closing is at best a
waste of time.

Third, using SetWarnings is not a good practice. Bett to
use the Execute method.

Putting all that together, I think your code should be more
like:

Private Sub Form_Close()
If Me.Dirty Then Me.Dirty = False 'Save first.
strSQL = "UPDATE ProdHistory SET ProdHistory.[Select] =
False " & _
"WHERE ProdHistory.[Select] = True"
CurrentDb.Execute strSQL, dbFailOnError
End Sub

Finally, I suggest that you reset the field when the form
opens because it guards against a system shutdown/crash
leaving the field in the wrong state.

 

[Guest]

Hey Marshall,

got it running, it seems like you said, Select is a restricted word.

thanks for your help

with the following code i am trying to uncheck a box on my continous form as
it's closing and this does not work. my recordsource is ProdHistory and in
that table the record that i need to disselect everytime the form closes is
Select. can anyone help?

Private Sub Form_Close()

DoCmd.SetWarnings False
If Me.Dirty Then Me.Dirty = False 'Save first.
strSQL = "UPDATE ProdHistory SET Select = false " & _
"WHERE Select = true;"
DoCmd.RunSQL strSQL
Me.requery
DoCmd.SetWarnings True

End Sub

It would help you provided more details about whatever
happens when "this does not work". Lacking those details, I
can make a couple observations.

First Select is an obvious SQL reserved word and using it
like that in a query should cause trouble. Until you change
the field name in the table, try using a fully qualified
reference.

Second, requerying the form as it is closing is at best a
waste of time.

Third, using SetWarnings is not a good practice. Bett to
use the Execute method.

Putting all that together, I think your code should be more
like:

Private Sub Form_Close()
If Me.Dirty Then Me.Dirty = False 'Save first.
strSQL = "UPDATE ProdHistory SET ProdHistory.[Select] =
False " & _
"WHERE ProdHistory.[Select] = True"
CurrentDb.Execute strSQL, dbFailOnError
End Sub

Finally, I suggest that you reset the field when the form
opens because it guards against a system shutdown/crash
leaving the field in the wrong state.