Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2010
    Location
    Illinois
    Posts
    6

    Question Unanswered: Selecting/Deselecting All Checkboxes in a continuous form

    My Database: We have a Customer Retail db that contains cust info as well as the services we did for them. It has 4 qrys for cust follow-ups: 2day, 2wk, 6mo, and 1yr....So for ex: When I click "2 Day Follow-up", it pulls up a Continuous Form that lists all cust which require a 2 Day followup with a checkbox to the left - so when checked, the cust is removed from the list. I also have a Rpt created that the employees can print & go thru the list as they followup with the customers throughout the workday and at the end of the day, they go back into the database & check all the names off their list....I have been requested to add a "Check All" box since some of our employees have a long list of customers.

    Here's my problem: I wrote a VB code, but when I check the master checkbox, it only checks the selected record - it doesn't check all the customers that are on the list.

    Here is what I have: The followup yes/no field (named ACCOUNT_FU2D) on the followup form. I created a checkbox object (named MasterCkBx) using the following code On Click:

    Private Sub MasterCkBx_Click()

    'Turns all of the checkboxes on or off using Check All (MasterCkBx) button.

    If Me.MasterCkBx = True Then
    Me.ACCOUNT_FU2D = True

    ElseIf Me.MasterCkBx = False Then
    Me.ACCOUNT_FU2D = False

    End If

    End Sub



    Do I need a loop or something else in here too? What am I missing? I can't find ANYTHING on this particular issue! Thanks!

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    one doesn't do loops in database - one does Action Queries

    in your case the set of records that are going to have all their checkboxes varied needs to be established - perhaps that is a table - but let's assume it is a query of a table that we will call: ToBeChangedQuery

    then make an UpdateQuery using that record set (ToBeChangedQuery) as the source and make it put either -1 (checked) or 0 (not checked) as the value for that check box field.

    do that manually and verify that it works - and then figure out a way to fire it from the user's form.

  3. #3
    Join Date
    Jan 2010
    Location
    Illinois
    Posts
    6
    Thank you for your response, much appreciated!! I just thought it would be as easy as adding an update all records in the code or something like that. Since I'm not very savvy with Access yet...let me see if I got this right?

    I have a qry that filters out customers from the Customers tbl; for example, "qry_FU_2D_filtered" pulls up our 2Day follow-up list, which is viewed on a form "frm_FU_2d_filtered" by our endusers.

    So I started to create an update query, "qry_FU_2D_filtered_UPDATE", using the above qry as the source. I just got confused from here...also I'm not sure how I would work this in with the master checkbox on the end users' forms to where they would check the master box and all the boxes then get checked.

    I apologize, I just never created an update qry like this before. The MS Help only explained how to update one field by using another field...

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If what you mean is that you want to loop through a set of controls on a form (such as changing Text1, Text2, Text3, etc...) you can loop through the controls and manipulate things such as true/false values, captions, colors, etc.. (providing you name the form's control name property with numbers after them and make the first part of the name the same). You can then cycle through a set of fields.

    ex (I name all 20 of my true/false fields using "Text" as the prefix):
    For X = 1 to 20 (20 controls....Text1, Text2, Text3,....Text20)
    me.controls("Text" & X).value = true
    next X
    Last edited by pkstormy; 01-29-10 at 00:27.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Jan 2010
    Location
    Illinois
    Posts
    6
    Thanks for your response PK....Yes, that is exactly what I'm wanting to do. From the switchboard, when the enduser clicks on "2Day Followup List", using the 2Day qry, it pulls up a form with a list of all the customers that need a 2 Day followup and a T/F checkbox is next to each name. I wanted to add a button or control named Select All so when the enduser clicks it, it will then change all customers on the list to True or False.....

    Downside is that there is a different number of customers on the list everytime so is there like an EOF (end of file) code that I can try? Thanks again for all of your help.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I think we are misunderstanding each other. The example I posted is used for a single form (I just realized you want to do this on a continous form and what I posted is an example to cycle through the fields on a SINGLE form and change values).

    I "think" what you're looking for (if you're trying to do it against a continuous form), you can write an UPDATE query which updates the same recordset (using the same criteria) as the continuous form. You then just run that query (ie. docmd.openquery "MyUpdateQueryName") when appropriate.

    Otherwise, if it's against a listbox based on a what is highlighted in a listbox (but again, this is done against a listbox - ie. on a single form.)

    If so, you would change the listbox "multi select" property to simple (versus none) which would allow users to highlight 1 or more records in the listing and then write code to loop through the listbox listing checking which ones are highlighted and update values in the table.

    This code may not be exactly correct but it would be something like this:

    Dim ctlList As Control
    Dim i As Integer
    Set ctlList = Me!ListBoxItems
    For i = 0 To ctlList.ListCount - 1
    if ctlList.Selected(i) = True then
    call myFunctionToUpdateThisCheckboxPassingIDValueOfList box(ctlList.ItemData(i))
    Next i

    and then your myFunctionToUpdateThisCheckboxPassingIDValueOfList box function would open the recordset (using the ID value passed to it) and update the appropriate true/false fields.

    ex:
    Function myFunctionToUpdateThisCheckboxPassingIDValueOfList box(RecID as variant)
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from myDataTable where RecordID = " & RecID & ""
    rs.open strSQL, currentproject.connection, adopendynamic, adlockoptimistic
    rs!SomeField = true
    rs!SomeOtherField = true
    rs.update
    rs.close
    set rs = nothing
    End Function
    Last edited by pkstormy; 01-29-10 at 12:13.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Tags for this Thread

Posting Permissions

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