Dependent Combobox entries disappear on form when scrolling through records

[sclark]

I have a form that uses dependent combo boxes. The combo boxes de-populate (at least the entry does not appear on the form; frmCallInfo) when navigating through the records, then back to the same record, or entering a new record then navigating back to some previous records (intermittent). The actual data entered is there on the master table (tblCallInfo), but it does not appear mainly in the second and third combo boxes (left column) at times.

May be some additional code needed to stop the combo box requeries ?? I have attached the database.

 

[melanie]

Hi there

Try this sample. You were storing values in the callreasonexplanation combo which didn't have corresponding records for the call reason. I have inserted a requery on that combo on the current event for the form.

Regards
Melanie

 

[sclark]

Thanks Melanie.

I need to look at this a little more closely. Some of the Call Reasons have no corresponding Call Reason Explanation by design. The problem was where there was actually supposed to be a Call Reason Explanation value and it was not showing up later after original entry.

 

[melanie]

sclark....

I had this same problem a while ago.

It doesn't matter if there is no explanation record to match the reason. If there is no explanation for the reason then the combo should show no records to select from, but yours was still showing explanations from the previous requery. The blanking of the 'reason' combo was because the corresponding explanation record you were attempting to store was not one which matched the reason. Your referential integrity is enforced so the table did not allow for the incorrect selection to be saved...hence the blanking.

Set your explanation combo to "" after update on the reason combo before the requery

me.cboCallReasonexplanation = ""
me.cboCallreasonExplanation.Requery

Regards
Melanie

 

[melanie]

sclark: to demonstrate where the problem is, create a new record.

Select 'Clinical' from combo1
Select 'MDcall' from combo2
Select 'Inbound Call for new RX' from combo3

Now, if you scroll back and forth between you records and drop the combo3, explanation, of any of the previous records, you will see that the three items available for selection (Inbound call for new Rx, Medical, Outbound etc.) is still the same as the new record you just created. The combo explanations should be showing you the records corresponding to combo 2 instead. This is why I put the requery for combo3 on the "current event" of the form, so that as you are scrolling back and forth through your records testing, that the correct selections are available.

Let me know if you can understand what I am describing.

Regards
Melanie

 

[sclark]

Thanks!! I will digest this a bit since I am a brand new user getting used to all the nuances and see if I can make it work.

 

[melanie]

Sclark:

Let me know if you don't come right. There are sample db's available for the workings of the "cascading combo" - see if you can locate one and study the after update events.

Regards
Melanie

 

[sclark]

I still get blanking on the Call Reason when navigating through the records, also on the Complaint Explanation.

Here is what I have:
Option Compare Database

Private Sub cboCallReason_AfterUpdate()
Me.cboCallReasonExplanation.Requery
End Sub

Private Sub cboCallType_AfterUpdate()
Me.cboCallReason.Requery
End Sub

Private Sub cboComplaintCategory_AfterUpdate()
Me.cboComplaintCategory_Explanation.Requery
End Sub

Private Sub Form_Current()
Me.cboCallReasonExplanation = Me.cboCallReasonExplanation
Me.cboCallReasonExplanation.Requery
End Sub

 

[melanie]

Private Sub cboCallReason_AfterUpdate()
Me.cboCallReasonExplanation = ""
Me.cboCallReasonExplanation.Requery
End Sub

Private Sub cboCallType_AfterUpdate()
Me.cboCallReason = ""
Me.cboCallReason.Requery
Me.cboComplaintCategory_Explanation = ""
Me.cboComplaintCategory_Explanation.Requery
End Sub

Private Sub cboComplaintCategory_AfterUpdate()
Me.cboComplaintCategory_Explanation = ""
Me.cboComplaintCategory_Explanation.Requery
End Sub

Private Sub Form_Current() 'just used this to show you what was happening
Me.cboCallReason.Requery 'as you scroll through the records testing
Me.cboCallReasonExplanation.Requery
End Sub

Try the above

Regards
Melanie

 

[melanie]

The following will occur using the above code:

When selecting the first combo, the second and third combo will be set to a zero string (The second and third combo's will be left blank) and then requeried.

After selecting the second combo, the third combo will be set to zero length (blank) and then requeried, so when you drop the bar the correct records will be available for selection.

This is the cascading effect, the second and third combo's are dependent on the selection of the preceeding combo.

Now what is currently happening: as you are testing your combo's, flipping backwards and forwards through your records, the combo's are still sitting with the last records produced by the query. So if you create a new record and there are "Explanation A" and "Explantion B" as the results of the query on the third combo, dependent on the second combo, you will find that if you scroll to another record, those same explanations are still available if you drop the third combo, selecting the wrong explanations for the type of call will result in the ghosting of your combo. But, if you reselect the first combo then the second (in sequence) the correct records will appear for the third combo. This is why i have put the requery on the current event of the form, to show you what is happening, that requery is not going to affect the correct data you have selected, but if you just want to change the explanation, the correct records will be available.

I hope I am not confusing you.

Regards
Melanie

 

[sclark]

Thanks for sticking with me on this.

I think I understand. I was being confused because I still had bad data stored on the master table. I went back in to each record, after correcting the code based on your posting, and re-chose the dependent boxes to make sure I had valid values for all combo boxes. I also added a requery for Complaint Category Explanation to the Current Form. This seems to have repaired all.

You're a GEM!!! May others be as gracious to you should you have a problem in the future.

 

[melanie]

It's my pleasure, I'm glad to have been able to help.

 

[NiyiO]

Hey Melanie

I had the same problem as sclark and your example codes helped tremendously. However I now have the problem that whenever I try and close my form (via a macro button) it comes up with a parameter value entry box asking me for the parameter values i based the dependent combo box query on.

So in the example above, when I exit the form it come up asing for [Forms]![frmCallInfo]![cboCallType]. Do you (or anyone else) have any ideas why this is happening and what I can do to correct it?

 

[melanie]

Hi NiyiO

Can you send a stripped sample for us to take a look at so that we can see why this is happening.

Have you got any code on the "close event of your form"?

Regards
Melanie

 

[NiyiO]

Thanks - please find attached the sample form and underlying tables, modules and code.

I haven't got any code in the close event but I have a simple exit button which isn't related in any way.

Thanks again for your help.

Regards
NiyiO

 

[melanie]

NiyiO

The problem appears to be with the second combo box. See the attached db for my solution.

Let me know if you don't understand or if it doesn't work for you.

Also for interest do a google search on "The Evils of Lookup Fields"

Regards
Melanie

 

[melanie]

oops forgot the db!

 

[melanie]

Ok, here it is.

 

[RuralGuy]

The Evils of Lookup Fields in Tables

 

[melanie]

Thank you, RG, that was the document I was thinking about.