Cascading Comboboxes in a Subform

[Cowlers]

Hi All,

I am aware I am not the first to raise this question but I've tried in vain to find a definitive answer and haven't been able to so I am hoping someone can point me in the correct direction!

I have a main form which allows me to fill in Order Information within which is a subform which allows me to fill in Order Lines.
My Order Lines contain the following fields:
Extrusion
Length
Qty
etc. etc.

Elswhere in my database (tblLengths) I have set up a list of possible lengths for each Extrusion and therefore when an order is filled out I want the user to be restricted only to those lengths that are possible with the particular extrusion they have chosen. This list contains the ExtrusionID and LengthID so that I can query the available lengths for a particular Extrusion.

Within my subform I have set the record source to point at the tblLengths and criteria on the ExtrusionID to point to [Extrusion]. I've also put a requery against the Extrusion field so that I can force the combobox to refresh its list of results.

My problem is that I am getting unexpected results each time I add an Order line into my form and reading up I see that what I am trying to do is a big no-no. Please could you please help me to achieve what I need to?

Many thanks in advance,

Dan

 

[Cowlers]

Just to add some further information I have also tried using a continuous form to represent the same information and the Comboboxes behave in exactly the same way as they do in the datasheet view.

 

[JHB]

Post you database with some sample data (zip it) - and the name of the form where the problem is.

 

[Cowlers]

I attach a zip of my database as-is (with the exception I have put dummy data in and removed the corporate branding). Please be aware this is a work in progress so it is not all working yet. It opens with a splashscreen that will then disappear and ask for a password, the username is the only one on there, AM, and the password is admin (again dummy data!).

There are a couple of forms I am having this problem with, please see Extrusion Orders as an example of what I am trying to do, namely to show the order header detail on the same form as the order detail and restrict the entry of the various boxes in the order detail to the options that are available for a particular extrusion.

View attachment Purchasing System - Help.zip

Any questions please shout!!

Many thanks,

Dan

 

[JHB]

Your subform is not update able, (your query is not correct).
In the query for the combobox length the criteria [ProfileID] is unknown to the query.
Sorry I don't think I'm able to help you, (it is a mix of queries in queries for queries so I lost track of it ... )!

 

[Cowlers]

Thanks for your help JHB,

I am sorry it is difficult to follow my structure, my problem is that there are quite a few variables that come together to determine what can and cannot be selected for a particular ProfileID...

If anyone else can help I'd greatly appreciated it! Otherwise I'll keep plugging away and try to resolve it myself. I'm currently approaching this in the method that each line would be modified directly in the subform, if it would be simpler to select a line and modify only that line at any one time then this may be the way I have to do it... I don't know what the general conventions are for designing an order form such as this, I was just trying to make it a little easier for the person inputting the order.

Many thanks,

Dan

 

[ynot]

Hi Dan

I managed to get my cascading comboboxes working by using the attached sample. Let me know how you get on. It's not easy getting it all to work right.

Best of luck

Tony

 

[Cowlers]

Thank you very much Tony,

I had eventually got it working myself after a lot of searching around but had forgotten to change my thread to solved until you updated the thread!

I used the "Textbox covering a combobox" method which seemed to do the trick nicely. It's a bit of a pain to set up correctly but to the user it works perfectly adequately.

Many thanks for your help,

Regards,

Dan

 

[ynot]

Hi Dan

That is great news, any chance you could send me a copy it may help me with my issue.

Thanks

Tony

 

[Cowlers]

Hi Tony,

No problem, I'm using it quite extensively in my database but it's fairly large and I've got quite a lot of sensitive data in it so I'll create a small sample database and get it posted in the next 24 hours,

best regards,

Dan

 

[Cowlers]

Ok, firstly apologies if this is not the most elegant solution out there but I've found this to work!! I'm a newby with Access and this method was found by seeking out various other posts on various forums so do not claim it as my own solution as without other users' help I wouldn't have stood a chance!!

Take a look and see if you can follow it, as I mentioned this is the method using a textbox over the second combobox to present the value and stop it from disappearing every time you move to a new line etc. Please shout if you need any explanation of any of it.

View attachment CascadingComboBoxes.zip

Good luck!

Regards,

Dan

 

[ablaubla]

Hi cowlers,
I just checked out the sample you've sent. It is good for passing new data but the editing of the options is not really working... at least at this small sample.
If you make a new entry lets say with Combo1ID = CB01B and then try to edit a previous entry with Combo1ID = CB01A you then have the options for the Combo2ID CB02C and CB02D which are supposed to be for the CB01B and not for the CB01A... do you face the same prob or is it only me? Anyone else who can help with this thing? I wanna do exactly the same thing with you...
BR
abla

 

[Cowlers]

You are absolutely correct abla, I believe you can resolve this problem by modifying the code on the current event of the form so that instead of the requery of the combobox being within the if statement (so that it only applies if the main combobox is empty) it also requeries when you change between records.

Sample below:

View attachment CascadingComboBoxes2.zip

Hope this helps!

Regards,

Dan