Cascading combo box disaster!!! Disappearing entries in previous records!!

[vangogh228]

I have set up a set of cascading combo boxes for "Service Department" and "Service Provided." When you choose the Department, the Provided field dropdown shows only those entries appropriate to that department. I put a Service Activity subform (continuous form) in my client data form for entry by the CSR.

All went well to that point, and it all looked great. BUT... now, when I am putting in the second or later service activities for any client, previous entries of service are affected when I make a choice in the Department field. All services from previous entries that are not in the department I just chose all suddenly DISAPPEAR.

I have attached a jpg file so that you can see my continuous subform.

I am donating this database to a local nonprofit company, and they are in desparate need to have this completed. I plan on putting this on their server on Tuesday July 8 and have training scheduled for Thursday July 10... so any help is APPRECIATED!!!!

 

[Rich]

Here's one post on the subject
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=20727&highlight=Continuous
there have been many more, if you search for continuous or cascade I'm sure the results will be beneficial.

 

[vangogh228]

Rich: Thanks! I did check and the best answer seems to be to take the combo off the continuous form, which I cannot do in my situation. Thanks.

 

[DBL]

The issue is because you are using the cascading combos in datasheet view. The combo box stores the bound field, usually an id field that is hidden from view. If you unhide the bound field you will see that the combo is working as it should and will always show you the bound field, it's the description it can't show until the requery happens. Here's a sample that shows how to work around it.

Shout if you get stuck

 

[vangogh228]

DBL:

Thanks. I have seen that example before and it is a good one. The problem for me, though, is that the dependent field is going blank in previous records. I would expect the field to blank in the current record, but all records are going blank.

The problem does not occur in the datasheet, but only in the form. Again, the problem is that previous records are blanking the dependent field when the first dropdown is selected on.

Thanks again. Tom

 

[DBL]

Have you tried doing a requery on the combo box on the OnCurrent event of the form?

 

[vangogh228]

I put the requery in the After Update property of the first combo, as I followed the example of the sample you posted (the one I had seen before). Thank you.

Here is the code I put in the After Update property:

Private Sub Service_Department_AfterUpdate()
Me.[Service Provided].Requery
End Sub

I guess, and this IS just a guess, the reason this is happening is that the requery is running for ALL "Service" fields in the subform whenever ANY iteration of the "Department" field is updated. So... how do I get the requery to run ONLY for the current subform record, or the one being edited, without affecting the others in the subform?

Thanks for any help guys and girls... as I am running out of time to resolve this.

Tom

 

[DBL]

If the database isn't too large and you can zip it, send it to me and I'll have a quick look. dawn.lawson@1stclass.uk.com

 

[vangogh228]

Would it make a difference if I put the update requery property in the underlying table's field rather than in the form control property? That would seem to make sense to me, but I'm not sure how to accomplish that... or if it would actually work.

Dawn: Sent the file 5:50pm your time. You should receive it just as soon as a 3/4 meg file can make it there. Thanks SO much!!

Tom

 

[DBL]

Hi Tom

There is a visual problem with viewing results of cascading combos in datasheet or continuous forms. Remember that you are using the same combo boxes for each record, only viewing them multiple times so if you change the selection in the first combo the query that the second combo is based on requeries and shows you a different list of options. The combos are still working properly and the bound field for both combos is being stored in the base table, and if you unhide that bound field it will always be visible in the second combo, it's the description and disappears. The only way round this problem is to have to fields to show the results for the second combo. Firstly a combo with the id field and description available so users can make their choice, secondly a text field that shows the description for the choice made in the second combo.

I'm sure that's as clear as mud but hopefully put together with the sample it makes sense!

Dawn

 

[vangogh228]

Dawn: What you sent is great. I think I understand what you are saying. I am going to try to implement it exactly as you have sent. Thank you thank you thank you. Tom

 

[wiklendt]

Hi Tom

There is a visual problem with viewing results of cascading combos in datasheet or continuous forms. Remember that you are using the same combo boxes for each record, only viewing them multiple times so if you change the selection in the first combo the query that the second combo is based on requeries and shows you a different list of options. The combos are still working properly and the bound field for both combos is being stored in the base table, and if you unhide that bound field it will always be visible in the second combo, it's the description and disappears. The only way round this problem is to have to fields to show the results for the second combo. Firstly a combo with the id field and description available so users can make their choice, secondly a text field that shows the description for the choice made in the second combo.

Hey, this is exactly what i am experiencing except my combos are on a single bound form (neither datasheet nor continuous).

This solution was also detailed more recently here:
http://www.access-programmers.co.uk/forums/showthread.php?t=198989

but i was wondering if this is still the only/elegant solution? seems to me rather round-about band-aid?

 

[gemma-the-husky]

its irritating

you get a similar thing if you try and grey out certain controls on a continuous form. It greys the same COLUMNS for ALL VISIBLE ROWS.

I think its just something you get used to. It doesnt affect the useability, and I do find a continuous form/data sheet is often so much easier ot use than a single form.

 

[wiklendt]

ok, i found a different solution, though i'm not sure it's any more elegant, just doesn't have as many "hidden" things ...it's all in the code.

i added some requeries on the "On Current" event - this works for my form because it's a single, not continuous form. i suppose my data lends itself to be continuous, but i've done it as a single form.

here's the code:

    Me.cboFamily.Requery
    Me.cboGenus.Requery
    Me.cboSpecies.Requery
    Me.cboStrain.Requery

obviously, this would be tedious depending on how many cascading combos you have, but i didn't have too many, so i don't (think i) need to loop controls or anything fancy like that, but you may want to if you have more on your form (don't forget to tag them if you have other combos on your form that you don't need requerying).

again, i'm not sure how this solution would work on a continuous form.

anyway, i've also added a few bells and whistles to mine, as it was specifically for species classification and adding it to an isolate, so in addition to cascading the classification key for each isolate, i had to be able to update the classifications if i needed to add a new, say, species of bacterium to an existing genus, or a new strain to an existing species, etc.

there is just one thing in this database i'm not particularly warming to, and that's where the classification subforms display the first record from the source of the bound form data before the listbox selects which data to display. i'd probably fix this by making the source null until the cascading combo fills the listbox (i.e., get the combo to fill the form too), but haven't got the time or energy at the moment.

anyway, here's the database for anyone who wants to play: