Update yes/no field with VBA on condition

[Danick]

I have a table called tblCompany with 2 Yes/No fields.
The first one Called [CompayNotes] is used to determine if the company requires summary notes
The second is a temporary holder called [ChkToDo]

I have a command button on a form to clear the contents of the [ChkToDo] field using an update VBA

Dim strSQL As String
strSQL = "UPDATE tblCompany SET ChkToDo = 0;"
CurrentDb.Execute strSQL, dbFailOnErrors
Me.Form.Requery

Works very well. But I’m having trouble getting a second command button working to Set the [ChkToDo] field where the [CompayNotes] field is set. I’m using this on the second cmd button.

Dim strSQL As String
strSQL = "UPDATE tblCompany SET ChkToDo = 1 where CompayNotes = -1;"
CurrentDb.Execute strSQL, dbFailOnErrors
Me.Form.Requery

This works fine if the user first uses the command button to clear the [ChkToDo] first. But if they don't press the first command button, then this VBA does not clear the [ChkToDo] where the [CompayNotes] field is not set. It sets the [ChkToDo] correctly, but also keeps any [ChkToDo] that the user set before hand.

Any ideas how to do this in one step?

 

[pbaldy]

I'm not sure I'm clear on the goal, but you can execute more than one statement:

Dim strSQL As String
strSQL = "UPDATE tblCompany SET ChkToDo = 0;"
CurrentDb.Execute strSQL, dbFailOnErrors

strSQL = "UPDATE tblCompany SET ChkToDo = 1 where CompayNotes = -1;"
CurrentDb.Execute strSQL, dbFailOnErrors

Me.Form.Requery

 

[Danick]

I'm not sure I'm clear on the goal, but you can execute more than one statement:

Dim strSQL As String
strSQL = "UPDATE tblCompany SET ChkToDo = 0;"
CurrentDb.Execute strSQL, dbFailOnErrors

strSQL = "UPDATE tblCompany SET ChkToDo = 1 where CompayNotes = -1;"
CurrentDb.Execute strSQL, dbFailOnErrors

Me.Form.Requery

WOW - that was simple. I had tried doing that before but forgot to repeat the Execute between the two Updates. This is what I had which didn't work.

strSQL = "UPDATE tblCompany SET ChkToDo = 0;"
strSQL = "UPDATE tblCompany SET ChkToDo = 1 where CompayNotes = -1;"
CurrentDb.Execute strSQL, dbFailOnErrors
Me.Form.Requery

Thanks so much for your quick response...

 

[pbaldy]

Happy to help! Yes, the second "strSQL =..." overwrites the first, so will be the only one executed.