Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Cheltenham, Gloucestershire, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Option Buttons Multi Select (2000)

    Edited by HansV to provide link to post - see <!help=19>Help 19<!/help>

    Following an earlier <post#=413377>post 413377</post#> 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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Option Buttons Multi Select (2000)

    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

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Cheltenham, Gloucestershire, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Option Buttons Multi Select (2000)

    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

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Option Buttons Multi Select (2000)

    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.
    Francois

  5. #5
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Cheltenham, Gloucestershire, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Option Buttons Multi Select (2000)

    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Option Buttons Multi Select (2000)

    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
    [img]/forums/images/smilies/cool.gif[/img] whether there is a table listing all possible locations.

  7. #7
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Cheltenham, Gloucestershire, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Option Buttons Multi Select (2000)

    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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Option Buttons Multi Select (2000)

    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.

Posting Permissions

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