Limit list box by two columns of another list box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Using the following Row Source I can populate a list box from the bound
column of another list box.

SELECT pc.CarrCode, pc.CarrPlanCode, pc.BenefitCode, pc.coverage_ind,
pc.coverage_notes FROM dbo_PlanCoverage AS pc WHERE pc.CarrCode=[lstCoverage]
ORDER BY [BenefitCode];

However, what I really need is to limit the second list box by two columns
of the first list box. Alas, the attempt shown below does not work:

SELECT pc.CarrCode, pc.CarrPlanCode, pc.BenefitCode, pc.coverage_ind,
pc.coverage_notes FROM dbo_PlanCoverage AS pc WHERE pc.CarrCode=[lstCoverage]
AND pc.CarrPlanCode = [lstCoverage.Column(1)] ORDER BY [BenefitCode];

I would appreciate somebody explaining the correct syntax. Thank you.
 
Graham,

I believe your reference covers my first example, but not the second, where
the list box value needs to be limited by links to two columns (not just one)
of the other list box. Thanks...

Graham R Seach said:
Richard,

The following applies to combo boxes, but the principle is equally
applicable to list boxes:
http://www.pacificdb.com.au/MVP/Code/ComboRS.htm

Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia
---------------------------

richardb said:
Using the following Row Source I can populate a list box from the bound
column of another list box.

SELECT pc.CarrCode, pc.CarrPlanCode, pc.BenefitCode, pc.coverage_ind,
pc.coverage_notes FROM dbo_PlanCoverage AS pc WHERE
pc.CarrCode=[lstCoverage]
ORDER BY [BenefitCode];

However, what I really need is to limit the second list box by two columns
of the first list box. Alas, the attempt shown below does not work:

SELECT pc.CarrCode, pc.CarrPlanCode, pc.BenefitCode, pc.coverage_ind,
pc.coverage_notes FROM dbo_PlanCoverage AS pc WHERE
pc.CarrCode=[lstCoverage]
AND pc.CarrPlanCode = [lstCoverage.Column(1)] ORDER BY [BenefitCode];

I would appreciate somebody explaining the correct syntax. Thank you.
Click to expand...
Click to expand...
 
I believe I have resolved this by adding a column of zero width to the first
list box consisting of both columns that I want to have restrict the second
list box, e.g. RTrim(pc.CarrCode) & RTrim(pc.CarrPlanCode) and setting this
as the bound column. Then I changed the WHERE clause of the Row Source in the
second list box to match this.

Now I have a follow-up question. When the second list box fails to find a
select "hit" and so is blank, I want it to display a message. It might be "No
policy restrictions found." Any thoughts on that?

Graham R Seach said:
Richard,

The following applies to combo boxes, but the principle is equally
applicable to list boxes:
http://www.pacificdb.com.au/MVP/Code/ComboRS.htm

Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia
---------------------------

richardb said:
Using the following Row Source I can populate a list box from the bound
column of another list box.

SELECT pc.CarrCode, pc.CarrPlanCode, pc.BenefitCode, pc.coverage_ind,
pc.coverage_notes FROM dbo_PlanCoverage AS pc WHERE
pc.CarrCode=[lstCoverage]
ORDER BY [BenefitCode];

However, what I really need is to limit the second list box by two columns
of the first list box. Alas, the attempt shown below does not work:

SELECT pc.CarrCode, pc.CarrPlanCode, pc.BenefitCode, pc.coverage_ind,
pc.coverage_notes FROM dbo_PlanCoverage AS pc WHERE
pc.CarrCode=[lstCoverage]
AND pc.CarrPlanCode = [lstCoverage.Column(1)] ORDER BY [BenefitCode];

I would appreciate somebody explaining the correct syntax. Thank you.
Click to expand...
Click to expand...
 
For anyone who's interested, here's my answer to my follow-up question of
placing a special message when the second list box is empty. I placed a label
"behind" the second list box with my message. Then I added this code at the
appropriate places:

Private Sub lstCoverage_Click()
Dim lst As ListBox
Set lst = lstBenefits
With lst
.Requery
.Visible = .ListCount
End With
End Sub



richardb said:
I believe I have resolved this by adding a column of zero width to the first
list box consisting of both columns that I want to have restrict the second
list box, e.g. RTrim(pc.CarrCode) & RTrim(pc.CarrPlanCode) and setting this
as the bound column. Then I changed the WHERE clause of the Row Source in the
second list box to match this.

Now I have a follow-up question. When the second list box fails to find a
select "hit" and so is blank, I want it to display a message. It might be "No
policy restrictions found." Any thoughts on that?

Graham R Seach said:
Richard,

The following applies to combo boxes, but the principle is equally
applicable to list boxes:
http://www.pacificdb.com.au/MVP/Code/ComboRS.htm

Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia
---------------------------

richardb said:
Using the following Row Source I can populate a list box from the bound
column of another list box.

SELECT pc.CarrCode, pc.CarrPlanCode, pc.BenefitCode, pc.coverage_ind,
pc.coverage_notes FROM dbo_PlanCoverage AS pc WHERE
pc.CarrCode=[lstCoverage]
ORDER BY [BenefitCode];

However, what I really need is to limit the second list box by two columns
of the first list box. Alas, the attempt shown below does not work:

SELECT pc.CarrCode, pc.CarrPlanCode, pc.BenefitCode, pc.coverage_ind,
pc.coverage_notes FROM dbo_PlanCoverage AS pc WHERE
pc.CarrCode=[lstCoverage]
AND pc.CarrPlanCode = [lstCoverage.Column(1)] ORDER BY [BenefitCode];

I would appreciate somebody explaining the correct syntax. Thank you.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top