Update yes/no field with VBA on condition

Danick

Registered User.
Local time
Today, 12:01
Joined
Sep 23, 2008
Messages
375
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

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

Code:
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?
 
I'm not sure I'm clear on the goal, but you can execute more than one statement:

Code:
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
 
I'm not sure I'm clear on the goal, but you can execute more than one statement:

Code:
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.
Code:
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...
 
Happy to help! Yes, the second "strSQL =..." overwrites the first, so will be the only one executed.
 

Users who are viewing this thread

Back
Top Bottom