By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,968 Members | 1,295 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,968 IT Pros & Developers. It's quick & easy.

Cascade Combo help!!

P: 7
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??
Attached Files
File Type: zip Ward Order Database (88.4 KB, 50 views)
Nov 21 '11 #1
Share this Question
Share on Google+
4 Replies

Expert 100+
P: 446
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
Attached Files
File Type: zip Ward Order Database (114.9 KB, 34 views)
Nov 21 '11 #2

Expert 100+
P: 446
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.
Nov 21 '11 #3

Expert 100+
P: 446
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmbItemID_Enter()
  2. Me.cmbItemID.Requery
  3. 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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmbTypeID_AfterUpdate()
  2. Me.ItemID = 0
  3. 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!
Nov 21 '11 #4

Expert Mod 15k+
P: 31,709
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.
Nov 21 '11 #5

Post your reply

Sign in to post your reply or Sign up for a free account.