Results 1 to 14 of 14
  1. #1
    Chuck55 is offline Novice
    Windows XP Access 2000
    Join Date
    Apr 2012
    Posts
    25

    clear checkbox and list box using TypeOf......Is

    What is the VBA code to uncheck a checkbox and deselect a listbox using TypeOf......Is?

    I have an unbound form (Access 2000) with comboboxes, textboxes, checkboxes and listboxes. None of the controls have a Row Source. Each combobox and listbox is supplied by their own table or value list.

    Controls on the unbound form are selected to determine the needed output. Once the needed controls have been selected, another command button is clicked to fire more code to select the records that match the selected controls.

    Before a new output is selected, the 4 control types need to be cleared. The comboboxes and textboxes do clear with the below VBA, which fires when a command button is clicked. A checkbox or listbox returns run time error 2448 'You can't assign a value to this object'

    Private Sub cmdClearCtls_Click()
    Dim ctl As Control
    For Each ctl In Me.Controls


    If TypeOf ctl Is ComboBox Or TypeOf ctl Is TextBox Then
    ctl.SetFocus
    ctl.Text = ""
    End If
    ' If ctl.ControlType = acCheckBox Then
    ' ctl = False
    ' End If
    ' If TypeOf ctl Is ListBox Then
    ' ctl.SetFocus
    ' ctl = ""
    ' End If
    ' If TypeOf ctl Is CheckBox Then
    ' ctl.SetFocus
    ' ctl = False
    ' End If
    Next

    End Sub

  2. #2
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    I have this in a test DB and it works [Access 2010 - but it should work for you too - I think?].
    Code:
            Me.Check11.SetFocus
            Me.Check11.Value = True
    Let me know if this helps! All the best.

  3. #3
    Chuck55 is offline Novice
    Windows XP Access 2000
    Join Date
    Apr 2012
    Posts
    25
    Hello Robeen - thanks for the suggestion but it does not solve the problem when using 'TypeOf...Is'. The form has 30 check boxes (and 7 listboxes) and using me.check11.set focus (hardcoding the control name) will require 37 different statements. Using 'TypeOf.....Is' precludes having to specify the control name. 'For Each' with 'Next' creates a looping condition that will check every control on the form. The form has 20 comboboxes and 4 text boxes that do clear with the above code.

    I can write hardcode clear statements for checkboxes and listboxes but I would like to avoid such coding.

  4. #4
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    I was thinking you could use the code you already had in place but with a small change:
    Code:
    If TypeOf ctl Is CheckBox Then
    ctl.SetFocus
    ctl.Value = False
    ?

  5. #5
    Chuck55 is offline Novice
    Windows XP Access 2000
    Join Date
    Apr 2012
    Posts
    25
    Using ctl.Value=False still results in run time error 2448 'You can't assign a value to this object'.

    If the control default value is set to "", is there VBA code that update the controls to the default value?

  6. #6
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    I'm still trying to find out how to get the name of the current control so that you can then refer to the control and set its value.
    Perhaps you - or someone else reading this - will know how to do that.
    If not, I'll post the solution as soon as I can find it.

  7. #7
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Not sure if this will work for you, but at least I was able to get the checkboxes on my Form to be unchecked.

    When I do If TypeOf ctl is acCheckBox . . . I get an error just like you.

    Your post says you specifically want to use the TypeOf keyword for the check boxes but I have to ask - does it HAVE to be TypeOf?
    If so . . . sorry I don't have an answer.
    I'm interested in finding out why, though.
    Let me know if it HAS to be TypeOf & I'll ask someone on the Forum to take a look for you.

    Meanwhile, this code works to uncheck all the checkboxes on the Form.
    Code:
    For Each ctl In Me.Controls
        
        If TypeOf ctl Is ComboBox Or TypeOf ctl Is TextBox Then
            ctl.SetFocus
            ctl.Text = ""
        End If
        If ctl.ControlType = acCheckBox Then
            ctl.Value = False
        End If
    Next ctl
    I hope this helps

  8. #8
    Chuck55 is offline Novice
    Windows XP Access 2000
    Join Date
    Apr 2012
    Posts
    25
    If ctl.ControlType = acCheckBox Then ctl.Value = False End If

    The same error is returned. Maybe this problem is unique to Access 2000?

    Something other than TypeOf is would be great. I was using TypeOf because this was the only way I knew of that could use a loop condition

  9. #9
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    I don't think that it's your version [2000] . . .
    Can you try a quick test?

    Create a Form and drop two or three check boxes and a cmd btn onto it.
    Then paste the exact code below into the On click event of the cmd btn and run the form.
    Check all three boxes if they are not checked by default.
    Click the button.
    All the check boxes should get un-checked.
    Code:
    For Each ctl In Me.Controls
       If TypeOf ctl Is ComboBox Or TypeOf ctl Is TextBox Then        
          ctl.SetFocus        
          ctl.Text = ""    
       End If    
    
    If ctl.ControlType = acCheckBox Then        
       ctl.Value = False    
    End If
    
    Next ctl
    If that doesn't work - could you post a stripped down copy of your db here?
    Take our sensitive data before you post it. Just leave the bare bones for testing.

  10. #10
    Chuck55 is offline Novice
    Windows XP Access 2000
    Join Date
    Apr 2012
    Posts
    25
    Hi Robeen - your suggestion for the test form lead to my solving the problem. The form I posted about has a tab control and every checkbox was on the tab control. I made a copy of the form, moved all controls off the tab control and TypeOf worked. It did not dawn on me until your test form suggestion that my use of Tab control in this manner was the source of the problem. I have used Tab control in a variety of mdbs and never had a problem with controls and VBA. Thanks for your help and patience in getting me to the solution.

    I would like to retain the structure of having a tab control with the other controls on several tab pages. Is there a solution to clear the checkboxes and listboxes?

  11. #11
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    One problem is that you are using the "Text" property. To set the text property, the control has to have the focus. It is the uncommitted value (ie before it is saved) of the control.
    If you don't try to set the focus to a control, it doesn't matter if it is on a tab control or not.


    Try this:

    Code:
    Private Sub cmdClearCtls_Click()
        Dim ctl As Control
    
        For Each ctl In Me.Controls
            Select Case ctl.ControlType
                Case acComboBox
                    ctl = Empty
                Case acTextBox
                    ctl = Empty
                Case acCheckBox
                    ctl = False
    '            Case acListBox
    '                ctl = Empty
            End Select
        Next
        Me.Dirty = False
    End Sub
    BTW, I am using A2K

  12. #12
    Chuck55 is offline Novice
    Windows XP Access 2000
    Join Date
    Apr 2012
    Posts
    25
    I am now able to clear all control types on the tab control using TypeOf. I created a new form with a new tab and copied the controls from the original form. This was one of those cases when it is better to start over then trying to fix a problem.

    Ssnafu, thanks for the Case structure. I have used Case before but not with TypeOf.

  13. #13
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Could you post the code that you used that finally worked, please?
    That way anyone coming behind us will be able to see your solution.

  14. #14
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Chuck55 View Post
    I have used Case before but not with TypeOf.
    Note that I didn't use "TypeOf", I used the "ControlType" property.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How do I clear the list of recent dbs?
    By revnice in forum Access
    Replies: 4
    Last Post: 01-25-2012, 11:24 AM
  2. Clear combo box
    By Cindygo in forum Forms
    Replies: 3
    Last Post: 09-19-2011, 09:25 AM
  3. Clear value list in combo box
    By jgelpi16 in forum Programming
    Replies: 4
    Last Post: 05-27-2011, 12:45 PM
  4. Clear selections from a List Box?
    By Dreams2Fly in forum Forms
    Replies: 3
    Last Post: 03-28-2011, 02:25 PM
  5. Clear all checkboxes?
    By thekruser in forum Forms
    Replies: 2
    Last Post: 09-16-2010, 09:50 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums