Values in a combo box - how?

[j_cocker]

My DB has (amongst others) two tables:
1. Centres - CentreID as primary key, and centre name a text field; and
2. Children: ChildID as primary key, and a Centre field referring to the CentreID above. It also has many other fields.

I am trying to create a form that will display a list box of all the children for a chosen centre. I basically want the user to be able to use a combo box to select a centre, and for the list box to automatically list all the children who belong to that centre.

I think that I know how to link the combo and list boxes, but I am a little confused here...

Is is possible to achieve this without a reference from the centres table to the children table, i.e. without creating a many-to-many table storing childID/centreID pairs?

I am sure this is trivial stuff for you Access experts, but despite pretty extensive programming experience I am a relational DB newbie, and sometimes just find it tricky to think in the right way.

Any help will be much appreciated.

 

[pbaldy]

Sounds like you want this type of thing:

http://www.baldyweb.com/CascadingCombo.htm

A listbox would operate in the same way as a combo.

 

[j_cocker]

Thanks for that - I already realised that I would need to use cascading combo, but wasn't sure how to use it.

I am now pretty close...

The row source of the combo looks like this:
SELECT [tblCentres].[CentreID], [tblCentres].[CentreName] FROM [tblCentres] ORDER BY [CentreName];

And the row source of the list box like this:

SELECT [tblChildren].[LastName]+" "+[tblChildren].[MiddleName]+" "+[tblChildren].[FirstName] FROM tblChildren WHERE (([tblChildren].[Centre_ID])=[Forms]![frmRegister]![comboCentreName])

I also added the After_Event to the combo.

The problem I now have is that the SELECT statement for the list above only compared the centre key (ID) to the centre name as it appears in the combo.

- How do I change the statement so that it compares centre ID & CentreID (or name & name)?
- Also, the statement looks very long, is it possible to put it (or part of it) in a query? how?

Again, thanks for you patience.

 

[DCrake]

You need to include the centreid in the children tble to the list box and make that the bound column. Set its width to zero so it appears hidden.

 

[j_cocker]

You need to include the centreid in the children tble to the list box and make that the bound column. Set its width to zero so it appears hidden.

I am not sure I understand your reply...

I was referring to this part of the statement WHERE (([tblChildren].[Centre_ID])=[Forms]![frmRegister]![comboCentreName])
that compares ID (number) to name (txt) and obviously does not match anything...

I attached a cut down version of the db to this message.

 

[pbaldy]

It appears to be working correctly. You have made a tactical error by using "+" as concatenation operator instead of "&". "+" propagates Nulls, "&" does not. That's why the listbox remains blank for centers 1 and 3. Try:

Expr1: [LastName] & " " & [MiddleName] & " " & [FirstName]

With a little creativity, you can use that difference to eliminate the extra space when MiddleName is Null.

 

[j_cocker]

Fantastic!

Again, many many thanks for your help!

 

[pbaldy]

Happy to help!

 

[vdanelia]

Thanks for the Example