Option Buttons Multi Select (2000)

Topic

New Reply

Edited by HansV to provide link to post – see Help 19

Following an earlier post 413377 I have now generated an incredible form with 64 Option Buttons. Which as you select or deselect the Options updates a table accordingly (there is no way I would have been able to do this without Francois assistance for which I shall always be grateful)

However, the department I am building the database for instead of being incredibly grateful now want the option to be able to select or clear them all from the current form from a button.

So if Opt1, Opt2 and Opt 36 are selected they want the option to deselect them so the form is now clear.
Also to be able to select Opt1 through Opt64 (this would be all or nothing!!!)

Thanks

Roberta

Reply | Quote

Replies

Create the following procedure in the form module:

Private Sub SetAll(OnOff As Boolean)
Dim i As Integer
For i = 1 To 64
Me.Controls(“Opt” & i) = OnOff
Next i
End Sub

Put a command button cmdAllOn with the following On Click code:

Private Sub cmdAllOn_Click()
SetAll True
End Sub

and also a command button cmdAllOff with the following On Click code:

Private Sub cmdAllOff_Click()
SetAll False
End Sub

Reply | Quote

Thanks for this – and for showing me how to edit my post (much appreciated).

However, though the code above will beautifully select or deselect the Option Buttons – unfortuantely it does not up date the table which is triggered by the AfterUpdate event of each option button (if I go on a record and back a record the selections have returned to their previous state rather than writing to or deleting from the table from the AfterUpdate event).

The option buttons are in a sub form – and I have tried with the code in both forms. Only with the code in the sub form will the select or deselect work – but I think I need to do something else to trigger the AfterUpdate code in the form at the same time?

I am so close to getting this database finished and have learnt so much!!!

Thanks

Roberta

Reply | Quote

Do you have code in the afterupdate event of each of the 64 checkboxes ?
What for code is that ? Every time the same ?
Can’t you make some generic code and call it from the setAll sub ?
If you explain what you’re doing in the after updates, maybe one of us can find a solution.

Reply | Quote

The code I have used is some that you very kindly provided for me

Private Sub Form_Current()
If Me.NewRecord = False Then
Me.Opt1 = DCount(“*”, “destination table”, “[pdf]= ” & Me.PDFRef & ” and [Location] = ‘Business Site'”) = 1
Me.Opt2 = DCount(“*”, “destination table”, “[pdf]= ” & Me.PDFRef & ” and [Location] = ‘Customer Site'”) = 1
End If
End Sub

Private Sub Opt1_AfterUpdate()
Dim strSQL As String
DoCmd.SetWarnings False
If Me.Opt1 = True Then
strSQL = “INSERT INTO [Destination Table] ( PDF, Location ) Values (” & Me.PDFRef & “,’Customer Site’)”
Else
strSQL = “DELETE ‘*’FROM [Destination Table] WHERE [Destination Table].PDF = ” & Me.PDFRef & _
” AND [Location] = ‘Customer Site'”
End If
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End Sub

Private Sub Opt2_AfterUpdate()
Dim strSQL As String
DoCmd.SetWarnings False
If Me.Opt2 = True Then
strSQL = “INSERT INTO [Destination Table] ( PDF, Location ) Values (” & Me.PDFRef & “,’Business Site’)”
Else
strSQL = “DELETE ‘*’FROM [Destination Table] WHERE [Destination Table].PDF = ” & Me.PDFRef & _
” AND [Location] = ‘Business Site'”
End If
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End Sub

Which I have to tell you works absolutely brilliantly – just not when they want to be able to update all or clear all!!

Roberta

Reply | Quote

A (tedious) solution is to call all the 64 After Update procedures in SetAll:

Private Sub SetAll(OnOff As Boolean)
Dim i As Integer
For i = 1 To 64
Me.Controls(“Opt” & i) = OnOff
Next i
Opt1_AfterUpdate
Opt2_AfterUpdate
…
Opt64_AfterUpdate
End Sub

It may be possible to do this more efficiently, but we’d have to know:
a) whether the locations corresponding to the 64 option buttons are ALL possible locations, and
whether there is a table listing all possible locations.

Reply | Quote

Yes there are three tables one which lists the project details, one which lists the possible locations (all 64 of them!!) and a third table — which has the project number and the location (being joint primary keys as each project can only be in one location!.

The third table is the one which is updated by the option selection.

However, if the 64 after update entries will work – then I am happy to go with this (anything to get this finished!!!!!!)

Cheers

Roberta

Reply | Quote

Say that you table with project details is named tblProjectDetails, and that it contains a field named Location. You should be able to use this code instead of listing all 64 option buttons explicitly:

Private Sub SetAll(OnOff As Boolean)
Dim strSQL As String
Dim i As Integer
For i = 1 To 64
Me.Controls(“Opt” & i) = OnOff
Next i
If OnOff = True Then
strSQL = “INSERT INTO [Destination Table] ( PDF, Location ) SELECT ” & _
Me.PDFRef & ” AS PDF, Location FROM tblProjectDetails”
Else
strSQL = “DELETE * FROM [Destination Table] WHERE PDF = ” & Me.PDFRef
End If
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End Sub

This code does not rely on the After Update procedure of the option buttons, so if you don’t need the visual feedback, you might even leave out the part that turns the option buttons on/off.

Reply | Quote