How to differentiate between moving forward and moving backwards in a report?

Hi Experts,

I have the folowing code that runs for each record previewed in report

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If Nz(sPrevSNV) <> Me.SNV_ID Then
    sID = sID & ",'" & Me.SNV_ID & "'"
    iCount = iCount + 1
End If
sPrevSNV = Me.SNV_ID
End Sub

Open in new window

This is meant for keeping track which record was already reviewed, so upon closing the report I run the following.
            s = "Update SNV_Printed_History set ReviewedDate = #" & VBA.Date & "#"
                       s = s & " where SNV_ID in (" & Mid(sID, 2) & ")"
            CurrentDb.Execute s

Open in new window


Now would like to change that as follows.

if report was moved forward, should work as is, however if was moved backwards, meaning user went to previous viewed record, then should work the opposite, meaning it should subtract from the sID variable the last viewed record .

So in other words, if lets say user viewed the first 8 records, then moved back to record 6, it should only update the first 6 records upon closing report.

Thanks in advance.
LVL 6
bfuchsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
I don't understand the logic of "unseeing" something that has been viewed.    I do see how backward movement adversely impacts your counting.  Maybe instead of tracking the entries in a string, it would be better to log them in a table where you can define the SNV_ID as a unique identifier.  That way it wouldn't matter if the user scrolls backward as well as forward because it would still be logged only once.

Nothing is perfect though.  Keeping user tables in a multi-user environment contains its own set of issues.  The table will need to include the userID so that multiple people can be reviewing data at the same time.  When the review is done, the update is applied from the table.  OR, maybe applying the viewed date at the time the record is viewed is the better solution.  Even though running individual updates is technically less efficient, It is hard to believe this would be a problem given the speed of a human reading a report.  It is unlikely that the time taken for the update on each page would be noticeable.  That makes the process pretty simple.  PS - if you are doing this and I won't even question the rational, you should almost certainly be logging who viewed the record as well as the date or date/time it was viewed.
0
bfuchsAuthor Commented:
Hi,

I don't understand the logic of "unseeing" something that has been viewed
Initially users had requested to know which record they already reviewed.
Now they say that sometimes they do open a record but don't have time to finish reviewing it, so in that case they simply want a way to rollback...

Re multi user, I'm not so concerned as at the moment only person is using it, will worry when things get changed.

Thanks,
Ben
0
PatHartmanCommented:
I'm not so concerned as at the moment only person is using it, will worry when things get changed.
That is short sighted.  Why would you want to do something twice?

So, what is wrong with my suggestion to update the viewed date directly rather than creating a string?  That actually allows you to change the RecordSource for the report to select only unviewed items.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

bfuchsAuthor Commented:
I'm not sure what is your suggestion.
I've currently for this table ReviewedDate and ReviewedBy fields, and that string updates as follows.
Private Sub Report_Close()
    Dim s As String, b As Boolean
    If MsgBox("Mark " & iCount & " as reviewed", vbYesNo + vbDefaultButton1 + vbQuestion) = vbYes Then
        If Len(Me.Filter) > 0 Then
            s = "Update SNV_Printed_History set ReviewedDate = #" & VBA.Date & "#"
            s = s & ", ReviewedBy = '" & Forms!Main.ComboInitials & "'"
            s = s & " where SNV_ID in (" & Mid(sID, 2) & ")"
            CurrentDb.Execute s
            b = True
        End If
        
    End If

Open in new window


Thanks,
Ben
0
Gustav BrockCIOCommented:
No reason to concatenate the date:

s = "Update SNV_Printed_History set ReviewedDate = Date()"

Open in new window



And you could use a collection to pick up the IDs:

Option Compare Database
Option Explicit

Private ViewedRecords   As New Collection


Private Sub Detaljesektion_Print(Cancel As Integer, PrintCount As Integer)

    On Error Resume Next
    ViewedRecords.Add Me!SNV_ID.Value, CStr(Me!SNV_ID.Value)

End Sub


Private Sub Report_Close()

    Dim Index   As Integer
    Dim Viewed  As String
    Dim s      As String
    
    For Index = 1 To ViewedRecords.Count
        Viewed = Viewed & ViewedRecords.Item(Index) & ","
    Next
    Viewed = Left(Viewed, Len(Viewed) - 1)
    
    If ViewedRecords.Count > 0 Then
        If MsgBox("Mark " & Index & " as reviewed", vbYesNo + vbDefaultButton1 + vbQuestion) = vbYes Then
            s = "Update SNV_Printed_History Set ReviewedDate = Date()"
            s = s & ", ReviewedBy = '" & Forms!Main.ComboInitials & "'"
            s = s & " Where SNV_ID In (" & Viewed & ")"
            CurrentDb.Execute s
        End If    
    End If

    Set ViewedRecords = Nothing
    
