Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Option Groups (Access 2k2)

    Is there a way to populate an option Group from a separate table?

    I want to place a filter on a form, but I want the ability to vary (on an irregular basis) the values on which I filter by. As the program is used, the operator gets practical experience of which values he or she wants to filter by, which frequently is not the same as my own. Also as time goes by, the values required for the filter will vary.

    What I want is a table with 7 values (my limit of options) in it, which would be used as the 7 "Choices" in the Option Group. This way, I can easily allow for a simple way to change the filter values easily.

    Thanks

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

    Re: Option Groups (Access 2k2)

    Create a table with 2 fields: a numeric field Value (long integer) and a text field Label.
    Create a primary key on Value.
    Create 7 records in this table. Set Value to the value you want the option group to return for the corresponding option button and Label to the caption to be displayed. If you want to hide an option button, leave the Label field empty.

    Create an option group with 7 option buttons on the form. The captions of the labels can be arbitrary, e.g. "x".
    Make all labels the same width, wide enough to accomodate the longest caption you will need.

    In the OnLoad event of the form, create code to set the values for the option buttons and captions for the labels. (If you don't know how to do this, post back.)

    This code uses DAO. You need to set a reference to the Microsoft DAO x.x Object Library (Tools/References... in any module).

    Private Sub Form_Load()
    Dim rst As DAO.Recordset
    Dim intIndex As Integer

    On Error GoTo Err_Load

    Set rst = CurrentDb.OpenRecordset("tblOptions")
    Do While Not rst.EOF
    intIndex = intIndex + 1
    If IsNull(rst!Label) Then
    Me("opt" & intIndex).Visible = False
    Else
    Me("opt" & intIndex).OptionValue = rst!Value
    Me("lblOpt" & intIndex).Caption = rst!Label
    End If
    rst.MoveNext
    Loop

    Exit_Load:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Exit Sub

    Err_Load:
    MsgBox Err.Description, vbExclamation
    Resume Exit_Load
    End Sub

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Option Groups (Access 2k2)

    Thanks very much for the answer.

    I do find that I learn as I go, and I feel that the solution you gave me to my last question was one that I should have been capable of working out.

    However I feel I am ages away from working out this kind of answer without some help.

    Thanks again. I'll try and load this later today when I get some time.

    Colin

  4. #4
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Option Groups (Access 2k2)

    It worked just fine after a bit of tweaking

    opt & lblOpt were not used as default names by the wizard creating the option group, and the label numbers increment by 2. So after I renamed all, it ran exactly as I wanted.

    I was even able to use the "&" in the table holding the values to create a ALT+ shortcut.

    Thanks very much for your help. This has saved me hours of experimentation.

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

    Re: Option Groups (Access 2k2)

    Hello Colin,

    Yes, I should have mentioned that the option buttons and labels had to be renamed. I'm glad that you figured it out yourself.

    Glad to have been of help,
    Regrads,
    Hans

  6. #6
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Option Groups (Access 2k2)

    Hi Hans

    You kindly helped me set-up an Option Group yesterday, but I'm struggling to get the handling of the group to work as well. I've used the code below to handle the selections and this works as it should.

    However, as the entries are controlled by the table tblFilters (set up as you suggested), letting me vary the filter choices programmatically, I need to be able to change this code below as well.

    I've tried adaprting the code you gave me yesterday, but can't seem to make it work.

    Any suggestions.

    Thanks in anticipation


    Select Case optFilt
    Case 1
    Me.FilterOn = False
    Case 2
    Me.Filter = "Word = 'Direct Marketing'"
    Me.FilterOn = True
    Case 3
    Me.Filter = "Word = 'Marketing'"
    Me.FilterOn = True
    Case 4
    Me.Filter = "Word = 'Programming'"
    Me.FilterOn = True
    Case 5
    Me.Filter = "Word = 'Software'"
    Me.FilterOn = True
    Case 6
    Me.Filter = "Word = 'Systems'"
    Me.FilterOn = True
    Case 7
    Me.Filter = "Word = 'Training'"
    Me.FilterOn = True
    Case Else
    Me.FilterOn = False
    End Select

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

    Re: Option Groups (Access 2k2)

    Hello Colin,

    What I would do is add a new field to tblFilters. It already has fields corresponding to the value returned by the option group (Value) and to the caption of the displayed label (Label). Now add a text field Filter and put the filter values in it. Leave this field empty if you want to turn filtering off.

    <table border=1><td>Value</td><td>Label</td><td>Filter</td><td>1</td><td>&All</td><td> </td><td>2</td><td>&Direct marketing</td><td>Direct marketing</td><td>3</td><td>&Marketing</td><td>Marketing</td><td>4</td><td>&Programming</td><td>Programming</td><td>etc.</td><td>...</td><td>...</td></table>

    Now, the code can be simple:

    Dim varFilter
    varFilter = DLookup("Filter", "tblOptions", "Value=" & optFilt)
    If IsNull(varFilter) Then
    Me.FilterOn = False
    Else
    Me.Filter = "Word = '" & varFilter & "'"
    Me.FilterOn = True
    End If

    Note the use of single and double quotes:
    <pre>Me.Filter = "Word = '" & varFilter & "'"</pre>

    If you want to filter on multiple fields, you can add more fields to tblFilters. You can add more bells and whistles if you like, for instance comparison operators < or > etc. But keep in mind that the end result should be transparent to the user.

  8. #8
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Option Groups (Access 2k2)

    Hans

    Wonderful

    I had already added a separate field to the table as you had suggested, but I couldn't get it into my Case selection. Your solution was so simple.

    Thanks again for your help.

    Its very much appreciated. I have had no training in this, and while I can get there in the end, I often need a pointer in which way to go.

    Once again thanks for your time and your understanding.


    Colin

Posting Permissions

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