combobox selection to open another combo box 1

[redbay]

I Have a table with three fields Zone, Sector & Location Set out as follows

Zone Sector Location
1 Ground Floor Office 1
1 Ground Floor Office 2
2 First Floor Office 4
3 Second Floor Office 5
3 Second Floor Office 6 Etc

I want to have on my form a combobox where the user can select an option, say ground floor, that then opens a second combobox showing all the locations that are available on the ground floor. I've looked at several other posts regarding this subject but dont seem to be getting very far.

Can anyone help please. I am quite ok in VBA but not in SQL statements

 

[KenReay]

Hi

see FAQ section of this forum, under combo boxes, I have a FAQ there which explains how to do this

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[redbay]

Does that mean that i should have 2 tables then ?

 

[KenReay]

Hi

I would have thought you would have three tables

tblZone
tblSector
tblLocation

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[redbay]

Ken

I have tried this and i am getting problems, when i make a selection from the first combobox the second one is blank this is how i have it set - i am only using 2 table

TblSector
SectorID PK
Sector Text

tblLocation
ID PK
Sector Text
Location Text

cboSector Row Source:
SELECT tblSector.SectorID, tblSector.Sector
FROM tblSector
ORDER BY tblSector.Sector

cboLocation Row Source:
SELECT tblLocation.Sector,tblLocation.Location.ID
FROM tblLoaction
WHERE (((tblLocation.ID)=[Forms]![frmLast]![cboSector]))
ORDER BY tblLocation.location

Where am i going wrong alltogether

Thx for replying

 

[KenReay]

Hi

You do not mention any code to requery the second combo box after a selection is made in teh first one, or to prevent user from trying to seelct from second vcombo before making a choice from the first one

do you have any such code, if yes, would you post it ?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[redbay]

Yep Sorry

Private Sub cboSector_AfterUpdate()
cboLocation.Requery
End Sub

 

[KenReay]

Hi

You are using wrong key, see below:

TblSector
SectorID PK
Sector Text

tblLocation
ID PK
Sector Text <- here you are using the text of the sector you should be using SectorId (see below)
Location Text

cboSector Row Source:
SELECT tblSector.SectorID, tblSector.Sector
FROM tblSector
ORDER BY tblSector.Sector

cboLocation Row Source:
SELECT tblLocation.Sector,tblLocation.Location.ID
FROM tblLoaction
WHERE (((tblLocation.ID)=[Forms]![frmLast]![cboSector]))
ORDER BY tblLocation.location
*** here you are doing the where on cboSector, the value of which is the SectorId, Not Sector

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[redbay]

I have altered this now my tblLocation is set as

SectorID Text PK
LocationID A/N
Location Text

cboLocation Row Source is

SELECT tblLocation.Sector,tblLocation.Location.ID
FROM tblLocation
WHERE (((tblLocation.ID)=[Forms]![frmLast]![SectorID]))
ORDER BY tblLocation.location

Now when i make a selection from cboSector i get 4 parameter boxes

Please help again

 

[KenReay]

Hi

from the FAQ:

Combo Box - dependant on another Combo Box
faq702-4289

Combo box dependant on another combo box

It is quite common to have a combo box from which you can choose a list of options, which then causes a second combo box to offer a list of options which are dependant on the first combo box.

The example below illustrates this.

Assumptions

Assume we have two simple tables as below:

Tables

TblManufacturers
LngManufacturerId – Autonumber – PK
StrManufacturerName

TblModels
LngManufacturerId – Long ) PK
LngModelId – Autonumber )
StrModelName

Combo Boxes

We have two combo boxes (cboManufacturer and cboModel) the two combo boxes are on a form (Form2) so

CboManufacturer
RowSource: SELECT tblManufacturers.lngManufacturerId, tblManufacturers.strManufacturerName
FROM tblManufacturers
ORDER BY tblManufacturers.strManufacturerName;

ColumnCount: 2

ColumnWidths: 0;2.54cm

LimitToList: Yes

Events:

Private Sub cboManufacturer_AfterUpdate()
cboModel.Requery
End Sub

CboModel
RowSource: SELECT tblModels.lngModelId, tblModels.strModelName, tblModels.lngManufacturerId
FROM tblModels
WHERE (((tblModels.lngManufacturerId)=[Forms]![Form2]![cboManufacturer]))
ORDER BY tblModels.strModelName;

ColumnCount: 3

ColumnWidths: 0;2.54;0

LimitToList: Yes

Events:
Private Sub cboModel_GotFocus()
If Len(Trim(Nz(cboManufacturer, &quot;&quot & &quot;&quot) = 0 Then
MsgBox &quot;Please Specify Manufacturer first&quot;
cboManufacturer.SetFocus
Else
cboModel.Requery
End If



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[redbay]

Ken

This was the model that i originally used i have checked it all and mine corresponds to yours but i am still getting a blank on my cboLocation

 

[redbay]

Hey Ken

I've done it... at last Thanks for all your help i'll give you a star and i'll post your faq as helpful

Thanks again

 

[redbay]

Ken

One slight problem, in my Main table where i want the results to be displayed i have set a field called Location as a combobox to tbllocation and have set both the combo boxes on a form based on that table (as above) but the results from cbolocation go back into the table based on the SectorID fiels from tblLocation and i wanted the location field,i have tried changing the bound column no to 3 (as its the 3rd field) but no joy....can you help with the next step of this please?

 

[KenReay]

HI

From you earlier posts, the LOcation TAble has only two columns (LocationId, Location), I assume you are trying to store LocationId in your 'main table', so if you have the convention thing, where the recordsource of teh location combo box is SELECT LocationId, Location from tblLocation ORDER BY Location, then .Columns should be 2, and your bound Column should be 1.

It may be easier to send me a copy of what you a have (kenneth.reayREMOVETHIS@talk21.com).



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[redbay]

Ken

I have sent you the email can you let me know if you get it OK

 

[redbay]

Hi Ken

I am unable to send the database via emai. At present i have the correct fields in the table the only problem i now have is that when i make a selection from cboSector i then have the correct results in the cboLocation if i pick the first option thats fine and the results are correct in the table but if i pick the second or third option from cboLocation then it will only show the first option back to the field and the tablebut my location table is set out as this

SectorID LocationID Location
1 13 Office12
1 14 Office13
1 15 Office14
2 16 Arrivals Hall
2 17 Bay8
3 18 Bay9
3 19 CheckIN

and so forth - so the cboLocation will only pick Office12, Arrivals Hall or CheckIn

Thanks for all your responses

 

[ponymc]

Hi

did this problem get resolved as I have the same problem