Option Groups (Access 2k2)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Value	Label	Filter
1	&All
2	&Direct marketing	Direct marketing
3	&Marketing	Marketing
4	&Programming	Programming
etc.	…	…

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:

Me.Filter = "Word = '" & varFilter & "'"

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 etc. But keep in mind that the end result should be transparent to the user.

Reply | Quote

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

Reply | Quote