Resetting Yes/No value

[Macros]

I'm relatively new to VBA coding, but in the project I'm working on, I've got a Yes/No option to make certain people active and others not. I can't seem to get the reset to the No value to work and this is something the user needs to be able to do. Here's the code:

Private Sub Inactive_Click()
On Error GoTo Err_Inactive_Click
Dim MsgResult As VbMsgBoxResult
Dim MySQL As String
 
     MsgResult = MsgBox("Are you sure you want to make ALL Candidates Inactive?", vbYesNo, "Make Candidates Inactive?")
If (MsgResult = vbYes) Then
 
MySQL = "UPDATE CandidateInformation SET CandidateInformation.ActiveCandidate = False WHERE (((CandidateInformation.ActiveCandidate)= True))"
 
DoCmd.RunSQL MySQL
 
End If
 
Exit_Inactive_Click:
    Exit Sub
 
Err_Inactive_Click:
    MsgBox Err.Description
    Resume Exit_Inactive_Click
 
End Sub
 
 

Thanks for any help/feedback!

[MSeda]

the yes/no message box actually returns the integer 6 for yes and 7 for no.
Change the vbYes in your condition to 6 and your code should work.

when in doubt insert a message box that displays the variable after setting the value of a variable just to make sure what you think should happen is really happening.

[MMcCarthy]

the yes/no message box actually returns the integer 6 for yes and 7 for no.
Change the vbYes in your condition to 6 and your code should work.

when in doubt insert a message box that displays the variable after setting the value of a variable just to make sure what you think should happen is really happening.

As a follow on to this change the variable to an integer.

 
Dim MsgResult As Integer

[Macros]

Thanks to you both for the fixes. I'm running into another problem now where it's asking me to enter a parameter value and no matter what I try, I get a message of 'operation must use an updateable query. Now I do have this pointed at a table, but I've also tried it using a query with that field and it still gives me the same messages. (Using Access 2000)

Here's the code with the changes:

Private Sub Inactive_Click()
On Error GoTo Err_Inactive_Click
Dim MsgResult As Integer
Dim MySQL As String
 
     MsgResult = MsgBox("Are you sure you want to make ALL Candidates Inactive?", vbYesNo, "Make Candidates Inactive?")
If (MsgResult = 6) Then
 
MySQL = "UPDATE CandidateInformation SET CandidateInformation.Active_Candidate = False WHERE (((CandidateInformation.Active_Candidate)= True))"
 
DoCmd.RunSQL MySQL
 
End If
 
Exit_Inactive_Click:
    Exit Sub
 
Err_Inactive_Click:
    MsgBox Err.Description
    Resume Exit_Inactive_Click
 
End Sub
 

[MMcCarthy]

Thanks to you both for the fixes. I'm running into another problem now where it's asking me to enter a parameter value and no matter what I try, I get a message of 'operation must use an updateable query. Now I do have this pointed at a table, but I've also tried it using a query with that field and it still gives me the same messages. (Using Access 2000)

Here's the code with the changes:

Private Sub Inactive_Click()
On Error GoTo Err_Inactive_Click
Dim MsgResult As Integer
Dim MySQL As String
 
     MsgResult = MsgBox("Are you sure you want to make ALL Candidates Inactive?", vbYesNo, "Make Candidates Inactive?")
If (MsgResult = 6) Then
 
MySQL = "UPDATE CandidateInformation SET CandidateInformation.Active_Candidate = False WHERE (((CandidateInformation.Active_Candidate)= True))"
 
DoCmd.RunSQL MySQL
 
End If
 
Exit_Inactive_Click:
    Exit Sub
 
Err_Inactive_Click:
    MsgBox Err.Description
    Resume Exit_Inactive_Click
 
End Sub
 

Since you are setting all CandidateInformation.Active_Candidate to False you don't need the where statement so delete it.

Mary

[NeoPa]

I can't see anything wrong with your code or your SQL (Since the variable MsgResult was converted to Integer - the only bug I could see).
I did notice though, that while you made no comment about it, you changed the name of the field between posts. Make sure whatever you use reflects the absolutely accurate name of the field.
I suggest you change your MySQL line to :

MySQL = "UPDATE CandidateInformation " & _
        "SET Active_Candidate=False " & _
        "WHERE Active_Candidate=True"

All the rest is extraneous cr*p provided gratuitously by MS Access.
I would keep the WHERE clause, as it will execute more efficiently that way.
vbYes is also a more readable version than 6. If it had solved your problem it may have been worth keeping but in the circumstances...

[Macros]

Thanks for the posts. I ended up seeing my mistake after walking away for a day. I had the field name set in the Table wrong. So fixed that and it worked like a charm! Thanks for all the help!

[NeoPa]

Glad to hear you got it resolved. I wondered if it may come back to that somehow.