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

ES-Components
ES-Components used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

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

Author

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
Distinguished Expert 2017

Commented:
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 & "')"
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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.....
Distinguished Expert 2017

Commented:
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.
Software & Systems Engineer
Commented:
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

Author

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.
John TsioumprisSoftware & Systems Engineer

Commented:
Glad you liked it...:)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial