dynamic listbox???

[craigey]

Hi there,

I'm working on a database for a youth organisation, but have hit yet another hurdle.

I've created a Form called Contacts which has 2 listboxes, and a couple of text boxes (for names etc). These all work ok. Except one of the listbox's has a lot of options to choose from. One of the listboxes is called Regions and the other called wings. There are about 5 wings to a region and about 6 regions, ehich makes the options box called Wings have about 30 options. How can i get the options reduced depending on which region has been chosen.

For example if Scotland & Northern Ireland Region was chosen as a region. You would only see the following 6 options; Aberdeen & North East Scotland Wing, Dundee & Central Scotland Wing, Edinburgh & South Scotland Wing, Glasgow & West Scotland Wing, Highland Wing, Northern Ireland Wing. Also is it possible to do the same in reverse, IE someone has skipped the Region Field and chooses Highland wing in the wing field, the Scotland & Northern Ireland Region is automatically filled in the region field.

Can someone guide me through this Please.


The following are all the options for the listboxes

Scotland & Northern Ireland Region
Aberdeen & North East Scotland Wing
Dundee & Central Scotland Wing
Edinburgh & South Scotland Wing
Glasgow & West Scotland Wing
Highland Wing
Northern Ireland Wing

Northern Region
Durham & Northumberland Wing
Central & East Yorkshire Wing
Cumbria & North Lancashire Wing
East Cheshire & South Manchester Wing
East Lancashire
South and West Yorkshire Wing

Central & East Region
Bedfordshire & Cambridgeshire Wing
Lincolnshire Wing
Hertfordshire & Bucks Wing
Norfolk & Suffolk Wing
South Midlands Wing
East Midlands Wing
Warwick & Birmingham Wing

London & South East Region
Kent Wing
London Wing
Middlesex Wing
Surrey Wing
Sussex Wing
West Essex Wing
East Essex Wing

Wales & West Region
No 1 Welsh Wing
No 2 Welsh Wing
No 3 Welsh Wing
West Mercian Wing
Staffordshire Wing
Merseyside Wing

South West Region
Bristol & Gloucestershire Wing
Devonshire Wing
Dorset & Wilts Wing
Hampshire & Isle of Wight Wing
Somerset Wing
Plymouth & Cornwall Wing
Thames Valley Wing




Regards Craigey

 

[KenReay]

Hi

Make the reowsource of the second combo box have a criteria which depends on the first combo box

In the got focus event of the second combo box, put cboMyCombo2.requery where cboMyCombo2 is the name of you second combo box. Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[craigey]

Thanks for the info,

but how do i "Make the reowsource of the second combo box have a criteria which depends on the first combo box"

I don't have a clue of where to start, do i need to create a table with all the field names as regions and for each region have the wings. Would I then need to create a relationship ????

Or do i use VB with something like

if [region]="South West Region" Then [wing]="Bristol & Gloucestershire Wing";"Devonshire Wing";"Dorset & Wilts Wing";"Hampshire & Isle of Wight Wing";"Somerset Wing";"Plymouth & Cornwall Wing";"Thames Valley Wing"

Else If [Region]="Wales & West Region" Then [wing]="No 1 Welsh Wing";"No 2 Welsh Wing";"No 3 Welsh Wing"


etc etc etc



I have absolutely no idea where to start with this.

Please can i have a little more detail. I do apreciate all answers that I get, but i don't know enough about access to know how to do what was suggested.

Thanks

 

[craigey]

I've been tinkering for about 2 hours and have so far got one list box reading from a table that i created. I'm still having trouble getting the 2nd listbox to select only certain fields depending on the field chosen by the region.

My table looks like this (bold is for the field name)

Scotland & Northern Ireland Region         Northern Region

Aberdeen & North East Scotland Wing Durham & Northumberland Wing
Dundee & Central Scotland Wing Central & East Yorkshire Wing
Edinburgh & South Scotland Wing Cumbria & North Lancashire Wing
Glasgow & West Scotland Wing East Cheshire & South Manchester Wing
Highland Wing East Lancashire
Northern Ireland Wing South and West Yorkshire Wing

 

[craigey]

Ok, Someone give me a star!!!!

I got it working!

In my region field i have a value list of "Northern Region";"South West Region";"Wales & West Region";"London & South East Region";"London & South East Region";"Central & East Region"

In the Wing list I have the following code in the on got focus (the code is code, not macro or expression)

Private Sub Wing_GotFocus()
If [Region] = "Scotland & Northern Ireland Region" Then [Wing].RowSource = "'Aberdeen & North East Scotland Wing';'Edinburgh & South Scotland Wing';'Glasgow & West Scotland Wing';'Highland Wing';'Northern Ireland Wing'"
If [Region] = "Northern Region" Then [Wing].RowSource = "'Durham & Northumberland Wing';'Central & East Yorkshire Wing';'Cumbria & North Lancashire Wing';'East Cheshire & South Manchester Wing';'East Lancashire';'South and West Yorkshire Wing'"
If [Region] = "Central & East Region" Then [Wing].RowSource = "'Bedfordshire & Cambridgeshire Wing';'Lincolnshire Wing';'Hertfordshire & Bucks Wing';'Norfolk & Suffolk Wing';'South Midlands Wing';'East Midlands Wing';'Warwick & Birmingham Wing'"
If [Region] = "London & South East Region" Then [Wing].RowSource = "'Kent Wing';'London Wing';'Middlesex Wing';'Surrey Wing';'Sussex Wing';'West Essex Wing';'East Essex Wing'"
If [Region] = "Wales & West Region" Then [Wing].RowSource = "'No 1 Welsh Wing';'No 2 Welsh Wing';'No 3 Welsh Wing';'West Mercian Wing';'Staffordshire Wing';'Merseyside Wing'"
If [Region] = "South West Region" Then [Wing].RowSource = "'Bristol & Gloucestershire Wing';'Devonshire Wing';'Dorset & Wilts Wing';'Hampshire & Isle of Wight Wing';'Somerset Wing';'Plymouth & Cornwall Wing';'Thames Valley Wing'"
End Sub

In the Row source type i have set it to value list

Just though i'd post this for anyone who might find it usefull.

I've noticed though, that if the wing is selected first and you change the region to one that is not listed in the wing that you just chose. You can still move to another record, and so would have incorrect information. I assume this is because you haven't focused on the wing field after changing the region. How would i code a messagebox to come up if the wing and the region do not match?

Regards

Craigey!

 

[juschuma]

I just did this...it might be easier than hardcoding everything...try this code...

1) "lbox_Division" is the first listbox
2) "lbox_ROC" is the second listbox which changes depending on what is selected in lbox_Division



Dim intlp As Integer, strwhr As String

For intlp = 0 To Me.lbox_Division.ListCount - 1
If Me.lbox_Division.Selected(intlp) Then
If Len(strwhr) > 0 Then
strwhr = strwhr & " or [tbl_DivisionROCIntersection].[Division] = " & "'" & Me.lbox_Division.Column(0, intlp) & "'"
Else
strwhr = "[tbl_DivisionROCIntersection].[Division] = " & "'" & Me.lbox_Division.Column(0, intlp) & "'"
End If
End If
Next intlp

strwhr = strwhr & ";"

Me.lbox_ROC.RowSource = "SELECT DISTINCT [tbl_DivisionROCIntersection].[ROC] FROM tbl_DivisionROCIntersection where " & strwhr