Combo box show values based on value in Option group?

[Guest]

Hello,

I would like to limit what is shown in the drop-down list of a combo box
based on the value that is checked off in an Option group.

Right now, I have the 'Row Source:' of my combo box set to:

SELECT LU_familymember.familymember
FROM LU_familymember, tFamilyHxDisease
WHERE
((([LU_familymember]![familydegree])=[Forms]![fFamilyHxDisease]![FrameRelDegree]));

where 'LU_familymember' is the lookup table I want to supply select family
members (field: 'familymember') in my combo box based on what is chosen in my
Option group field ('FrameRelDegree') on the current form (fFamilyHxDisease).
In the AfterUpdate() Event of 'FrameRelDegree', I have the following to
requery my combo box:

Private Sub FrameRelDegree_AfterUpdate()
Me!CbxRelType.Requery
End Sub

where 'CbxRelType' is the combo box which I would want to limit which values
are shown, based on what is selected from the Option Group (FrameRelDegree')
on the same form.

How do I make this happen? Thank you.

 

[Guest]

Pat:

The value of an option group is a number. Each option button has an
OptionValue property which is assigned as the value of the option group when
its selected. In the RowSource of your combo box therefore the FamilyDegree
value would have to be a number corresponding to the OptionValue of the
selected option button.

If the values of FamilyDegree are not numbers then you would need to either
add a column to the FamilyMember table with the relevant numbers if the table
contains unique FamilyDegree values and use that column in the WHERE clause,
or if not, create a separate FamilyDegrees table, if you don't have one
already, with unique rows with two columns containing the familyDegree and
Numeric values and join that table to familyMember in the SQL statement.

Ken Sheridan
Stafford, England

 

[Pat Hartman\(MVP\)]

In addition to what Ken said, your query is incorrect. You are including a
table from which no column is selected and which is not properly joined to
the first table. This query will produce a Cartesian product which is most
likely not what you are looking for. Try this:

SELECT LU_familymember.familymember
FROM LU_familymember
WHERE
[LU_familymember].[familydegree] =
[Forms]![fFamilyHxDisease]![FrameRelDegree];

Pat:

The value of an option group is a number. Each option button has an
OptionValue property which is assigned as the value of the option group
when
its selected. In the RowSource of your combo box therefore the
FamilyDegree
value would have to be a number corresponding to the OptionValue of the
selected option button.

If the values of FamilyDegree are not numbers then you would need to
either
add a column to the FamilyMember table with the relevant numbers if the
table
contains unique FamilyDegree values and use that column in the WHERE
clause,
or if not, create a separate FamilyDegrees table, if you don't have one
already, with unique rows with two columns containing the familyDegree and
Numeric values and join that table to familyMember in the SQL statement.

Ken Sheridan
Stafford, England

Hello,

I would like to limit what is shown in the drop-down list of a combo box
based on the value that is checked off in an Option group.

Right now, I have the 'Row Source:' of my combo box set to:

SELECT LU_familymember.familymember
FROM LU_familymember, tFamilyHxDisease
WHERE
((([LU_familymember]![familydegree])=[Forms]![fFamilyHxDisease]![FrameRelDegree]));

where 'LU_familymember' is the lookup table I want to supply select
family
members (field: 'familymember') in my combo box based on what is chosen
in my
Option group field ('FrameRelDegree') on the current form
(fFamilyHxDisease).
In the AfterUpdate() Event of 'FrameRelDegree', I have the following to
requery my combo box:

Private Sub FrameRelDegree_AfterUpdate()
Me!CbxRelType.Requery
End Sub

where 'CbxRelType' is the combo box which I would want to limit which
values
are shown, based on what is selected from the Option Group
(FrameRelDegree')
on the same form.

How do I make this happen? Thank you.

 

[Guest]

Fantastic, Pat! I don't know why I was missing the fact that the extra table
in my query was never going to fly. Great to have that second pair of eyes
sometimes. Thanks again!
--
Pat Dools

In addition to what Ken said, your query is incorrect. You are including a
table from which no column is selected and which is not properly joined to
the first table. This query will produce a Cartesian product which is most
likely not what you are looking for. Try this:

SELECT LU_familymember.familymember
FROM LU_familymember
WHERE
[LU_familymember].[familydegree] =
[Forms]![fFamilyHxDisease]![FrameRelDegree];

Pat:

The value of an option group is a number. Each option button has an
OptionValue property which is assigned as the value of the option group
when
its selected. In the RowSource of your combo box therefore the
FamilyDegree
value would have to be a number corresponding to the OptionValue of the
selected option button.

If the values of FamilyDegree are not numbers then you would need to
either
add a column to the FamilyMember table with the relevant numbers if the
table
contains unique FamilyDegree values and use that column in the WHERE
clause,
or if not, create a separate FamilyDegrees table, if you don't have one
already, with unique rows with two columns containing the familyDegree and
Numeric values and join that table to familyMember in the SQL statement.

Ken Sheridan
Stafford, England

Hello,

I would like to limit what is shown in the drop-down list of a combo box
based on the value that is checked off in an Option group.

Right now, I have the 'Row Source:' of my combo box set to:

SELECT LU_familymember.familymember
FROM LU_familymember, tFamilyHxDisease
WHERE
((([LU_familymember]![familydegree])=[Forms]![fFamilyHxDisease]![FrameRelDegree]));

where 'LU_familymember' is the lookup table I want to supply select
family
members (field: 'familymember') in my combo box based on what is chosen
in my
Option group field ('FrameRelDegree') on the current form
(fFamilyHxDisease).
In the AfterUpdate() Event of 'FrameRelDegree', I have the following to
requery my combo box:

Private Sub FrameRelDegree_AfterUpdate()
Me!CbxRelType.Requery
End Sub

where 'CbxRelType' is the combo box which I would want to limit which
values
are shown, based on what is selected from the Option Group
(FrameRelDegree')
on the same form.

How do I make this happen? Thank you.