Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2009
    Location
    British Columbia, Canada
    Posts
    29

    Unanswered: Change RecordSource for report

    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!

    Code:
    Case POSTAL_CODE_SORT
    
        '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
        End If
    
    Case Else
    Last edited by nfw04; 07-06-09 at 16:15.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Rather than change the recordsource, I'd use this technique to filter the report:

    Open a second form to the record
    Paul

  3. #3
    Join Date
    Jun 2009
    Location
    British Columbia, Canada
    Posts
    29
    Wow, that's awesome. Thanks for pointing that out pbaldy.

    EDIT: I just tried it, and...problem solved! Thanks a million!

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No problem, glad it helped.
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •