Link to home
Create AccountLog in
Avatar of ES-Components
ES-ComponentsFlag for United States of America

asked on

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
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

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
if your search combo box is in the subform (maybe in the header), then I would use:

me.Filter = strFilter
me.FilterOn = True
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.
ASKER CERTIFIED SOLUTION
Avatar of Paul Cook-Giles
Paul Cook-Giles
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of ES-Components

ASKER

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