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
Re-query combo box
-
- StarLounger
- Posts: 96
- Joined: 11 Feb 2010, 21:59
- Location: Ohio
Re-query combo box
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78541
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Re-query combo box
Hi,
You need to requery the second combo box, not the first one:
You need to requery the second combo box, not the first one:
Code: Select all
Private Sub ICComplaintID_AfterUpdate()
Me!ICComplaintSubTypeID.Requery
End Sub
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 96
- Joined: 11 Feb 2010, 21:59
- Location: Ohio
Re: Re-query combo box
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,
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,
-
- Administrator
- Posts: 78541
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Re-query combo box
It works for me in the attached version. What happens if you try it?
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 96
- Joined: 11 Feb 2010, 21:59
- Location: Ohio
Re: Re-query combo box
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
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
-
- StarLounger
- Posts: 96
- Joined: 11 Feb 2010, 21:59
- Location: Ohio
Re: Re-query combo box
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.
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.
-
- StarLounger
- Posts: 96
- Joined: 11 Feb 2010, 21:59
- Location: Ohio
Re: Re-query combo box
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.
-
- Administrator
- Posts: 78541
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Re-query combo box
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
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 96
- Joined: 11 Feb 2010, 21:59
- Location: Ohio
Re: Re-query combo box
It works perfectly.
Thank you thank you!
Thank you thank you!