Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2011
    Posts
    21

    Unanswered: Clear All Checkboxes

    Hi! I have created a database for a music mixer. I have a checkbox that is linked to the table. Anyways I want to create a button that sets all the checkboxes for that item to null. I have tried using me.checkbox=null, but it only clears the selected record. How can I select all the records and set it to null using vba?

    Thanks!!!
    Last edited by Dargonian; 10-08-11 at 01:02.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Is it set all checkboxes for that item (record) to Null:
    Code:
    Dim ctl as Control
    For Each ctl In Me.Controls
        If ctl.ControlType = acCheckBox Then ctl.Value = Null
    Next ctl
    Or is it set the value of a field (= column in a table) to Null for all records (= rows in a table):
    Code:
    Dim strSQL As String
    strSQL = "UPDATE <TableName> SET <ColumnName> = Null;"
    CurrentDb.Execute strSQL, dbFailOnErrors
    Have a nice day!

  3. #3
    Join Date
    Oct 2011
    Posts
    21
    So I put in the second code you stated, it works, but it is only clearing one at a time. After I click the button, each record I click on it deletes it. is there a way to set it to select all records, or is it just going to do the one? I have attached a copy of the database. Take a look and see what you think it is. To get to where I have the button click MULTIPLE SELECTOR. Then the button will appear on the left. This is the button I am trying to use to click it. Look under vba code. What do you think I need to change.
    Attached Files Attached Files

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The second example clears the specified column of all rows directly into the table. From a form you can add the following line after it to immediately see the changes:
    Code:
    Me.Requery
    Have a nice day!

  5. #5
    Join Date
    Oct 2011
    Posts
    21
    Sweet it is working...there is onl one problem. It clears all but one. The code I have is:

    Private Sub cmdClear_Click()
    Dim strSQL As String

    Me.frmMultipleSelecter.SetFocus
    strSQL = "UPDATE tblMusic SET Selected = Null;"
    CurrentDb.Execute strSQL
    Me.Requery

    End Sub

    What do you think? By the way thanks for helping me.

  6. #6
    Join Date
    Oct 2011
    Posts
    21
    Never mind I figured it out. It just needed a me.requery at the beginning to to update before I do the clear selected. Thank you for your help.

  7. #7
    Join Date
    Oct 2011
    Posts
    21
    I have another question. I would like to write a code similar to the sequel code you gave me. To run a shell command for each record in a certain column. DO you know what sequel code to use for this?

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What exactly do you name a shell command? Do you intend to start another application with the contents of the column as a parameter, do you want to run a command from the command interpretor (cmd.exe), or is it something else? Please provide an example of what you intend to do.
    Have a nice day!

  9. #9
    Join Date
    Oct 2011
    Posts
    21
    Attached is a picture of what the form looks like. I have the shell portion of it working. But I want it to run teh shell for everything in the selected query. This is the shell code: Shell = "C:\Program Files (x86)\VideoLAN\VLC\vlc.exe " & Chr(34) & Me.fLocation & Chr(34). This works. Also, it opens a new vlc every time this command is ran. What I want is to have this run for every record in the Me.fLocation spot on the query.

    Does this help?

    Thank you soooo much for your help.
    Attached Thumbnails Attached Thumbnails Capture.PNG  

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can always open a Recordset on the query and loop through it:
    Code:
    Sub ShellLoop()
    
        Dim rst As DAO.Recordset
        Const c_strShell As String = "C:\Program Files (x86)\VideoLAN\VLC\vlc.exe '@F'"
        
        Set rst = CurrentDb.OpenRecordset("MyQuery", dbOpenSnapshot)
        With rst
            Do Until .EOF
                Shell Replace(Replace(c_strShell, "'", Chr(34)), "@F", rst!fLocation)
                DoEvents
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
        
    End Sub
    If you want to do the same but with the rowset of the form, you can use:
    Code:
    Sub ShellLoop()
    
        Dim rst As DAO.Recordset
        Const c_strShell As String = "C:\Program Files (x86)\VideoLAN\VLC\vlc.exe '@F'"
        
        Set rst = Me.RecordsetClone
        With rst
            Do Until .EOF
                Shell Replace(Replace(c_strShell, "'", Chr(34)), "@F", rst!fLocation)
                DoEvents
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
        
    End Sub
    In any case, omitting the DoEvents instruction risks to lock the computer if there are many files to open simultaneously (i.e. if there are many rows in the Recordset).
    Have a nice day!

  11. #11
    Join Date
    Oct 2011
    Posts
    21
    That worked awesome thank you sooo much!!

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  13. #13
    Join Date
    Oct 2011
    Posts
    21
    sindho i have a question. I think you would know how to fix this problem with sequel. I don't know sequel very well, but i think that is what this guy needs. Take a look at this.

    http://www.dbforums.com/microsoft-ac...-new-post.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •