No Requery After Second ComboBox (1 Viewer)

joejs

New member
Local time
Today, 12:37
Joined
Apr 4, 2019
Messages
5
I am using cascading combo-boxes (I have 2 of them) and when I make a selection in the first combobox the second combobox requeries (like I want it to) but when I make a selection from the second combobox it disregards the first combobox selection and requeries the form based on only that selection.

I have the following code:

Private Sub Combo44_AfterUpdate()

Me.FilterOn = True


Me.Filter = "Book = Forms![AIM_OfferInfo]![Combo44]"

Me.Form.Requery


End Sub
***************************************************
Private Sub Combo44_Change()

Combo46.Requery

End Sub
***************************************************
Private Sub Combo46_AfterUpdate()

Me.FilterOn = True

Me.Filter = "YrSeason = Forms![AIM_OfferInfo]![Combo46]"

Me.Form.Requery

End Sub
****************************************************

I also have [Forms]![AIM_OfferInfo]![Combo44] in my row source.

I need my form to filter for the YrSeason based on the Book selection. Any tips?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:37
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to the forum. It's not clear which is the first or the second combobox, but you could try changing your code to set the Filter property first before turning it on. Also, you might try resolving the form reference in the code, in case it helps. For instance:
Code:
    Me.Filter = "Book = '" & Forms![AIM_OfferInfo]![Combo44] & "'"
 

joejs

New member
Local time
Today, 12:37
Joined
Apr 4, 2019
Messages
5
Combo 44 is the first combobox and Combo 46 is the second.
 

joejs

New member
Local time
Today, 12:37
Joined
Apr 4, 2019
Messages
5
Yes. There is no error but it still does the same thing
 

joejs

New member
Local time
Today, 12:37
Joined
Apr 4, 2019
Messages
5
It is being set to the selection in the second combobox but it is ignoring the selection in the first one

YrSeason = Forms![AIM_OfferInfo]![Combo46]
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:37
Joined
Oct 29, 2018
Messages
21,358
It is being set to the selection in the second combobox but it is ignoring the selection in the first one

YrSeason = Forms![AIM_OfferInfo]![Combo46]
Oh, it means you'll have to combine the two. So, try it this way:
Code:
Me.Filter = "YrSeason = '" & Me.Combo46 & "' AND Book = '" & Me.Combo44 & "'"
 

Mark_

Longboard on the internet
Local time
Today, 09:37
Joined
Sep 12, 2017
Messages
2,111
Two things...
1) Combo46 as a control name makes no sense. I'd recommend giving your controls meaningful names so that you can understand clearly what you are using them for when coding.

2) You are setting the filter in combo46
Code:
Me.Filter = "YrSeason = Forms![AIM_OfferInfo]![Combo46]"

to NOT include your original filter. I am guessing that YrSeason should be a subset of Book and that more than one Book can have the same YrSeason? If so, you would want one SUB to take care of your filter that you can call from either combo. It would look something like
Code:
SUB TCO_FILTER
DIM asFilter AS STRING

asFilter = ""
IF nz(Forms![AIM_OfferInfo]![Combo44],"") <> "" THEN
   asFilter = "Book = '" & Forms![AIM_OfferInfo]![Combo44] & "'"
End IF
IF nz( Forms![AIM_OfferInfo]![Combo46],"" ) <> ""
   IF asFilter <> "" THEN asFilter = asFilter & " AND "
   asFilter = asFilter & YrSeason = "' & Forms![AIM_OfferInfo]![Combo46] & "'"
End IF

msgbox "Filter is " & asFilter  'Use to verify you are passing exactly what you want, remove after you finish debuggin.

Me.Filter = asFilter
Me.FilterOn = True
Me.Form.Requery

You would then simply call TCO_Filter for each of your combo's.

Were I you, I'd try this out to see if it will work for you. I don't have your app so I can't actually try this.
 

Users who are viewing this thread

Top Bottom