locked
Change False to True data type (vice versa) with Buttons RRS feed

  • Question

  • HI,

    i currently have a table with multiple variables which includes a Yes/No data type. I would like to click on a button and it would change a True to False or False to True.  This are my current codes and i've been trying to use SQL statements.

    Private Sub Command14_Click()

    int_id = Me.ID   

    'MsgBox (Me.ID) 

    Dim db As DAO.Database   

    Dim rs As Recordset 

      Dim i As Long   

    Dim strSQL As String 

      Set db = CurrentDb iRet = MsgBox("Would you like to deactivate " & Me.NAMER & "?", vbYesNo) 

    If iRet = vbNo Then   

      Exit Sub           

    Else       


    strSQL = "UPDATE tbl_People SET STATUS = True" 

    Exit Sub   

    End If   

      Me.Refresh

    End Sub


    Tuesday, May 22, 2018 6:58 AM

Answers

  • I would like to click on a button and it would change a True to False or False to True.
    As a Boolean (Yes/No) column can only have values of TRUE or FALSE you can simply set the value to the converse of its current value:

        Dim strSQL As String
        Dim strMessageText As String

       strMessageText = "Would you like to deactivate " & Me.NAMER & "?"
       strSQL = "UPDATE tbl_People SET status = NOT status WHERE id = " & Me.id
        
        If MsgBox(strMessageText , vbYesNo+vbQuestion, "Confirm") = vbYes Then
            Me.Dirty = False
            CurrentDb.Execute strSQL, dbFailOnError
        End If

    Ken Sheridan, Stafford, England

    • Marked as answer by syahid noor Wednesday, May 23, 2018 4:53 AM
    Tuesday, May 22, 2018 10:52 AM

All replies

  • Hi syahid,

    >> it would change a True to False or False to True

    For this, you could try query below:

    UPDATE tblUser
    SET Status =
        Switch(
            Status = true, false,
            Status = false, true
            )
    
    where Id = 1

    In addition, do you have any issue with thread below?

    How to integrate excel spreadsheet with access database

    progress tracking for new employees

    Progress tracker on microsoft access

    Auto refresh new data insert

    If not, I would suggest you mark the helpful reply as answer to close them. If there is, please feel free to keep following.

    Best Regards,

    Tao Zhou



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, May 22, 2018 8:51 AM
  • I would like to click on a button and it would change a True to False or False to True.
    As a Boolean (Yes/No) column can only have values of TRUE or FALSE you can simply set the value to the converse of its current value:

        Dim strSQL As String
        Dim strMessageText As String

       strMessageText = "Would you like to deactivate " & Me.NAMER & "?"
       strSQL = "UPDATE tbl_People SET status = NOT status WHERE id = " & Me.id
        
        If MsgBox(strMessageText , vbYesNo+vbQuestion, "Confirm") = vbYes Then
            Me.Dirty = False
            CurrentDb.Execute strSQL, dbFailOnError
        End If

    Ken Sheridan, Stafford, England

    • Marked as answer by syahid noor Wednesday, May 23, 2018 4:53 AM
    Tuesday, May 22, 2018 10:52 AM
  • I never knew you could just use NOT in this manner.  That's a really clean and simple solution Ken.  Thank you for sharing.

    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Tuesday, May 22, 2018 3:30 PM
  • Even we old dogs can still learn the occasional new trick, Daniel.  I'm glad to have given back a little bit in return for everything I've learned from the real professionals here like you.

    Ken Sheridan, Stafford, England

    Tuesday, May 22, 2018 4:13 PM
  • Thanks Ken, appreciate it
    Wednesday, May 23, 2018 4:53 AM