Listbox loaded from previous listbox value

[Guest]

I have 2 list boxes.
The 1st loads from a query.
I need to load the 2nd list box based on the bound filed #1 of the 1st
listbox.
1st list box is a provider list with providerID
2nd list box is a membe list with providerID and MemberName
Need all members displayed (2nd listbox) for the provider selected in the
1st list box.
Having probs with the boundfield and query to get the results.
TIA
Jay

 

[Al Camp]

The query behind List2 should have a citeria in the ProviderID like this.
(use your own names)
= Forms!frmYourFormName!List1
That will filter out all ProviderIDs but the one that matches the current
value in List1.

Note: On the AfterUpdate event of List1, Requery List2 to force it to
re-synch to that List1 value.

 

[Guest]

Getting there..
Does list1 need to ID the bound column for List2
Any good MS sites to work through this one..

 

[Guest]

Al, any idea on why the dialog box keeps appearing and asking me for the ID
number. Once I type it in, it all works great. But for some reason the value
is not getting passed to the List2 and it prompts me for it.
Thanks, you have been helpfull

 

[Al Camp]

JE,
What is the exact name of your form?
What is the exact name of Listbox1?
What is the exact criteria you're using against ProductID in the List2
query ?

JE, when you have coding problems, it's advisable to include the code...
like the criteria you've tried so far.

In your ProductID criteria, you don't use the name ProductID as a
criteria, you use the name of the listbox (list1) that contains the
ProductID on your form.
= Forms!frmYourFormName!List1
List1 should contain the ProductID you've selected... make sure you have
the proper column bound to the control!

We'll get it... hang in there.

 

[Guest]

Al,

Thanks for taking the time to help me work through this one...

Form Name = HEDIS07MainForm

List1 Name = Provider_Practice_Location_ListBox

SQL in Provider_Practice_Location_ListBox = SELECT
Provider_Practice_Location_Name.PRVDR_FRWRD_ID,
Provider_Practice_Location_Name.GROUP_PRCTC_LCTN_NM,
Provider_Practice_Location_Name.TAX_ID FROM Provider_Practice_Location_Name
ORDER BY Provider_Practice_Location_Name.GROUP_PRCTC_LCTN_NM;

Bound Column Is Set To : 1 (PRVDR_FRWRD_ID)

List2 Name = second_list_box

SQL In second_list_box = SELECT [AQITST1_mbr Query].PRVDR_FRWRD_ID,
[AQITST1_mbr Query].MBR_UNECRYPTD_LAST_NM, [AQITST1_mbr
Query].MBR_UNECRYPTD_FIRST_NM, [AQITST1_mbr Query].MBR_UNECRYPTD_FRWRD_ID
FROM [AQITST1_mbr Query] WHERE ((([AQITST1_mbr
Query].PRVDR_FRWRD_ID)=Forms!HEDIS07MainForm!Provider_Practice_Location_ListBox));

Bound Column is set to 1 (PRVDR_FRWRD_ID)

There is no code in the VB procedures, it is all in the Properties window.

The above config works, it just prompts me for the value of the
PRVDR_FRWRD_ID, one I enter the value it returns the correct rows. So it
knows it is a parm value but must not have 'the value' to resolve.

Thanks again..

Jay

 

[Al Camp]

JE,
I see several problems...
If you send me the .mdb file I'll try to take a look at it. You can send
it to me through my website listed below... via the Contact button.
Include a good description of where the problem is...
No promises, but I'll try... and everything will be confidential, and no
charge.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Al,

Thanks for taking the time to help me work through this one...

Form Name = HEDIS07MainForm

List1 Name = Provider_Practice_Location_ListBox

SQL in Provider_Practice_Location_ListBox = SELECT
Provider_Practice_Location_Name.PRVDR_FRWRD_ID,
Provider_Practice_Location_Name.GROUP_PRCTC_LCTN_NM,
Provider_Practice_Location_Name.TAX_ID FROM
Provider_Practice_Location_Name
ORDER BY Provider_Practice_Location_Name.GROUP_PRCTC_LCTN_NM;

Bound Column Is Set To : 1 (PRVDR_FRWRD_ID)

List2 Name = second_list_box

SQL In second_list_box = SELECT [AQITST1_mbr Query].PRVDR_FRWRD_ID,
[AQITST1_mbr Query].MBR_UNECRYPTD_LAST_NM, [AQITST1_mbr
Query].MBR_UNECRYPTD_FIRST_NM, [AQITST1_mbr Query].MBR_UNECRYPTD_FRWRD_ID
FROM [AQITST1_mbr Query] WHERE ((([AQITST1_mbr
Query].PRVDR_FRWRD_ID)=Forms!HEDIS07MainForm!Provider_Practice_Location_ListBox));

Bound Column is set to 1 (PRVDR_FRWRD_ID)

There is no code in the VB procedures, it is all in the Properties window.

The above config works, it just prompts me for the value of the
PRVDR_FRWRD_ID, one I enter the value it returns the correct rows. So it
knows it is a parm value but must not have 'the value' to resolve.

Thanks again..

Jay

JE,
What is the exact name of your form?
What is the exact name of Listbox1?
What is the exact criteria you're using against ProductID in the List2
query ?

JE, when you have coding problems, it's advisable to include the
code...
like the criteria you've tried so far.

In your ProductID criteria, you don't use the name ProductID as a
criteria, you use the name of the listbox (list1) that contains the
ProductID on your form.
= Forms!frmYourFormName!List1
List1 should contain the ProductID you've selected... make sure you
have
the proper column bound to the control!

We'll get it... hang in there.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions