Option Group Values based on a Table

[James Frater]

Hello Everyone,

Is it possible to create an Option Group based on vaule in a table? The
Option Group would then be used to filter a subform and a few reports.

The fields I'd like to use are:
UserID
SportTypeID
SportTypeName
from the table tbl_UserAllocations

The background to this is users of the database are assigned areas of work
and I'd like the Option group to change to match their allocation of work.

Example for each user on the form frm_myAllocation I'd like the Option group
to change to the following:

James is assigned
Rugby Union
Football
Hockey

Olly is assigned
Polo
Horse Riding

Polly is assigned
Tennis
Badminton
Squash

Thanks in advance

James

 

[Arvin Meyer [MVP]]

You realize of course that option group uses only a numerical values.

Those values can be use in a select case statement to do what you're looking
to do.

 

[James Frater]

Arvin,

Thanks for your swift response.

I probably didn't explain my example properly. I'm happy with case select
and how that works, it's the how to create the option group and change the
number of options depending on how many sports each user has been allocated.

If we use my allocation as an example, when I open up the form
"frm_UserAllocation" I'd like the option group "group_Allocation" to have
three option button and three options labels. I'd like the value for each of
the option buttons to be carried forward from the table tbl_UserAllocations
where the option group is filtered on the field "UserID"

13 Rugby Union
8 Football
6 Hockey


I'm probably missing a really basic step, so any help is always massively
appreciated.

Thank you

James

 

[Arvin Meyer [MVP]]

I wouldn't change the number of options. Instead, I would use an option
group which contained all the of the options possible. Then I'd show and
hide options based on the conditions encountered. You can also change
captions the same way. To make it all make sense to your code, make sure you
rename the option buttons and their labels.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[James Frater]

Arvin, Good morning,

After a quick chat with the girls and boys in the office, they've decided
they'd rather have enabled/not-enabled over visable/not-visable.

I'm probably missing something very simple, but I only seem able to enable
the 1st sport and not any of the others. In my case it will only enable 6
Hockey and not 8 Football or 13 rugby Union. It's the same for the other
users.

Massive thanks in advance, code below

James

======

Private Sub Form_Load()
Dim i As Integer
Dim RsGetSport As ADODB.Recordset

Set RsGetSport = New ADODB.Recordset

For i = 1 To 35
SQLStr = "SELECT * FROM tbl_UserAllocations WHERE userID=" & Me.UserID
RsGetSport.Open SQLStr, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic, adCmdText

If Controls("opt_" & i).OptionValue = RsGetSport("st_id") Then
Controls("opt_" & i).Enabled = True
Else: Controls("opt_" & i).Enabled = False
End If

RsGetSport.Close
Next i

Set RsGetSport = Nothing

End Sub

======

 

[Arvin Meyer [MVP]]

I wouldn't "attack" the problem that way. I'd use the Form Open event,
rather than the load, to allow the data to fill in, then use your SQL
statement. Then I'd specifically assign the values and captions to the radio
buttons. You do not need all 35 options. You only need to have enough
buttons to fill the maximum number of values that can be entered by any 1
person. Then disable any unused buttons. I'd actually reconsider hiding
them. If, for instance, you have 5 buttons and 3 options, fill 3 buttons and
hide the other 2. I'd also consider hard coding the values and captions and
using a Case statement. Unless you are continually changing options or
users, that's probably the fastest method.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[James Frater]

Thanks Arvin,

After another consultation round everyone is now in agreement that hiding
unused buttons is a much better idea! I'm sure that had nothing to do with
the cake at my desk

However I still have the same problem in that I can't get it to move through
the records.

Using my allocation as an example. I can assign the 1st radio button it's
value, assign the label it's name and make them both visable, however I can't
assign it the to the other two and then make the remaining 8 buttons
non-visable.

Am I missing a do..loop command or something along those lines?

Many thanks

James

 

[James Frater]

BruceM hello,

Yes the idea is to display the option buttons relevant to that person.

Where a user has been allocated 4 sports, when they open the from
frm_UserAllocation they will have 4 option buttons available, or 1, or 6, or
10 or another number depending on how many sports they've been allocated.

I can make the right number of option buttons visible for each user, however
what I can't seem to achieve is passing values to the option value of each
button.

