Results 1 to 14 of 14
  1. #1
    burt1215 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    34

    Preventing the Report From Jumping to the Top when the RecordSource is Updated

    Hey, so I have a report that contains five subreports that track applicants and where they are on their application status.
    Each subreport has a button to move the applicant to the next report.

    To 'refresh' the reports, I just reset the recordset.



    Code:
    Forms!frmGUI!fsubSchedule.Report!rsubApplicant4.Report.RecordSource = Forms!frmGUI!fsubSchedule.Report!rsubApplicant4.Report.RecordSource
    Forms!frmGUI!fsubSchedule.Report!rsubApplicant5.Report.RecordSource = Forms!frmGUI!fsubSchedule.Report!rsubApplicant5.Report.RecordSource
    This moves it from subreport 4 to subreport 5.

    My problem is when I do that, the detail of my main report does not shrink or grow to reflect that, unless I use
    Code:
    Forms!frmGUI!fsubSchedule.Report.RecordSource = Forms!frmGUI!fsubSchedule.Report.RecordSource
    Which updates the recordsource of my main report.

    The problem with doing that is it jumps the report to the very top, which gets annoying when you have hundreds of applicants.

    Can anyone think of a fix for this?

    Thanks

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Reports always start at the first record. You can't edit records in a report, so why start the report in the middle?

  3. #3
    burt1215 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    34
    My reports are dynamic.
    Like, as the application process goes on, more information needs to be entered.
    Here's a good example. I have a field called "Paperwork Complete." It's a boolean.
    I have it set up so if you click the box it will toggle between yes and no, editing the record in the report.

    Let's say I just got through a big training group, or ten people just finished their paperwork.
    The reason I would want to 'start' in the middle is because I'm trying to move a bunch of people from sub report 4 to sub report 5 cause they completed training.

    But if I move one person to the next level, updating the recordsource just brings me back to the top.
    Doing this for ten different people get's pretty tiresome.

    Does that make sense?

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    After you refresh the main report, you could set the focus to the subreport, or to the first record of the subreport. Not sure if this will work, or if it will create jumping.

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Why are you doing this on reports instead of forms?

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I had this problem and didn't find a solution right off. In my case it wasn't a big deal so I didn't look that hard. I used report view to get the grouping/sorting ability of a report and the interactivity of a form. I've only used report view a couple of times.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    burt1215 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    34
    Quote Originally Posted by aytee11
    After you refresh the main report, you could set the focus to the subreport, or to the first record of the subreport.
    I like it. It's a decent fix, and is pretty simple. Assuming it works, it'll still cause a little bit of jumping, but it will keep the report in generally the same area.
    I think what I'm going to try is get rid of the code that updates the RecordSource for the main report. (No more jumping now)
    And then I'm going to calculate how much space each row takes, and add code to resize the details of the subreports and move the top of the subreports as records are moved from one to the next.

    Quote Originally Posted by rpeare
    Why are you doing this on reports instead of forms?
    Honestly, it's been about two months since I created this report, so I'm not sure the exact reason, but I started with a form and it wasn't doing what I wanted it to do, so I switched to a report and it worked the way I wanted it to work. So I stuck with reports. And now here we are...

    Quote Originally Posted by pbaldy
    I had this problem and didn't find a solution right off. In my case it wasn't a big deal so I didn't look that hard. I used report view to get the grouping/sorting ability of a report and the interactivity of a form. I've only used report view a couple of times.
    Yeah, it's a big deal when you are have to scroll through 100+ applicants every time you want to change someone's paperwork to complete...
    Did you ever find a solution?

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by burt1215 View Post
    Yeah, it's a big deal when you are have to scroll through 100+ applicants every time you want to change someone's paperwork to complete...
    Did you ever find a solution?
    I don't doubt it; my point was that it wasn't a big deal in my app. For that reason I never researched it further, so no, never solved it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    burt1215 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    34
    Quote Originally Posted by pbaldy
    I don't doubt it; my point was that it wasn't a big deal in my app. For that reason I never researched it further, so no, never solved it.
    What a bummer.
    Well if I actually solve it, I'll let you know.

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Thanks! I always like to learn.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    burt1215 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    34
    I remember why I used subReports instead of subForms.
    The subReports automatically grow and shrink to display all of the records.
    When you have five subReprots, they grown and shrink together so it's one right after another.

    Forms don't do that (as far as I know, and a quick google search was no help.)

    However, forms are nicer, so I'm going to switch to subForms.

    In order to control growth and simulate auto resizing, I am going to do something like (DCount * RowHeight) + HeaderHeight, and then just recalculate and resize every time a record is moved.

  12. #12
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    If you have a sample of your database you can share I would like to take a look at it because I've never done any data manipulation through reports before. Secondly, the point of forms is that you don't really need to display *all* the data, if you are just moving people between groups you can do that with a single form using a 'short' list with the names of the people and a few other pieces of information to make the data identifiable and then rely on the report to show the 'final' product rather than trying, I think it'd be far more efficient than scrolling through several pages to find what you're after each time.

  13. #13
    burt1215 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    34
    Okay, like a week later. I switched to forms. It still jumps on update. But I also figured out a way to move it back to where it needs to go.
    The data on my form is set to a query, which can't be edited from a form, so I had to continue using "update table set value = whatever" in vba and then updating the RecordSource.

    After updating the table, I run this code.

    Code:
    Dim intCurrentRecord As Integer
    Dim intOffset As Integer
    Dim intTopRecord As Integer
    
    
    intCurrentRecord = Me.CurrentRecord
    intOffset = (Me.CurrentSectionTop - 1058) / 300
    intTopRecord = intCurrentRecord - intOffset
    
    
    Forms!frmGUI!fsubSchedule.Form!Child0.Form.RecordSource = Forms!frmGUI!fsubSchedule.Form!Child0.Form.RecordSource
    
    
    DoCmd.GoToRecord acActiveDataObject, , acLast
    DoCmd.GoToRecord acActiveDataObject, , acGoTo, intTopRecord
    It works. The only issue I have with it is there's a real quick flicker.

  14. #14
    burt1215 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    34
    Quote Originally Posted by rpeare
    Secondly, the point of forms is that you don't really need to display *all* the data, if you are just moving people between groups you can do that with a single form using a 'short' list with the names of the people and a few other pieces of information to make the data identifiable and then rely on the report to show the 'final' product rather than trying, I think it'd be far more efficient than scrolling through several pages to find what you're after each time.
    Here's a screenshot of what it looks like. As you can see, I am very good at redacting personal information.
    Click image for larger version. 

Name:	AT.jpg 
Views:	8 
Size:	210.3 KB 
ID:	25863
    To me, that's the smallest amount of information required for the user to sort through. That or I don't understand what you're saying there.

    Quote Originally Posted by rpeare
    If you have a sample of your database you can share I would like to take a look at it because I've never done any data manipulation through reports before.
    Sure! Let me make a copy of my backend and get rid of all the personal information then I'll send you a PM with a link or something.

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

Similar Threads

  1. Replies: 2
    Last Post: 10-01-2014, 12:38 PM
  2. Replies: 0
    Last Post: 05-09-2011, 01:51 PM
  3. Replies: 1
    Last Post: 04-25-2011, 12:20 PM
  4. Replies: 1
    Last Post: 02-03-2010, 08:27 AM
  5. Changing A Report Recordsource from a form
    By warrenjburns in forum Reports
    Replies: 0
    Last Post: 04-09-2009, 05:38 AM

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