Hi guys, I've been fighting with this issue for a few days, all help will be appreciated!
I'm trying to make a report that will change the data it's presented based on form input. The report groups all sales by the first three characters of their postal code (thus giving insight into sales by geographic location). Right now, when the field is left empty, it shows this report. I would like it to behave so that if there are three characters in the field, it will change the RecordSource of the report to include just postal codes beginning with those three characters. However, after implementing the code, no records are filtered.
The funny thing is, I've got this implemented for a few forms and it runs fine. I've found with a MsgBox that the code is entering the select case that I've put my code into. I've also created a brand new report with the query I wanted, and it works that way. I just can't seem to get it to work by changing the RecordSource property.
I could just use the other report I've created, but I would like to fully explore this avenue first.
Thanks for helping!
'Define SQL query
sqlString = "SELECT [Postal Code Prefix Subquery].Prefix, [Postal Code Prefix Subquery].[Postal Code], [Postal Code Prefix Subquery].[First Name], [Postal Code Prefix Subquery].[Last Name], Sales.[Invoice Number], Sales.Date " & _
"FROM [Postal Code Prefix Subquery] INNER JOIN Sales ON [Postal Code Prefix Subquery].[Customer ID] = Sales.[Customer ID] " & _
"WHERE [Postal Code Prefix Subquery].Prefix='" & [Forms]![Sale Queries]![QueryEntry] & "'"
'If no input, run full report
If IsNull(QueryEntry) Then
DoCmd.OpenReport "Sales by Postal Code Prefix", acViewReport
'If first three characters are entered, filter out other prefixes
ElseIf Len(QueryEntry) = 3 Then
DoCmd.OpenReport "Sales By Postal Code Prefix", acViewReport
Reports.Item("Sales by Postal Code Prefix").RecordSource = sqlString
'For debugging purposes. This MsgBox DOES come up, confirming that this branch is executed
MsgBox "RecordSource changed."
'TODO: complete this case
ElseIf Len(QueryEntry) = 6 Then
DoCmd.OpenReport "Sale Postal Code Lookup", acViewReport