Full Version: Cascading Combos on a Continuous Form
UtterAccess Forums > Microsoft® Access > Access Forms
johncrop
Gurus,
There are lots of posts here re cascading combos, but all the ones I have looked at refer to forms that are designed to display single records. I need to use cascading combos on a continuous form.
cboProduct selects a product. cboVersion selects the version of the product, with each product having its own set of versions. cboVersion's row source is a simple SQL statement that refers to cboProduct in its where clause,and cboProduct_AfterUpdate requeries cboVersion. All works fine in a single record form.
But put it in a continuous form, and when a selection is made in cboProduct and cboVersion is requeried, all the selections in cboVersions for previous records vanish from the display (though not from the underlying table) unless the products in the previous records happen to be the same as for the current record. When cboVersion is requeried, this applies to cboVersion in all records in the continuous form, not just the current record.
Can anyone let me know how to get cascading combos in a continuous form, please?
John
AQM_UK
Hi John,
ascading combos can be used with continuous forms.
It is just that once you enter the parameters to the combo in the where clause, only these records appear. So only the versions of the product you have selected appear.
Have you thought about using a combo box for product to open the form.
This will then have all your versions on the form.
Then add a combo box to the form header to either find the record or to filter the records, based on the value in the combo box.
Not sure if this will help.
Jimbo
Clippit
I have a continuous forms subform which, I believe, does what you are trying to do.
The selection in the first combo box on the row affects what choices are offered in the second combo box. The choices in the previous row combo boxes keep "working right" even after a value is selected in the later combo box.
Here's what I did:
The SQL which is the row source for the second combo box referes to the first. I do not modify the SQL "on the fly" as some do:
..... WHERE ((([tbl0200_SE-MerchRelease].REGION)=[Forms]![Work With Merch Calendar Deals]![tbl0300_SE-MerchModule subform].[Form]![cboRegion]) AND....
([Forms]![Work With Merch Calendar Deals]![tbl0300_SE-MerchModule subform].[Form]![cboRegion] is the first conbo box)
The requery is on the second comb box' Enter event
Private Sub CboDefaultRelease_Enter()
Me.CboDefaultRelease.Requery
End Sub
johncrop
Thanks for your thoughts, Jimbo, but I'm not sure what you mean.
need to be able to view a customer's existing orders and enter new ones. There are only a few basic fields, such as date, product, version, quantity, and in the context of this database, a simple continuous form is the ideal way to do this. I don't think that filtering the form by product would really help, if that's what you were getting at.
I'm trying a variety of ideas, focussing on maybe having duplicate controls on the form. Fingers crossed.
Regards,
John
johncrop
Steve,
He putting the requery in the second combo OnEnter event, I actually had a requery in the form OnCurrent event which did the same thing. Moving it to the second combo OnEnter event might be neater - thanks.
But it does not appear to solve the problem of the selections in previous rows not being visible if the product is different. Do you find that with your form - after the second combo is requeried, the values in the second combo in previous rows are not displayed unless the value in the first combo happens to be the same?
Regards
John
AQM_UK
Hi John.
aving two combo boxes is called "Cascading", as the second relies on the output of the first.
Thus, if you have a cbo with customer as the selection, this will limit the output of the second cbo to the orders that this customer has.
Now, if you use this second cbo output to open your query, you are only going to see that customer order.
What I was suggesting was this.
Have a frmMain with customer (one) with a subform of the orders this customer has placed (Many). Parent/child link is customerID. You therefore only need one combo box to limit the selection to customer and the query that is bound to frmMain as the parameter [Forms]![frmCustSelect]![cboCustomerID] in the customer ID of the query.
When the form is opened, this will list all the orders attached to this customer. You can put a command button "Add New Order" onto the subform header with the code DoCmd.GoToRecord , , acNewRec.
The second bit I was suggesting was this.
You may want to add a second cbo onto the subform that contains all the customer orders based on, say, product. When the user selects a product, in the afterupdate event, you could filter for that product and all orders, with that customer, for that product would be listed.
This is an approach I have used many times and has been helpful to my users.
HTH
Jimbo
Clippit
No, I do not have that problem. Here's a picture of what mine does- see if we're talking about the same thing.
johncrop
Hi Jimbo,
Sorry to take a while to get back to you - too much to do and not enough time, etc.
If I am interpreting what you are suggesting correctly, you are using the cascading combos to filter the form so that it only displays certain records. I agree - the way you are suggesting works fine for that.
However, the problem I am having is to cascade combos within a record on a continuous form. In this instance, the form is not filtered - all records need to be visible. The combos are needed for selecting field values when adding or modifying a record, and I'm having trouble keeping the selected values for all the records visible when the second combo is updated and the selected value for one record is not in the recordset of combo values for the another record. Does that make sense?
Regards,
John
johncrop
Steve,
Sorry for the delay in getting back to you.
Thanks for the pictures. Yep, we're talking about the same thing.
Osome little tables and a form form copying your picture and I put the requery in the enter event for the second combo and it seems to work fine. Well done.
All I have to do now is work out why it worked in this test example and not when I tried it in the real thing!
Regards,
John
Clippit
Oh, I hate that, when it works in the little test but you can't duplicate the feat where it counts!
dovedy
Hello John,
I am having a similar problem, where the selection from the second cbo vanishes.
I have a date field on the continuous subform and decided to assign a calender to make entries. Each time I click on the date field the calender pops up on all the previous records.
Any solutions??
Thanks.
Regards,
Dove
johncrop
Dove,
I am not sure what you mean but I found that Steve's solution worked fine for the problem I was having. Have a look at Steve's post above and his attachment. Hopefully there is something there that helps you.
Let me know how you get on.
John
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.