Results 1 to 5 of 5
  1. #1
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,796

    Report RecordSource Count

    I have a report where I want to give preference to a recordsource where certain records have been "Selected". I.e., I toggle the RecordSource with a simple filter. The code below works fine in a form, but for some reason not in a report. I tried the code in both the OnLoad and OnCurrent events but it made no difference. I can solve the problem with two different RecordSource queries, but I thought there was a simpler way?
    Code:
    Private Sub Report_Open(Cancel As Integer)
    Me.Filter = "[Selected] <> ''"
    Me.FilterOn = True
    
    
    If Me.RecordsetClone.RecordCount = 0 Then Me.FilterOn = False
    End Sub


  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    2,741
    I thought data was not available on open?

    Edit: Just tried in one of my reports and filter works fine.?
    However report does not appear to have a recordsetclone and I get an error?

    Why not just open the report with a filter in the first place in the open statement?

    Code:
    Private Sub Report_Load()
    Me.Filter = "On_Date < #01/01/1965#"
    Me.FilterOn = True
    End Sub
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    2,741
    You can use
    If Reports(sReportName).HasData Then

    to see if anything exists after the filter.

    Credit to arnelgp at AWF in https://www.access-programmers.co.uk...report.299480/
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  4. #4
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,796
    I thought data was not available on open?
    Yes, me too and why I had tried the code in the OP both in the OnLoad and OnCurrent events.

    Your "HasData" suggestion works like a charm as long as the code is in the OnLoad event:
    Code:
    Private Sub Report_Load()
    Me.Filter = "[Selected] <> ''"
    Me.FilterOn = True
    
    
    If Not (Reports("rptLabels").HasData) Then Me.FilterOn = False
    End Sub
    
    Thanks,
    Bill

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    2,741
    Well I would have thought it would work in any event that could actually see the data?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 05-24-2021, 02:01 AM
  2. Replies: 10
    Last Post: 04-08-2019, 04:48 PM
  3. MS Access VBA Me.RecordSource report will not save
    By ramirezx@ddmfg.com in forum Access
    Replies: 1
    Last Post: 02-15-2018, 05:02 PM
  4. Replies: 3
    Last Post: 01-13-2016, 12:34 PM
  5. Report RecordSource record count at Open
    By GraeagleBill in forum Reports
    Replies: 11
    Last Post: 04-19-2013, 02:03 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums