Results 1 to 7 of 7
  1. #1
    Glenley is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    87

    Losing Report Recordsource in Print Preview


    I have three reports that trigger when running a billing operation. All three of these reports have Recordsource set in VBA. Everything seems to be running fine but recently I've noticed when I switch to Print Preview mode, I lose all of my data. I would prefer to open them in PP view but I understand that I can't set the recordsource in PP view so I have them set to open in Report View. Does anyone have any wisdom on this? TIA

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Lots of ways to deal with it. However, the first report I looked at in design view has a recordsource property "WeeklyApplicatorNumberQ" so are you really setting report sql in vba or perhaps you just think that's the case. I've never had both in place at the same time so I can't tell you what you should expect.
    So solutions
    - leave the report source in place & use the sql to edit the query definition
    - you can pass rather long strings to OpenArgs property then set recordsource to that string (few times I've tried never raised an error)
    - you could modify the report recordsource property before attempting to open
    - could use a staging table that you edit each time and base the report on that. You delete the staging table records in the report close or unload event.

    1 and 3 require that you open the object in design view (hidden) modify its property, save, close and reopen.
    More ideas to come, no doubt (other than not trying to switch views and modify the property).
    Last edited by Micron; 04-20-2022 at 04:08 PM. Reason: correction
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Glenley is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    87
    I usually use the Query designer to build my SQL statement in the more complex statements. I could just use the query I built but want to use the "SDate" and "EDate" input variables. You followed my last post, so I assume you're somewhat familiar with what I'm doing. The strange thing is it doesn't lose the recordsource every time when switching views, only occasionaly. I'll study your possible solutions and try to figure something out. Thanks

  4. #4
    Glenley is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    87
    So, here is a thought. When switching views, does it trigger the SQL statement again? If so, after I run the operation the first time the records are updated to Billed. These reports are set to return unbilled records. If the SQL is running a second time after the update query, that would explain why I'm losing data. Is there really not a way to set the recordsource and open directly in PP view?

  5. #5
    Glenley is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    87
    I figured it out. Right after opening in Report view and setting the RecordSource, run a Docmd.OpenReport in PP view before the Update Queries run

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Interesting. I would have thought opening an open report would just make the already open report the active window. It usually does in other circumstances.
    If it doesn't hold up, consider the staging table. Methinks it's less work because if mods are needed, all you have to do is tweak the query, assuming you don't need additional report fields as well.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Glenley is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    87
    So far this seems to have fixed the issue. If I have any other problems, I'll post them here

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

Similar Threads

  1. Replies: 1
    Last Post: 12-13-2018, 01:06 PM
  2. Replies: 3
    Last Post: 04-28-2017, 09:51 AM
  3. Replies: 5
    Last Post: 08-06-2015, 03:26 PM
  4. Replies: 1
    Last Post: 02-21-2015, 11:35 PM
  5. Replies: 6
    Last Post: 03-01-2014, 07:07 AM

Tags for this Thread

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