Problem with using filters and using combobox to find records

[Ed]

I am using the technique to find a record based on a combobox drop down (see
http://allenbrowne.com/ser-03.html if interested in technique). My solution
has 2 combo boxes: the first is to select a company and the second selects an
employee within the company.

When you change companies, the form loads all the employees for the company
and then I can use the second combobox to find a particular employee and
display them.

I found an issue that occurs if I filter the form and then I change the
company in the drop down. The recordset will not load the employess for the
new company I selected (but the employee combobox does load the correct new
employees). I have to close the form and re-open it to be able to change
companies after I use filtering. I tried putting in code to turn off the
filter and also set it to "" but that did not solve this problem.

 

[John W. Vinson]

I am using the technique to find a record based on a combobox drop down (see
http://allenbrowne.com/ser-03.html if interested in technique). My solution
has 2 combo boxes: the first is to select a company and the second selects an
employee within the company.

When you change companies, the form loads all the employees for the company
and then I can use the second combobox to find a particular employee and
display them.

I found an issue that occurs if I filter the form and then I change the
company in the drop down. The recordset will not load the employess for the
new company I selected (but the employee combobox does load the correct new
employees). I have to close the form and re-open it to be able to change
companies after I use filtering. I tried putting in code to turn off the
filter and also set it to "" but that did not solve this problem.

In addition to changing the value of the Filter property try setting the
FilterOn property to No.

 

[Ed]

I tried that and the result is the same. Here is my code:

Private Sub cboCompany_AfterUpdate()
Dim stDocName As String

Me![cboEmployee] = Null
Me![cboEmployee].Requery

If Me.FilterOn Then
Me.Filter = ""
Me.FilterOn = False
End If

stDocName = "Requery"
DoCmd.RunMacro stDocName

End Sub

When I step through the code, the first time this event occurs after I
applied my form filter, the code steps through and sets the filter to empty
string and turns off filtering. The forms record set does not update and
still has the filtered employees from the previous company. When I change
the company again and step through the code, the "If Me.FilterOn Then" code
evalutes to False so the IF block code is skipped. The resulting records in
the form remain the filtered employees.

 

[Charles Wang [MSFT]]

Hi Ed,
Good morning!

Regarding this issue, to let me better understand your issue, could you
please send me (changliw_at_microsoft_dot_com) a test copy of your Access
file so that I can quickly reproduce your issue at my side and perform
research for you?

Please do not hesitate to let me know if you have any other questions or
concerns.

Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================