two list boxes

[jordanh]

Hi,

I'm not sure if this has been answered before... I've looked back quite far and I can't see an answer. I'm sure my problem will be very simple for someone to solve...

I have two drop down boxes to select items from a table; the data looks a little like this,

R1 R2 Sys Sub
1 0 A a
1 1 A b
1 2 A c
1 3 A d
1 4 A e
2 0 B f
2 1 B g
2 2 B h
2 3 B i
3 0 C j

I have the first drop down box selecting all unique values of R1, so a list of A,B,C,D,E,F,G.... ( SELECT DISTINCT Sheet1.R1, Sheet1.System FROM Sheet1 ORDER BY R1; )

The second drop down box uniquely selects R2, so lists a,b,c,d,e,f,g... ( SELECT DISTINCT Sheet1.R2, Sheet1.R1, Sheet1.Subsystem FROM Sheet1 ORDER BY R1 ASC, R2 ASC; ) when you select from the first drop down box, the second box becomes focused on whichever sub corresponds to R1=selected and R2=0.

The thing is I want to LIMIT the second drop down list to only the R2 at a given R1.

does that make sense? basically I want to filter the list in the second box. Is there some way I can build the query for the second list box including a variable? I know exactly how I'd do it using mySQL and PHP but until now I've avoided access.

If someone could help I would be most grateful.

Thanks

Jordan

 

[LittleSmudge]

I think what you are asking for is then

( SELECT DISTINCT Sheet1.R2, Sheet1.R1, Sheet1.Subsystem FROM Sheet1 WHERE Shee1.R1 = lstBox1 ORDER BY R1 ASC, R2 ASC; )




G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! :-D

 

[LittleSmudge]

oops - Hit the submit button too early.

I mean to add - I only say I think this is what you want because your words/description of what you are doing does not tie in with the SQL that you have posted.

all unique values of R1 are 1, 2, 3.
They are not .. .. a list of A,B,C,D,E,F,G.. .. as you posted.

Also
( SELECT DISTINCT Sheet1.R1, Sheet1.System FROM Sheet1 ORDER BY R1; )

will produce a two column list of DISTINCT R1, System pairs
Eg.
R1 Sys
1 A
2 B
3 C

Which is not ( necessarily ) the same as all unique values of R1 - If it is the same then you need to take a long serious look at your data normalisation.


'ope-that-'elps.



G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! :-D

 

[jordanh]

Thanks for replying.

"all unique values of R1 are 1, 2, 3.
They are not .. .. a list of A,B,C,D,E,F,G.. .. as you posted."

sorry, severe lack of clarity on my part. The combo box has 2 columns, R1 and Sys, R1 has width 0 so it only displays a list of A,B,C,D... in reality, R1 and Sys are connected.

"I think what you are asking for is then

( SELECT DISTINCT Sheet1.R2, Sheet1.R1, Sheet1.Subsystem FROM Sheet1 WHERE Shee1.R1 = lstBox1 ORDER BY R1 ASC, R2 ASC; ) "

Ahhh. Thats how you do it.

The only problem I'm having now is that if I select a different item in the first list box the second one doesn't change.

I'm going to need something in the AfterInsert box aren't I... but what???

Thanks again for your help.

Jordan

 

[jordanh]

hi,

I solved this problem by using requery. I found a very helpful artcile in the FAQ (I only noticed it existed after posting my last reply).

I have further problems though!

I am using the combined combo boxes to navigate a large number of records (3500 in all) and I need some way of selecting a record and opening it without altering any field's values. I will obviously have to include an edit button at some point.

I have made each combo box 'locked' to protect the table data but now I cannot select any records. This means that using my current scripting changes the values of R1 when I select a record, throwing the whole thing into chaos.

My current queries for each box are...

SELECT DISTINCT Query1.R1, Query1.System FROM Query1 ORDER BY R1;

for the first box, and

SELECT DISTINCT Query1.R2, Query1.R1, Query1.Subsystem FROM Query1 WHERE Query1.R1 = SysSel ORDER BY R1 ASC, R2 ASC;

For the second. Does anyone have any ideas (I have checked the FAQs this time!).

Many thanks in advance,

Jordan

 

[LittleSmudge]

To protect the data in the table you do not need to 'lock' the control you need to UNBIND the control. Blank off the combo box's ControlSource property. It then becomes an unbound control that is able to display the selected data from the list but, not being linked to any field in the table - will not change anything.


'ope- that-'elps.




G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! :-D

 

[jordanh]

and that sorts it out.

thank you very much.

Jordan

PS : I may well have more questions on this project, it's a bit of a steep learning curve, please have patience with me!