Query for a Combo Box

[carterlangley]

Hi everyone,



I have two tables, Organisation and Division. One to many relationship (one organisation can have many divisions).
On the organisation form, there is a combo box to select the divisions associated with that organisation. What I'm finding is getting me very confused.

I have an organisation, Aberdeenshire Council, and about 20 other organisations all different. When I drop the combo box down for an organisation which doesn't have any divisions listed in the division table for that organisation, I am getting the divisions listed for Aberdeenshire Council. All the other organisations which also don't have division details listed in the division table also display the divisions for Aberdeenshire Council.

Can somebody please explain to me what I have done wrong?

Organisation Table
Organisation ID autonumber primary
OrganisationName

Division Table
DivisionID autonumber primary
OrganisationID (Lookup option used to establish query)
DivisionName

underlying query in the combo box

SELECT [Division].[DivisionID], [Division].[OrganisationID], [Division].[Division] FROM Division ORDER BY [Division];

Something here is just not looking right. I'm not a programmer, so I can't quite place it but something there doesn't seem right

[ranman256]

when you look at the table tDivision ,does it have [OrganisationID] values?
if you have a form, and pick the combo box for Organisation , the combo box must be bound to the 1st column [OrganisationID]
now that you have that ID, you can filter (the records, or the form) based on that value.

SELECT [Division].[DivisionID], [Division].[OrganisationID], [Division].[Division] FROM Division where [[OrganisationID]=forms!myForm!cboOrg

then it will show only those divisions based on the ID in the combo box.

[Minty]

It sounds like you need or are needing cascading combo boxes - Paul has an excellent guide here; http://www.baldyweb.com/CascadingCombo.htm
With a small demo database included.

[carterlangley]

Hi ranman,

Tried that, but I can't seem to get it working. I've exported the table information into a text document. Is there anyway I can get that over to you so you can have a look?
I can do the same with the relationships if that will help?

[ranman256]

post the database so we can see.
(use the MANAGE ATTACHMENTS button at the bottom of screen)

[carterlangley]

Database size exceeds the forum limit :-(
Database is just over 1 meg in size at the moment.
It also has a hell of a lot of personal information. I'm my wife's full time carer and I'm trying to do this database to keep a record of medication usage and toilet routines and paramedic visits and hospital admissions, etc.

[aytee111]

Make a copy of the database, delete all tables except the two that are in question here. Compact and Repair it, the zip it. That will bring it down to size.

[carterlangley]

Hi aytee111,

I've emailed a copy of the database as it currently stands to ranman256. I'll see if I can zip it and then post it.

[aytee111]

If he is helping then don't worry!