Re-query combo box

[Spider]

Happy Monday!
I have been working on something that I have done before, but am having trouble with it (again).

I have a Microsoft Access 2003 database for patient complaints.
Two of the fields are Combo Boxes:
1. (cmb)ICComplaintID
2. (cmb)ICComplaintTypeID

I would like to limit choices on Combo Box 2, based on what is populated in Combo Box 1. I believe this is working, but it will not “requery” even though I have code in Combo Box 1 that says:

Private Sub ICComplaintID_AfterUpdate()
Me!ICComplaintID.Requery

End Sub

When I go to a new record, the Combo Box 2 still has the previous choices, even though I choose a different item in Combo Box 1.

I must be missing something?
I did attach a stripped down example.
Vicky

[HansV]

Hi,

You need to requery the second combo box, not the first one:

Code: Select all

Private Sub ICComplaintID_AfterUpdate()
    Me!ICComplaintSubTypeID.Requery
End Sub

[Spider]

I moved the requery to the 2nd box. It behaves the same as it did - so I must be doing something else wrong.
No matter which record I move to, the choices are limited to just the billing complaint sub types.

Are these the only two things I need?
1.
SELECT tblICComplaintSubType.ICComplaintSubTypeID, tblICComplaintSubType.ComplaintSubType, tblICComplaintSubType.RemoveComplaintSub, tblICComplaintSubType.ICComplaintID
FROM tblICComplaintSubType
WHERE (((tblICComplaintSubType.RemoveComplaintSub)=No) AND ((tblICComplaintSubType.ICComplaintID)=[Forms]![frmDataEntry]![ICComplaintID]))
ORDER BY tblICComplaintSubType.ComplaintSubType;

2.
Private Sub ICComplaintID_AfterUpdate()
Me!ICComplaintSubTypeID.Requery
End Sub

Thank you,

[HansV]

It works for me in the attached version. What happens if you try it?

Complaints.zip

[Spider]

Yes, your version does work. I think I know why now.
I misunderstood your above mention of:

Private Sub ICComplaintID_AfterUpdate()
Me!ICComplaintSubTypeID.Requery
End Sub

I placed the whole thing in the 2nd combo box's After Update - instead of leaving it in the 1st combo box to requery the 2nd box.
I will make sure I am correct about that and work on it.
Thank you so much as usual,
Vicky

[Spider]

I guess there is still a problem with the combo boxes.
I went back in to populate some of the ones that are not populated in the 2nd combo box now (for some reason), but the data does not remain in the form.
If I populate a few Complaint Sub Types, then go back to those records, the fields are empty again. I will continue to look at the requery?

This happens on the last version that you sent as well.

[Spider]

It seems like it could be defaulting to the first item in the list? the only records that display a Complaint Sub Type are the ones that are the first Complaint Type of "Billing". It is the first in the Combo Box 1 list.

[HansV]

You also need to requery the combo box when you move to a different record:

Code: Select all

Private Sub Form_Current()
    Me!ICComplaintSubTypeID.Requery
End Sub

[Spider]

It works perfectly.
Thank you thank you!