We help IT Professionals succeed at work.

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

ES-Components
on
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
Comment
Watch Question

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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.
Senior Application Developer
Commented:
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.  :)

Author

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, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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

Author

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 Developer

Commented:
>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!