End Sub

Open in new window

0
John TsioumprisSoftware & Systems EngineerCommented:
I think that capturing the backward movement of the report viewing is not an easy task ...maybe API calls could help but i am not sure..
On the other hand you could play smart...disable the built in vavigation and place on the report buttons that will handle this task...it would require some work to change the  recordsource of the report according to each individual record but i think is viable...that way you could have total control on the viewing status of your reports.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
If I understand correctly, I would setup an in-memory array to keep track of the records.   I would then update the records on report close.

While viewing the report, you would update the flag for each page to true (reviewed).  You would also erase the array from the current page + 1 for all pages each time.

So view page 1, set the flag
So view page 2, set the flag
View page 3, set the flag
View page 4, set the flag

move back to page 3, erase the flag for page 4
move back to page 2, erase the flag for page 3

close report.

 Pages 1 and 2 are the only two "reviewed".

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
The other option would be to write all pages viewed in the report to a temp table.  Then upon close put up a continuous form based on that asking which of the viewed pages are "review completed".

Then write the records based on that

Jim.
0
PatHartmanCommented:
You're welcome Ben.  If you are happy with my suggestion, please close the question.
0
bfuchsAuthor Commented:
@Gustav,
I dont think it works in my version of Access(2003), getting error "missing object".
Also it not addressing the main q, how to unset the reviewed records.

@John,
it would require some work to change the  recordsource of the report according to each individual record but i think is viable
Not sure why is this necessary, there is no MoveNext/MovePrevious event in reports?
@Jim,
So your idea is based on a true false logic, meaning if something was set already to true then next time is false?
In that case I will have a problem as the code to capture ID's is running on Detail_Print event, and for some reason it gives me same ID twice (this is why I check for "If Nz(sPrevSNV) <> Me.SNV_ID Then".
How can I overcome that?

Thanks,
Ben
0
Gustav BrockCIOCommented:
I dont think it works in my version of Access(2003), getting error "missing object".

Can't tell. Tested and works nicely here in Access 2016.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<So your idea is based on a true false logic, meaning if something was set already to true then next time is false?>>

 No.  Everything would start out as false.   As each page is viewed, you would set the flag for that page to True. When the user backs up, you would erase all the true flags after that page.  This would assume that the review process is sequential.

 If that's not the case, then I would go with the second suggestion I made; set a true flag for each page as it viewed.   On report close, write the pages to a table.  Then open a form based on that table and ask which pages should be considered "complete" for review.   based on that, actually write the log record that the review is complete.

Jim.
0
bfuchsAuthor Commented:
When the user backs up,
This is the question, how to identify it?
This would assume that the review process is sequential.
Thats not always the case.
Then open a form based on that table and ask which pages should be considered "complete" for review
Would prefer not to have users do additional tasks if it can be done auto, as its being used very often.
On report close, write the pages to a table.
Actually if will have to use temp table for this, I can already go with your 1st suggestion and store the order in that table.

Wondering if there isn't an easier way to accomplish this?

Thanks,
Ben
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Wondering if there isn't an easier way to accomplish this?>>

 It's not clear (at least to me) what the process is, so it's hard to guess what might work or not.

Jim.
0
John TsioumprisSoftware & Systems EngineerCommented:
Well some clarification on my proposal...well with my suggestion you kind of forget the usual way reports works....don't think them as a recordset spread in a number of pages but instead individual reports based on a recordset....
So -if i am not mistaken you are in medical business- so probably your reports are patient/treatment/medicine related...you put the criteria and voila the report is generated..in the case i proposed again you pull the data but each page/set of pages behaves autonomous..so that page1 is the filtered recordset that represents only the data for this single case
So for example lets say that you have  patients records...with the criteria you entered you retrieve 3 patients...you hit the report...the report goes to the 1st patient but the report essentially is just the report for this single patient ...there is no next page...you hit the next button you go to another patient but essentially is the same single report with a changed recordsource...
I guess it will take some fiddling but based on your needs might worth the effort
0
bfuchsAuthor Commented:
Thanks to all participants.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.