Why doesn't the Afterupdate code select the records in the subform?

I have attached a file. If you open the main form and try selecting a writer, the combo box does not work. I used the code below.
What is wrong?

Thanks...
Rick

Private Sub CmbSearch_AfterUpdate()

If Len(Form_WriterTotalByWriter_subform.Filter) > 0 Then
Form_WriterTotalByWriter_subform.Filter = Form_WriterTotalByWriter_subform.Filter & " AND " & "Writer = '" & Me.CmbSearch & "'"
Else
Form_WriterTotalByWriter_subform.Filter = "Writer = '" & Me.CmbSearch & "'"
End If
Form_WriterTotalByWriter_subform.FilterOn = True
End Sub
BookOrders.accdb
ES-ComponentsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
ES,

On my iPad, so cannot look at the actual code.  First thought would be the way you are referring to the filter property of the subform.  The syntax for referring to a subform, from a main form is:

me.subformControlname.Form.Filter = strFilter
me.subformControlname.Form.FilterOn = Yes
Dale FyeOwner, Developing Solutions LLCCommented:
if your search combo box is in the subform (maybe in the header), then I would use:

me.Filter = strFilter
me.FilterOn = True
Dale FyeOwner, Developing Solutions LLCCommented:
Also, if there is already a filter on a writer;

"Writer = 'Shakespeare'

Then you code would include that and add the new writer with an AND, so it might look like:

"Writer = 'Shakespeare' AND Writer = 'Chaucer'

which would never be true, and would return no records.  You might want to consider rewriting that to use an "OR" instead of the "AND" if you want to be able to search for more than one writer.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Paul Cook-GilesSenior Application DeveloperCommented:
The most significant reason for your code not working is that --while your combo box displays the names of the writers-- it actually contains their ID.  When you pass the filter text to the subform, you're sending Writer = '1', not Writer = 'Bob'.

Use this syntax to get the value displayed in the combo box:
If Len(Me.CmbSearch) > 0 Then
   Me.WriterTotalByWriter_subform.Form.Filter = "Writer = '" & Me.CmbSearch.Column(1) & "'"
   Me.WriterTotalByWriter_subform.Form.FilterOn = True
End If

Open in new window


You might consider added code to the GotFocus event of the combo box to remove the code from the subform, so that when you're ready to start a new search, all the records are visible.  :)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ES-ComponentsAuthor Commented:
Paul,
I just tried your suggestion. It worked!! I will award you the solution. If I had not had and ID Column in the Writer table, I assume my code would have worked?  

Thanks...
Rick
Dale FyeOwner, Developing Solutions LLCCommented:
ES,

No, because your Writer column in your table actually contains a number, not a string.

Also, don't forget my comment that this segment of code:

If Len(Form_WriterTotalByWriter_subform.Filter) > 0 Then
    Form_WriterTotalByWriter_subform.Filter = Form_WriterTotalByWriter_subform.Filter & " AND " & "Writer = '" & Me.CmbSearch & "'"
Else

Will not work if you already have another filter on the subform.  Rewritten, it will look like:

If Len(Form_WriterTotalByWriter_subform.Filter) > 0 Then
    Form_WriterTotalByWriter_subform.Filter = Form_WriterTotalByWriter_subform.Filter & " AND " & "Writer = " & Me.CmbSearch.Column(1)
Else

Open in new window

But that will result in a filter that looks like:

Writer = 1 AND Writer = 2
ES-ComponentsAuthor Commented:
Yes Dale, you are correct also. Thanks for your help! I am a beginner at this Access stuff, so all of these suggestions are quite helpful
Paul Cook-GilesSenior Application DeveloperCommented:
>I just tried your suggestion. It worked!! I will award you the solution.

Excellent-- thank you!

>If I had not had and ID Column in the Writer table, I assume my code would have worked?  

Yes, I expect it would have.  Your concept was sound;  the only glitch was that you weren't giving the filter exactly what it wanted.  :) Concatenating the single quote with the criteria was an excellent thing;  shows that you get the difference between numeric criteria (which don't get the quote) and text critiera, which have to have the quote.  Keep up the good work!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.