Using my alloaction as an example.

I'm assigned three sports (AllocationOrder is set with a bit of code listed
below)

ST_ID SportName AllocationOrder
13 Rugby Union 1
8 Football 2
6 Hockey 3

for option button 1 I'd like it's option value to be 13 and it's label
caption to be Rugby Union

for option button 2 I'd like it's option value to be 8 and it's label
caption to be Football... and so on.

Hope that makes sense

James


====
AllocationOrder

SQLStr = "SELECT * FROM tbl_userallocation WHERE UserID=" & Me.UserID
RsUpdateAllocation.Open SQLStr, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic, adCmdText

j = 1
Do Until RsUpdateAllocation.EOF
RsUpdateAllocation("AllocationOrder") = j
RsUpdateAllocation.Update
RsUpdateAllocation.MoveNext
j = j + 1
Loop
RsUpdateAllocation.Close

====

I noted something earlier, but wanted to see how the thread developed.
However, this may be a good time to pass along what I observed.

In this line of code it seems to me that RsGetSport('st_id") refers to a
specific value that will not change with each loop.
If Controls("opt_" & i).OptionValue = RsGetSport("st_id")

Is the idea that you would display just the option buttons relevant to the
person, and the person would then select the appropriate one for that record?

Thanks Arvin,

After another consultation round everyone is now in agreement that hiding
unused buttons is a much better idea! I'm sure that had nothing to do with
the cake at my desk

However I still have the same problem in that I can't get it to move through
the records.

Using my allocation as an example. I can assign the 1st radio button it's
value, assign the label it's name and make them both visable, however I can't
assign it the to the other two and then make the remaining 8 buttons
non-visable.

Am I missing a do..loop command or something along those lines?

Many thanks

James

I wouldn't "attack" the problem that way. I'd use the Form Open event,
rather than the load, to allow the data to fill in, then use your SQL

[quoted text clipped - 140 lines]

.

--



.

 

[James Frater]

Morning BruceM

Thanks for all of this, I know it's a difficult one to get your head around.

So in answer to your questions.
The option group has 10 option buttons and labels. They are named opt1, opt2
etc etc and the labels are lbl1, lbl2 etc etc.

On the form_open event I use the following code to show or hide the relevant
number of option buttons

For i = 1 To DCount("UserAllocationID", "tbl_userallocation", "UserID=" &
Me.UserID)
Controls("opt" & i).Visible = True
Next i

For i = (DCount("UserAllocationID", "tbl_userallocation", "UserID=" &
Me.UserID) + 1) To 10
Controls("opt" & i).Visible = False
Next i

I've now created a query (I agree it keeps things tidier, so thank you), but
the problem I have is I cant get the loop to work and pass a value to the
optionvalue of the corresponding button or the text to the corresponding
label caption.

This is my current loop

Set RsGetSport = New ADODB.Recordset
SQLStr = "SELECT * FROM qry_userallocation WHERE UserID=" & Me.UserID
RsGetSport.Open SQLStr, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic, adCmdText

Do
i = 1
Do Until i < DCount("UserAllocationID", "tbl_userallocation", "UserID=" &
Me.UserID)

If i = RsGetSport("allocationorder") Then
Controls("opt" & i).OptionValue = RsGetSport("st_id")
Controls("lbl" & i).Caption = RsGetSport("Sport Type")
End If
Exit Do
i = i + 1
Loop
Loop Until i = DCount("UserAllocationID", "tbl_userallocation", "UserID=" &
Me.UserID)

RsGetSport.Close
Set RsGetSport = Nothing


The order is set by our allocations people when they take bookings. It's set
by the following code:

SQLStr = "SELECT * FROM tbl_userallocation WHERE UserID=" & Me.UserID
RsUpdateAllocation.Open SQLStr, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic, adCmdText

j = 1
Do Until RsUpdateAllocation.EOF
RsUpdateAllocation("AllocationOrder") = j
RsUpdateAllocation.Update
RsUpdateAllocation.MoveNext
j = j + 1
Loop
RsUpdateAllocation.Close

It still seems to me that RsGetSport('st_id") is a fixed value through each
loop.

I assume there is a Sports table, a Users table, and a junction table
tbl_UserAllocations.

tblSport
SportID (Number primary key)
SportName

tblUsers
UserID (primary key)
FirstName
LastName
etc.

tbl_UsersAllocation
UA_ID (PK)
SportID
UserID
AllocationOrder

There is a relationship between the like-named fields. For the interface,
there is a User form, with a subform based on tbl_UsersAllocation. A combo
box on the subform gets its Row Source from tblSport. In this way any number
of sports may be assigned to a user.

This allocation would be done once, or infrequently.

I would use a query based on tbl_UsersAllocation, with a join to tblSport, as
the Row Source for a list box. To assign option values and captions to
option buttons will take some more work.

You will need the maximum possible number of option buttons in the option
group. Use a query based on tbl_Users Allocation, limited to one UserID,
with a join to tblSport. Use the count of this recordset as the number of
times to repeat the loop (or maybe it would be EOF). In any case, each time
through the loop you would move to the next option button. Assign its Option
Value and the Caption property for the label from the current record in the
recordset. I fear I am not explaining this very well, but I don't have a lot
of time to work out the details just now. The general idea is to produce a
recordset listing all of the sports allocated to a particular user, then loop
through that recordset, assigning values to each option button and label. I
don't see how the allocation order is set. I see how there are numbers, but
I don't see where the order comes from.

I'll have to leave it here for now, incomplete though it may be.

BruceM hello,

Yes the idea is to display the option buttons relevant to that person.

Where a user has been allocated 4 sports, when they open the from
frm_UserAllocation they will have 4 option buttons available, or 1, or 6, or
10 or another number depending on how many sports they've been allocated.

I can make the right number of option buttons visible for each user, however
what I can't seem to achieve is passing values to the option value of each
button.

Using my alloaction as an example.

I'm assigned three sports (AllocationOrder is set with a bit of code listed
below)

ST_ID SportName AllocationOrder
13 Rugby Union 1
8 Football 2
6 Hockey 3

for option button 1 I'd like it's option value to be 13 and it's label
caption to be Rugby Union

for option button 2 I'd like it's option value to be 8 and it's label
caption to be Football... and so on.

Hope that makes sense

James

====
AllocationOrder

SQLStr = "SELECT * FROM tbl_userallocation WHERE UserID=" & Me.UserID
RsUpdateAllocation.Open SQLStr, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic, adCmdText

j = 1
Do Until RsUpdateAllocation.EOF
RsUpdateAllocation("AllocationOrder") = j
RsUpdateAllocation.Update
RsUpdateAllocation.MoveNext
j = j + 1
Loop
RsUpdateAllocation.Close

====

I noted something earlier, but wanted to see how the thread developed.
However, this may be a good time to pass along what I observed.

[quoted text clipped - 31 lines]

.

--



.

 

[James Frater]

BruceM,

Hope you had a good break over Christmas? I had a rather nasty bout of
man-flu so spend most of it hidden under a duvet!

Thank you so so much for below makes so much more sense set out like that.
One thing however I can't seem to do it clear the value for the controls as
setting it to "Null" gives me the error message "Run-Time error '13': Type
mismatch"

Thank you

James

One thing I see is that this line may not have the intended effect:
Do Until i < DCount...

Another point is that I would assign the DCount number to a variable before
starting the loop, rather than each time through the loop.

Why the < operator? I would have expected = or >. It will be less than from
the start, unless I am misreading something.

I put together a simple database with one table (tblTest). It has two fields:
a number field TestID and a text field TestText. The values are as follows:

TestID TestText
1 First
2 Second
3 Third

Private Sub Command6_Click()

Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim ctl As Control

strSQL = "SELECT TestID, TestText FROM tblTest"

' Clear the controls
For Each ctl In Me.Controls
Select Case ctl.Tag
Case "txt"
ctl.Value = Null
Case "lbl"
ctl.Caption = ""
End Select
Next ctl

rs.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

For Each ctl In Me.Controls
If ctl.Name = "Text" & rs("TestID") Then
ctl.Value = "Text box " & rs("TestID")
rs.MoveNext
If rs.EOF Then Exit For
End If
Next ctl

rs.MoveFirst

For Each ctl In Me.Controls
If ctl.Name = "Label" & rs("TestID") Then
ctl.Caption = rs("TestText")
rs.MoveNext
If rs.EOF Then Exit Sub
End If

Next ctl

End Sub

There may be a way to avoid having to loop through the recordset and the
controls collection several times, but I couldn't find it in my brief search.
I realize this is not exactly what you need to do, but it gives the idea of
looping through the records and controls. Since the recordset is limited to
records labeled 1, 2, 3 etc. there seems to be no need to do more than check
for EOF. No need for a record count, in other words. You should be able to
hide and unhide controls, although I haven't shown it here. I'm trying to
show a general approach, and will leave it to you to apply to your situation.

I would just point out further that your allocation order seems to be based
on whatever the order of records is before you set the value. If it was me I
would probably set the value when I create the record, or maybe use a ranking
query:
http://allenbrowne.com/ranking.html

If i = RsGetSport("allocationorder") Then
Controls("opt" & i).OptionValue = RsGetSport("st_id")
Controls("lbl" & i).Caption = RsGetSport("Sport Type")
End If
Exit Do
i = i + 1
Loop
Loop Until i = DCount("UserAllocationID", "tbl_userallocation", "UserID=" &
Me.UserID)

Morning BruceM

Thanks for all of this, I know it's a difficult one to get your head around.

So in answer to your questions.
The option group has 10 option buttons and labels. They are named opt1, opt2
etc etc and the labels are lbl1, lbl2 etc etc.

On the form_open event I use the following code to show or hide the relevant
number of option buttons

For i = 1 To DCount("UserAllocationID", "tbl_userallocation", "UserID=" &
Me.UserID)
Controls("opt" & i).Visible = True
Next i

For i = (DCount("UserAllocationID", "tbl_userallocation", "UserID=" &
Me.UserID) + 1) To 10
Controls("opt" & i).Visible = False
Next i

I've now created a query (I agree it keeps things tidier, so thank you), but
the problem I have is I cant get the loop to work and pass a value to the
optionvalue of the corresponding button or the text to the corresponding
label caption.

This is my current loop

Set RsGetSport = New ADODB.Recordset
SQLStr = "SELECT * FROM qry_userallocation WHERE UserID=" & Me.UserID
RsGetSport.Open SQLStr, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic, adCmdText

Do
i = 1
Do Until i < DCount("UserAllocationID", "tbl_userallocation", "UserID=" &
Me.UserID)

If i = RsGetSport("allocationorder") Then
Controls("opt" & i).OptionValue = RsGetSport("st_id")
Controls("lbl" & i).Caption = RsGetSport("Sport Type")
End If
Exit Do
i = i + 1
Loop
Loop Until i = DCount("UserAllocationID", "tbl_userallocation", "UserID=" &
Me.UserID)

RsGetSport.Close
Set RsGetSport = Nothing

The order is set by our allocations people when they take bookings. It's set
by the following code:

SQLStr = "SELECT * FROM tbl_userallocation WHERE UserID=" & Me.UserID
RsUpdateAllocation.Open SQLStr, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic, adCmdText

j = 1
Do Until RsUpdateAllocation.EOF
RsUpdateAllocation("AllocationOrder") = j
RsUpdateAllocation.Update
RsUpdateAllocation.MoveNext
j = j + 1
Loop
RsUpdateAllocation.Close

It still seems to me that RsGetSport('st_id") is a fixed value through each
loop.

[quoted text clipped - 99 lines]

.

--



.

 

[James Frater]

BrueM,

Completely ignore me, I was being very very slow on the uptake.

Thank you so much for all of your help.

Happy New Year

James

BruceM,

Hope you had a good break over Christmas? I had a rather nasty bout of
man-flu so spend most of it hidden under a duvet!

Thank you so so much for below makes so much more sense set out like that.
One thing however I can't seem to do it clear the value for the controls as
setting it to "Null" gives me the error message "Run-Time error '13': Type
mismatch"

Thank you

James

One thing I see is that this line may not have the intended effect:
Do Until i < DCount...

Another point is that I would assign the DCount number to a variable before
starting the loop, rather than each time through the loop.

Why the < operator? I would have expected = or >. It will be less than from
the start, unless I am misreading something.

I put together a simple database with one table (tblTest). It has two fields:
a number field TestID and a text field TestText. The values are as follows:

TestID TestText
1 First
2 Second
3 Third

Private Sub Command6_Click()

Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim ctl As Control

strSQL = "SELECT TestID, TestText FROM tblTest"

' Clear the controls
For Each ctl In Me.Controls
Select Case ctl.Tag
Case "txt"
ctl.Value = Null
Case "lbl"
ctl.Caption = ""
End Select
Next ctl

rs.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

For Each ctl In Me.Controls
If ctl.Name = "Text" & rs("TestID") Then
ctl.Value = "Text box " & rs("TestID")
rs.MoveNext
If rs.EOF Then Exit For
End If
Next ctl

rs.MoveFirst

For Each ctl In Me.Controls
If ctl.Name = "Label" & rs("TestID") Then
ctl.Caption = rs("TestText")
rs.MoveNext
If rs.EOF Then Exit Sub
End If

Next ctl

End Sub

There may be a way to avoid having to loop through the recordset and the
controls collection several times, but I couldn't find it in my brief search.
I realize this is not exactly what you need to do, but it gives the idea of
looping through the records and controls. Since the recordset is limited to
records labeled 1, 2, 3 etc. there seems to be no need to do more than check
for EOF. No need for a record count, in other words. You should be able to
hide and unhide controls, although I haven't shown it here. I'm trying to
show a general approach, and will leave it to you to apply to your situation.

I would just point out further that your allocation order seems to be based
on whatever the order of records is before you set the value. If it was me I
would probably set the value when I create the record, or maybe use a ranking
query:
http://allenbrowne.com/ranking.html

If i = RsGetSport("allocationorder") Then
Controls("opt" & i).OptionValue = RsGetSport("st_id")
Controls("lbl" & i).Caption = RsGetSport("Sport Type")
End If
Exit Do
i = i + 1
Loop
Loop Until i = DCount("UserAllocationID", "tbl_userallocation", "UserID=" &
Me.UserID)

Morning BruceM

Thanks for all of this, I know it's a difficult one to get your head around.

So in answer to your questions.
The option group has 10 option buttons and labels. They are named opt1, opt2
etc etc and the labels are lbl1, lbl2 etc etc.

On the form_open event I use the following code to show or hide the relevant
number of option buttons

For i = 1 To DCount("UserAllocationID", "tbl_userallocation", "UserID=" &
Me.UserID)
Controls("opt" & i).Visible = True
Next i

For i = (DCount("UserAllocationID", "tbl_userallocation", "UserID=" &
Me.UserID) + 1) To 10
Controls("opt" & i).Visible = False
Next i

I've now created a query (I agree it keeps things tidier, so thank you), but
the problem I have is I cant get the loop to work and pass a value to the
optionvalue of the corresponding button or the text to the corresponding
label caption.

This is my current loop

Set RsGetSport = New ADODB.Recordset
SQLStr = "SELECT * FROM qry_userallocation WHERE UserID=" & Me.UserID
RsGetSport.Open SQLStr, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic, adCmdText

Do
i = 1
Do Until i < DCount("UserAllocationID", "tbl_userallocation", "UserID=" &
Me.UserID)

If i = RsGetSport("allocationorder") Then
Controls("opt" & i).OptionValue = RsGetSport("st_id")
Controls("lbl" & i).Caption = RsGetSport("Sport Type")
End If
Exit Do
i = i + 1
Loop
Loop Until i = DCount("UserAllocationID", "tbl_userallocation", "UserID=" &
Me.UserID)

RsGetSport.Close
Set RsGetSport = Nothing

The order is set by our allocations people when they take bookings. It's set
by the following code:

SQLStr = "SELECT * FROM tbl_userallocation WHERE UserID=" & Me.UserID
RsUpdateAllocation.Open SQLStr, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic, adCmdText

j = 1
Do Until RsUpdateAllocation.EOF
RsUpdateAllocation("AllocationOrder") = j
RsUpdateAllocation.Update
RsUpdateAllocation.MoveNext
j = j + 1
Loop
RsUpdateAllocation.Close

It still seems to me that RsGetSport('st_id") is a fixed value through each
loop.
[quoted text clipped - 99 lines]

.

--



.