Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2009
    Posts
    17

    Question Unanswered: Select All using checkbox from MSaccess

    hi i'm having difficulties solving this problem. i'm trying to make a list of person using a continuous form which has a check box in it, to select the person i want, i will filter the data using their office,year hired, age, and name.

    to be able to give them an invitation, the user will check the checkbox for the person and uncheck if not. because the data has many records i'd like to filter the data using the following criteria that i mentioned. if i check the "Select All" checkbox, the checkbox on the filtered data will be selected. and if i uncheck it, the checkbox on the filtered data will be deselected.

    sorry if i can't explain it briefly but i really need your help!
    many tenks in advance.

    PS. do you have example for this? it will be a great help ;-)

    fredyski
    Last edited by fredyski; 03-13-09 at 02:19.

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Welcome to the forums.

    I assume the "select all" checkbox is on a form with all the other checkboxes on a continuous subform? If so, I think there's two ways to go about it:

    Option 1
    On the click of the select all checkbox, perform an update query and then requery the subform

    Option 2
    Cycle through the subform recordset and set each individually, something like:
    Code:
        Dim frm As Form
        Dim subfrm As Control
        Dim rst As DAO.Recordset
        Dim blnCheck As Boolean
        
        Set mainfrm = Forms!frmName
        Set subfrm = mainfrm!frmSubName
        Set rst = subfrm.Form.Recordset
        blnCheck = me.chkName
    And then if it's a linked field you'll need to declare the edit and update after, something like this:
    Code:
        rst.MoveFirst
        Do While Not rst.EOF
            With rst
                .Edit
                !fldCheck = blnCheck
                .Update
                .MoveNext
            End With
        Loop
    Otherwise, if it's unbound I think you do like:
    Code:
        rst.MoveFirst
        Do While Not rst.EOF
            With rst
                .fldCheck = blnCheck
                .MoveNext
            End With
        Loop
    All untested, but should give you a good start at least I think. If you have questions about forms/subforms references, I use this as a reference.
    Me.Geek = True

  3. #3
    Join Date
    Mar 2009
    Posts
    17
    thanks for the reply, i really appreciate it.
    sorry, i don't understand VB code that much, if you have an example for this i think i can get to it.

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    well first off, is it indeed a form with a continuous subform? What's the names of the controls (the "all" checkbox name, the subsequent checkboxes, etc.)? Are the checkboxes bound or unbound?
    Me.Geek = True

  5. #5
    Join Date
    Mar 2009
    Posts
    17
    first of all, sorry i forgot to tell you that i'm not a programmer. so, kinda bit slow. please bear some patience with me.

    in my emp_inv table(name,age,years,office,inv)
    inv data type is yes/no (checkbox). everytime you filter a list of data, all in the fields are locked except for the inv which is the checkbox.

    i'm using only 1 form, the list of data is shown in the detail section (set to continuous form). In the form header i put 3 combo boxes(age,office,year) and text box(names) for the criteria and a command button to start searching.

    i found a sample program on the internet regarding listing and filtering of data. that's why i put it in my program and it worked. here 's the code:

    Code:
        Private Sub cmdFilter_Click()
        Dim strwhere As String
        Dim lngLen As Long
        
        If Not IsNull(Me.txtname) Then
            strwhere = strwhere & "([Name] Like ""*" & Me.txtname & "*"") AND "
        End If
            
        If Not IsNull(Me.cboYears) Then
            strwhere = strwhere & "([years] = " & Me.cboYears & ") and "
            
        End If
        
        If Not IsNull(Me.cboage) Then
            strwhere = strwhere & "([age] = """ & Me.cboage & """) AND "
        End If
        
        If Not IsNull(Me.cbooffice) Then
            strwhere = strwhere & "([office] = """ & Me.cbooffice & """) AND "
        End If
    
        lngLen = Len(strwhere) - 5
        If lngLen <= 0 Then
            MsgBox "No criteria", vbInformation, "Nothing to do."
        Else
            strwhere = Left$(strwhere, lngLen)
            
            Me.Filter = strwhere
            Me.FilterOn = True
        End If
    End Sub
    now, i really don't know how to put the "Select All" checkbox.

    help me please, i just want to finish this project. tnx

  6. #6
    Join Date
    May 2005
    Posts
    1,191
    Start by putting an unbound checkbox in the header. Then on the click event of that checkbox build and run an update SQL string (see Martin Green's six part lecture for help on that if you need). Tip: Use your form's filter string as the WHERE clause for you query.

    It might be a little late, but you may also want to think about changing your field names to be something a little more specific, as I know that Name and Year are Reserved Words in Access. Tip: I name all my fields starting with "fld", so like "fldName", "fldYear", etc.
    Me.Geek = True

  7. #7
    Join Date
    Mar 2009
    Posts
    17
    ok, i will try it.
    thank you for the "Tips", it will be a big help as well.

  8. #8
    Join Date
    Mar 2009
    Posts
    17

    Thumbs up Select all checkbox

    hellow!

    i finally made it, i followed all your advise.
    i put an SQL query to filter the data then an update query.
    thank you for the help!
    i hope this thread will also help others.

    cheers

Posting Permissions

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