Cascade Combo help!!

[joseppi01]

Hi there,

I've been desperately trying to work out Cascade Combo's for a week now, and as much as I'm trying to follow instructions and tutorials, I can't seem to make it work!

I've attached the database, and what I'm trying to do is:

1) in frmOrder, make a cascading combo - choose a Service, which will then populate the Wards applicable to that service (tblWard shows which wards come up what service)

2) if possible, in sfrmOrders, do something similar - choose a 'Type' to order, and then the 'Item'. Same thing, choosing a type will populate the avaiable items (see tblItem for descriptions).

From what I have read the second part may be a bit trickier. Some orders will have up to 20 items so it needs to be a datasheet.

Please help??

[sierra7]

joseppi
Here is the fix.
I've just spent an hour typing the changes and they have been lost.
I'll write them as separate notes
S7

[sierra7]

Hi again,
The first thing I have done was to remove all the Look-ups from the table structures. This is probably not absolutely necessary but I was getting confused; when I see ServiceID I expected to see a number not the Service-name, which is confusing when you want to use the output to cascade filter.

On the main form frmOrders, I removed tblService and tblWard from the query, as the data they were contributing could be read from the comboBoxes. If tblWard had a 'telephone-number' or 'person-in-charge' which you wanted to display, then you would leave it in and include the additional fields.

The other change was to select WardID and ServiceID from tblOrder. This is very IMPORTANT because these are the bound fields that you are going to update.

Now addressing the comboBoxes. 'Service' I renamed cmbService. The RowSource selects ServiceID and Service. The BoundColum = 1. On the Format tab the ColumnCount=2 and the ColumnWidths=0,2cms. i.e the first column is invisible, so Service is displayed.

The second comboBox is now cmbWard. The RowSource selects WardID, WardName and ServiceID. Note that ServiceID is filtered by [cmbService] i.e. the bound field in cmbService.

The top part of the form now works as you requested.
S7

[sierra7]

Hi again 2,
Adressing the sub-form;
There is an issue with Access when displaying multiple records like you want to do in the subform, whether in Datasheet view or in ContinuousRecord view, when cascade filtering. If you have four records all filtered on different values, you will only see the look-up on the one which has focus.

Starting with the query for sfrmOrder, on this occasion I have left tblItem included to get the item's description to mitigate the above mentioned problem.

The first comboBox is now cmbType, has RowSource selecting TypeID and Type, BoundColumn=1, ColumnCount=2, ColumnWidthe=0;2cms. Note that the ControlSource=TypeID (I did not mention in my last post but the ControlSources for cmbService and cmbWard were ServiceID and WardID, obvious really)

The second combo is now cmbItemID, where ControlSource=ItemID, ColumnCount=3, ColumnWidths=1.3;8;0cms, ListWidth=9.5cms but Width only = 1.3cms. The RowSource selects ItemID, Item and Type, where Type is filtered by the contents of the bound field of [cmbType].
The width has been reduced to 1.3cms but will expand to 9.5cms upon selection where the different item descriptions will be available.

Note that cmbTypeID and cmbWard are re-queried upon entry,e.g.

Private Sub cmbItemID_Enter()
Me.cmbItemID.Requery
End Sub
 

Also, note that an After_Update event has been added to cmbTypeID to ensure that the Type cannot be changed to leave displayed an invalid Item for that Type.

Private Sub cmbTypeID_AfterUpdate()
Me.ItemID = 0
End Sub

The final change has been to add a textBox to display the item description. This has been called txtDescription and is bound to 'Item' from the query. On the DataTab it is set so Enabled=No and Locked=Yes, so it is for display only and can only be changed via the comboBox cmbItemID.
The subform can be displayed as continuous records (not very well aligned at the moment!)or in DataSheet mode, in the latter case check that the DatasheetCaptions are completed on the Others-Tab.

I think that's it!
S7

[NeoPa]

You may find Cascaded Form Filtering helpful. I know this doesn't fix your problem for you, but having a play may leave you in a position where you understand the fundamentals better, and so can help you fix it yourself.