How to build a "references dialog box"-type checklist?

Posted on 2005-05-07
Last Modified: 2008-03-06
Must be tired, but I have scratched my head for a few hours and can't come up with a method to accomplish this:
I want to create a continuous subform which lists AVAILABLE diagnostic tests (based on a product type selected in the parent form) and allows the user to check which ones are to be performed. On re-opening the list, both the selected and available items should appear. EXACTLY the same function as the VBA "references" checklist, right? Populating the list from a product-to-tests xref table is no biggie, and I'm thinking the checkbox should be unbound and linked at runtime. The checkbox update event should then probably add (or remove) records from a "selected tests" table.
To simplify things, let's say:
T1: test_key, test_desc
T2: product, test_type_reqd
and if T1 = (1,kick it),(2,squeeze it),(3,smell it)
and T2 = (A,2),(A,3)...

I want to see
Unit: "A"
->    kick it          _
       squeeze it    X
       smell it        X

How they heck do I setup the subform and click events?
Like I said, too many hours this week in front of the screen...
Question by:ayurkowski
    LVL 26

    Accepted Solution

    OK. You can use a FOR NEXT statement, combined with a CASE for this to loop through your check boxes, check their value and if ticked do something... Idea?

    Store the object name (checkbox name) and it's associated procedure name in a table.

    On error resume next
    Dim myLoop as Integer
    Dim myProc as String
    Dim myControl as String

    For myLoop = 1 to 10    'number of controls, where your controls are called check1, check2, etc
         myControl = "Check" & myLoop
         If Me.MyControl.Value = -1 Then
            'Option is checked - run procedure
            myProc = Dlookup("ProcName", "yourTable", "ObjectName = " & chr(34) & Me.MyControl & chr(34))
            Call myProc
         End If
         myProc = ""
    Next myLoop

    LVL 11

    Assisted Solution

    > I'm thinking the checkbox should be unbound and linked at runtime

    Not sure what you are intending. Why unbound and linked at runtime? Checkboxes must be bound on continuous forms if they are to be useful.

    If you want to refresh the order so that the checked items appear first you could use

    Me.OrderBy = "[NameOfFieldCheckBoxIsBoundTo], [OtherField]"
    Me.OrderByOn = true

    as code in the After Update event

    LVL 17

    Assisted Solution

    I would do it this way.  Bind the checkbox to a field(MyCheckBox) in T1. Place a button on the main form(MyButton).  allow the users to check all the boxes necessary and in then place the code below in the On Click event of the button:

    T1: test_key, test_desc, BoundCheckBoxField
    T2: test_key, test_desc, DateSelected               'Added foreign key to results table. Shouldn't the results contain what you selected?
    and T2 = (A,2),(A,3)...            'Not sure what you meant about this

    Private Sub MyButton_OnClick()

    Dim DB as Database
    Dim RS1 as Recordset
    Dim RS2 as Recordset

    Set DB= CurrentDb()
    Set RS1= DB.OpenRecordset(T1)         ' Open Recordset for subform
    Set RS2= DB.OpenRecordset(T2)         ' Open Recordset for results table


    Do while Not RS1.EOF
        If RS1!BoundCheckBoxField = True then
              RS2!TestKey = RS1!TestKey
              RS2!TestDesc = RS1!TestDesc
              RS2!.DateSelected = Date
         end if

            RS1!BoundCheckBoxField = False   'Clear the checkbox field

         RS1.Movenext      'Move to the next record to check if the checkbox is true  

    set RS1 = nothing
    set RS2 = nothing
    set DB = nothing

    [YourSubformName].Form.refresh           ' Refreshes the checkboxes in subform

    docmd.close          'If you want to close the main form

    end sub

    This will read the fields in T1 and place the results in T2 for you to use for a query/report.  Then it clears the checkbox field in T1 to get ready for a new selection process.  Finally it refreshes the subform to show the clearing of the checkboxes.

    I may not have exactly as you described but hopefully this will at least get you in the right direction.  Also, like the References Dialog, you might want to close the main form at the end of the process(See Above)

    Good luck!!

    LVL 17

    Expert Comment

    I was thinking more about this and decided it needs a small change in the Do loop:

    Do while Not RS1.EOF
        If RS1!BoundCheckBoxField = True then
              RS2!TestKey = RS1!TestKey
              RS2!TestDesc = RS1!TestDesc
              RS2!.DateSelected = Date
              RS1!BoundCheckBoxField = False   'Clear the checkbox field
        end if

        RS1.Movenext      'Move to the next record to check if the checkbox is true  

    That way the BoundCheckBoxField is cleared to False only if it was true to start with.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now