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

Posted on 2005-05-07
Medium Priority
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
  • 2
LVL 26

Accepted Solution

dannywareham earned 536 total points
ID: 13950025
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

Stephen_Perrett earned 532 total points
ID: 13950052
> 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

Arji earned 532 total points
ID: 13950545
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

ID: 13954481
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

862 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