Cascading Combobox value disappears

[sxschech]

Was playing around with cascading comboboxes and when I entered the last box with data and ran the afterupdate event, all the comboboxes blanked out. The results that the comboboxes provided did show up though. cbo1-cbo4 are shorthand for the actual name of the comboboxes.

On open form is unbound
Comboboxes are unbound in header
Textboxes in detail are unbound

After update cbo1 provides row source for cbo2
After update cbo2 provides row source for cbo3
After update cbo3 provides row source for cbo4
After update cbo4 sets the recordsource of the form to an SQL statement and adds a where clause using the values from the comboboxes.
Form displays the results correctly of the recordsource in textboxes where the field (control source) is determined by the value in cbo2 (tablename) --and then the values in the combobox disappear (in stepping though code, i see that the value of the cbo is null so more than disappearing, it is wiping out what was there)

Before this behavior, the comboboxes maintained the value and I could then choose another item from one of the cascaded boxes to view another record's data. Not sure what changes in the code is causing this "reset". I don't have any requery or refresh statements so have ruled that out as a cause.

I did a search and found a suggestion that had the value from the combo be put in a textbox. Tried that, but that also got cleared out on the final combo box's afterupdate event.

Do I need to use a form/subform approach rather than header/detail? I can proved the code if needed.

 

[isladogs]

When using a 'cascade ' effect, if one thing goes wrong, everything does....

In cases such as you describe, I normally put the combo box value in a hidden textbox as I've had problems in the past using the combobox output to feed another control.

In your case, could you populate the textbox only where the value of the cbo isn't 'cleared out'

However that doesn't solve the issue of why its getting wiped out
Form/subform sounds a better solution

 

[sxschech]

I'll give a try to rewrite as form/subform and let you know results. May not be until next week though.

 

[VSolano]

Was playing around with cascading comboboxes and when I entered the last box with data and ran the afterupdate event, all the comboboxes blanked out. The results that the comboboxes provided did show up though. cbo1-cbo4 are shorthand for the actual name of the comboboxes.

On open form is unbound
Comboboxes are unbound in header
Textboxes in detail are unbound

After update cbo1 provides row source for cbo2
After update cbo2 provides row source for cbo3
After update cbo3 provides row source for cbo4
After update cbo4 sets the recordsource of the form to an SQL statement and adds a where clause using the values from the comboboxes.
Form displays the results correctly of the recordsource in textboxes where the field (control source) is determined by the value in cbo2 (tablename) --and then the values in the combobox disappear (in stepping though code, i see that the value of the cbo is null so more than disappearing, it is wiping out what was there)

Before this behavior, the comboboxes maintained the value and I could then choose another item from one of the cascaded boxes to view another record's data. Not sure what changes in the code is causing this "reset". I don't have any requery or refresh statements so have ruled that out as a cause.

I did a search and found a suggestion that had the value from the combo be put in a textbox. Tried that, but that also got cleared out on the final combo box's afterupdate event.

Do I need to use a form/subform approach rather than header/detail? I can proved the code if needed.

Hi
I have this issue and I could not find any better solution to this than have the ID included in the column count. So when you open the form, you will see the ID

 

[sxschech]

I changed the form to be a form/subform and that seemed to solve the main issue as well as the need for textboxes. I'm still trying to figure out how to "skip" a step when a certain document type is chosen in an earlier combobox. Normally it will take 4 cascading combos to get the data each one modifies the record source of the following. There is one doc type that only needs 3 combos. I tried an If statement and when I step through the code and hover mouse over it, shows that its value is being set for me.cboResponse.Value = Me.cboQuestionNo.Column(1), but it does not display in the combobox on the form or pull up the data unless I manually click on the combobox and choose the item in the list that I wanted to have auto populated.

 Case 2
            Me.cboResponse.RowSource = "SELECT Term, Definition FROM tblGlossary WHERE ProductCode='" & Me.cboProductCode & "' AND QuestionNumber=" & Me.cboQuestionNo
            Me.lblResponse.Caption = "Definition"
            Me.frmEdit_Sub.Form.lblQuestion.Caption = "Term"
            Me.frmEdit_Sub.Form.lblAnswer.Caption = "Definition"
            If Me.cboResponse.ListCount = 1 Then 'Single Line Definition
                Me.cboResponse.Value = Me.cboQuestionNo.Column(1)
                Me.frmEdit_Sub.Form.RecordSource = "SELECT QuestionNumber, Term, Definition FROM tblGlossary WHERE ProductCode='" & Me.cboProductCode & "' AND QuestionNumber=" & Me.cboQuestionNo & " AND Term='" & Me.cboQuestionNo.Column(1) & "'"
                Me.frmEdit_Sub.Form.txtQuestion.ControlSource = "Term"
                Me.frmEdit_Sub.Form.txtAnswer.ControlSource = PlainText("Definition")
                Me.frmEdit_Sub.Form.txtExplanation.ControlSource = ""
            End If

 

[isladogs]

Suggest you request the combo box after setting its value
You might also want to add the line DoEvents after that to ensure code is processed before carrying on

 

[sxschech]

Thanks ridders.
I added a DoEvents before Me.cboResponse.Value = Me.cboQuestionNo.Column(1) and that displays the correct data in the subform, however it does not display the value in the combobox. Not a big deal, but would be nice to have the visual confirmation of the value displayed in the combobox.

Not sure what you mean by requesting the combobox, does that mean me.cboResponse.SetFocus, Requery, Refresh, Repaint? (tried them all, no effect)

 

[isladogs]

typo. I meant reQuery. Sorry about Q - autocorrect kept altering it!
Surprised requery didn't work

Colin

 

[isladogs]

have you tried requerying the whole form

 

[sxschech]

[SOLVED] Re: Cascading Combobox value disappears

Got some help. Here is the solution:

'Refer to the item data in the actual combobox -> cboResponse 
'rather than the combobox cascaded from -> cboQuestionNo value 
Me.cboResponse.Value = Me.cboResponse.ItemData(0)