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

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...
ayurkowskiAsked:
Who is Participating?
 
dannywarehamConnect With a Mentor Commented:
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.
Then:

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


0
 
Stephen_PerrettConnect With a Mentor Commented:
> 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

Steve
0
 
ArjiConnect With a Mentor Commented:
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

RS1.movefirst

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

     RS1.Edit
        RS1!BoundCheckBoxField = False   'Clear the checkbox field
     RS1.Update

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

RS1.Close
RS2.Close
DB.Close
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!!

0
 
ArjiCommented:
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.AddNew
          RS2!TestKey = RS1!TestKey
          RS2!TestDesc = RS1!TestDesc
          RS2!.DateSelected = Date
       RS2.Update
   
       RS1.Edit
          RS1!BoundCheckBoxField = False   'Clear the checkbox field
       RS1.Update
    end if

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

That way the BoundCheckBoxField is cleared to False only if it was true to start with.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.