Cascading Combo in an embedded continous subform (1 Viewer)

Hello folks, I am looking into creating a stock control/allocation database and have come to a problem. I have a form which contains the customer details. I have also created a sub form based on resources allocated to that customer, with the idea that a continual record of stock allocation can be seen on the screen and new allocations made when required.

A combo box contains a list of the possible main categories i.e. Stationary, Clothing etc, and a second combo box contains a list of items controlled by the first combo box. cboCategory 'Stationary' - cboItemDetails 'glue', 'pencils', 'erasers' etc.

This works well when done on a simple form, but that of course would not show the allocation history without moving through one record after another. I tried to do the same on a continuous form, but I keep getting error messages.

My questions are these: Is it possible to have cascading combo boxes on a continuous form? If not, is there an alternative that would give the same result?

Rob

Can you post a copy of your db with a few dummy records to show the problem

Thank you for your prompt reply Bob. I will send what I have ASAP.

Rob

Hi Bob, I hope you got the attachment. This is where I am at the moment. The form [OrderFormMain] contains the customer details and the embedded subform [OrderFormsub] should contain a record of each stock allocation.

The first combo contains a general description and the second should show all the records pertaining to that general description and the user should select from there.

I had it working (kind of) but now it seems that in my confused state I've lost even that.

Any help or advice would be greatly appreciated.

Regards

Rob

Take a look at the attached db.
Hopefully, this is closer to what you want.
I have added a text box to the sub form. This needs to be positioned on top of the combo box. the text box is bound to the Description field.
I have added code to move the focus to the combo box.
I have changed the order of the columns in the combo box and made another one of the columns invisible by setting its width to 0.
I have added code to requery the combo box when it gets the focus.
If you need any further explanation please ask.

I don't want to tread on Bob's toes, but this is an example you will find in the code repository - works for continuous and datasheet forms

https://www.access-programmers.co.uk/forums/showthread.php?t=275155

That's amazing Bob. Thank you very much. You are a star!

Thanks for the head-up CJ. I'll certainly have a look there.

bob fitz said:

Take a look at the attached db.
Hopefully, this is closer to what you want.
I have added a text box to the sub form. This needs to be positioned on top of the combo box. the text box is bound to the Description field.
I have added code to move the focus to the combo box.
I have changed the order of the columns in the combo box and made another one of the columns invisible by setting its width to 0.
I have added code to requery the combo box when it gets the focus.
If you need any further explanation please ask.

Click to expand...

Hello again Bob
Recently you solved my problem with cascading combo boxes on a continuous form. Somehow, whilst making further developments elsewhere I lost this functionality. I may have used an earlier copy or something.

Do you think, if I sent you a current copy, you could have another look?

Apologies for going over the same stuff again but I am lost with this.

Rob

Sorry Bob, I forgot to attach the DBase.

The relevant form is accessed by the 'Staff Resource Allocation' button, then 'Browse Records' Button.

Many thanks once again

Rob

See attached file

bob fitz said:

See attached file

Click to expand...

Once again Bob...Thank you!!!!!!

Hello once again Bob.

Some time ago you very helpfully solved my Stock Control database for me. It has worked beautifully since then. Now there has been some new requests made which involved additional filtering. This is achieved by adding another level of item classification. Now there are two levels instead of one. This reduces the number of items available in the final combobox. I have done this and tried to update your code accordingly. Unfortunately now the selected items in Subform1 are not updating to the user history in Subform2. I have looked at the underlying table and everything seems normal. I checked Subform2 and discovered a hidden textbox there that you created which references the CustomerID in Subform1. This is not working with the new modifications. I have tried to alter your code but ended up a little muddled. I did leave the original combo boxes on subform1 for reference and created new ones,

I wonder if it would be possible for you to have a quick look at it again?

I have modified only one form which is accessed by Switchboard-Resource Allocation-Select From List. The other form which is access by barcode scanner is identical and I should have no problem replicating any changes made.

Anyway Bob, I'd be very grateful for any help you can give and to this end I have attached a copy of the now modified database.

Thanks

Rob

Hi Robbyp2001

No problem. Will look at it over the weekend

Many thanks Bob

Try the following code in the OnCurrent event of the Main Form

Thanks very much Bob, but unfortunately this seems to have no effect The first subform is now populated with the last item entered into the underlying table.

Robbyp2001 said:

Thanks very much Bob, but unfortunately this seems to have no effect The first subform is now populated with the last item entered into the underlying table.

Click to expand...

So, can you tell us a little more about your perceived problem and the expected records shown in the subforms

Hi Bob, something occurred to me last night and that is, without modifying the existing structure which is working well, I can add two unbound combo boxes to the form which should filter the returned records in the third combo.

The problem that I have now is the request to have a number of clothing items added to the database. Unfortunately, each item can have up to 13 or so different sizes, and many are labelled Old and New stock which can lead in some cases to 26 different items!

In the existing application that you worked on, it is pretty straightforward. 1 Item to 1 classification, If I selected Photocopy from Combo1 this would produce 4 records in Combo2 (A3, A4 Colour and A3, A3 Mono). Works well! Each record is stored in the Order Table, which is effectively each employee's personal record, and the balance updated each time.

Now, because of all the different permutations of clothing size and model (hundreds of them) I have to introduce a way of filtering these as much as possible. Combo1 has the option of 'Stationary', 'Clothing' and 'Photocopying'. If 'clothing' is selected for example, Combo2 will show 'Shirts & T Shirts', 'Pants & Shorts', 'Skirts & Smocks' etc. Combo3 will now have a much reduced number of items because it will ignore all stationary, photocopying and irrelevant clothing items. Selecting one of reduced combo3 list, then allocating it as before, should work because the code that you produced has not been touched and will start working from this point.

It's still rather clumsy as some of the lists produced by combo2 are still quite long. These will reduce though as the Old Stock diminishes. Once these reach 0 balance they can be deleted.



I know it's quite a poor way of achieving what I need. I would much rather the user selected an item and then added the model and size on the form, but I have no idea how to do this and show the current balance (as it does now) before committing the record. That would definitely eliminate the long lists of items, but way beyond my limited knowledge unfortunately.

The other alternative is to create another database that contains only
clothing items.

Does this make any sense?

Thanks again

Rob

Hello Bob

I am happy to report that I have finally got the database working the way I wanted. Adding another level of item classification certainly filters the records more effectively but led to a major headache trying to get the thing to work. Eventually, I opted for a complete re-build. which forced me to study your code closely in order to modify it to suit the new design. Getting your code to work was the key. I also discovered some flaws in my logic which would definitely have caused problems further down the line. So once again thanks for all your help. I could not have got this far without you.

Rob