Why doesn't my Subform Requery with a Combo Text Box?

Why doesn't my Subform Requery? I have 3 Combo Text boxes along with a Refresh Button and a Print Button on the Form.
The code for these are below. I have also attached the file for reference. The form in question is TestNEWForm2.
What am I doing wrong??
Thank you in advance for any help you can offer.

Rick

Private Sub CmbCustomerSearch_AfterUpdate()
Dim MyCustomerSearch As String
MyCustomerSearch = "SELECT * From [tbl_Customer] Where ([Field3] ='" & Me.CmbCustomerSearch.Column(1) & "')"
Me.tbl_Customer_subform1.Form.RecordSource = MyCustomerSearch
Me.tbl_Customer_subform1.Form.Requery
Me.CmbWriters2 = Null
Me.CmbPriceLines2 = Null
End Sub



Private Sub CmbPriceLines2_AfterUpdate()
Dim MyPriceLines2 As String
MyPriceLines2 = "SELECT * From [tbl_Customer] Where ([Field10] ='" & Me.CmbPriceLines2.Column(1) & "')"
Me.tbl_Customer_subform1.Form.RecordSource = MyPriceLines2
Me.tbl_Customer_subform1.Form.Requery
Me.CmbWriters2 = Null
Me.CmbCustomerSearch = Null
End Sub







Private Sub CmbWriterr_AfterUpdate()
Dim myWriter As String
myWriter = "SELECT * From [tbl_Customer] Where ([Field17] ='" & Me.CmbWriterr.Column(1) & "')"
Me.tbl_Customer_subform1.Form.RecordSource = myWriter
Me.tbl_Customer_subform1.Form.Requery
Me.CmbPriceLines2 = Null
Me.CmbCustomerSearch = Null
End Sub


Private Sub Command74_Click()
DoCmd.RunCommand acCmdPrintSelection
End Sub




Private Sub ReFresh_Click()

Me.tbl_Customer_subform1.Form.RecordSource = "tbl_Customer"

End Sub
CustomerSalesHistoryNew.zip
ES-ComponentsAsked:
Who is Participating?
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.

PatHartmanCommented:
I don't see any form with three combos and I looked at all NINE forms.  You might have told us what form we needed to look at.  Form1 seems to be the most complete form and for it's ONE combo, the subform filters correctly.

PS best practice is to give objects meaningful names Field3, Text98, etc are completely inappropriate object names.  You also have queries prefixed with tbl_  - that's going to cause confusion.  You save yourself a lot of work and prevent subtle bugs when you use some discipline in naming.
0
ES-ComponentsAuthor Commented:
Pat
I have uploaded the file again. There are 12 Forms in the file. Please look at the form called
"TestNEWForm2.   This is the one that does not work.

Thank you.
Rick
CustomerSalesHistoryNew2.accdb
0
PatHartmanCommented:
The problem was that you changed the combo so that it only returned a single field but in your SQL you were referring to the SECOND column

This is the fix

myWriter = "SELECT * From [tbl_Customer] Where ([Field17] ='" & Me.CmbWriterr & "')"
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

ES-ComponentsAuthor Commented:
Thanks Pat,
I just figured it out. One additional thing. Now that it works based on what you just showed me, is there a way I can add just 2 more text boxes with a button that I could enter a date range that would select out the range from the subform?

Please let me know. I will be awarding you for the Best Solution.

Rick.....
0
PatHartmanCommented:
You just use the same technique.  Add the two textboxes.  Set their format to Short Date so that Access knows they are supposed to be dates and will show the date picker.

Rather than putting the code in the click event of the combo, Add a button.  The click event of the button should verify that all three fields are present and then build the SQL String.
1
John TsioumprisSoftware & Systems EngineerCommented:
I think that my attachment will cover all your needs..
Also no need for Refresh....just make your selections...the records will get filtered on the spot...and when you want to start over just press the Clear Filters button.

JT
CustomerSalesHistoryNew2.zip
1

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:
Thank you John,
I liked your solution to my problem. I am not a programmer, so my attempt at solving the problem may not be the best way. It took me a little bit to understand your code solution, and without a doubt yours is the best!!
I can now move on to the next section of this project.
0
John TsioumprisSoftware & Systems EngineerCommented:
Glad you liked it...:)
0
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.