Find Previous Record field data, or better suggestion

Anonymous
2020-01-13T19:45:54+00:00

I am trying to find the previous record value/field in a query to apply conditional formatting to my form. I have been researching using LAG function, but just can't make it work for what I need or am just not doing it right. 

What I have is a form that lists(and more): 

SERIES - TITLE

Series1 - Title1

Series1 - Title2

Series2 - Title3

Series3 - Title4

Series3 - Title5

Series3 - Title6

and I want to add a conditional format to hide the series values that repeat from the record above to show: 

SERIES - TITLE

Series1 - Title1

            - Title2

Series2 - Title3

Series3 - Title4

            - Title5

            - Title6

I tried to use the LAG Function, but dont think i'm adding it correctly.

Then i also found the below code but that just returns #NAME in the field. 

Function PrevRecVal(F As Form, Keyname As String, KeyValue, FieldNameToGet As

String)

End Function

    Dim RS As Recordset

On Error GoTo Err_PreRecVal

    'The default value is zero

    PrevRecVal = 0

    'Get the form recordset.

    Set RS = F.RecordsetClone

    'Find the current record.

    Select Case RS.Fields(ID).Type

        'Find using numeric data type key value?

        Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, DB_DOUBLE, DB_BYTE

            RS.FindFirst "[" & KeyName & "]=" & KeyValue

        'Find using date data type key value?

    Case DB_DATE

            RS.FindFirst "[" & KeyName & "]=#" & KeyValue & "#"

    Case DB_DATE

            RS.FindFirst "[" & KeyName & "]='" & KeyValue & "'"

    Case Else

            MsgBox "Error: Invalid key field data type!"

    End Select

        'Move to the previous record.

        RS.MovePrevious

        'Return the result

        PrevRecVal = RS(SeriesID)

Bye_PrevRecVal:

        Exit Function

Err_PrevRecVal:

        Resume Bye_PrevRecVal

        End Function

(in the control source of the field to show the previous record)

= PrevRecVal([Form],"SeriesID",[SeriesID],"SeriesID"

can anyone tell me 1)which one is better to use(above code or a lag function), 2)what im doing wrong or how to add a lag function if that is what i need here, 3)is there a better/easier solution you can suggest instead?Help! THANK YOU!

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Anonymous
2020-01-15T22:31:40+00:00

Let's confine it to one table to start with:

SELECT T1.SeriesID,

IIF(COUNT(*) = 1,T1.Title,"") AS Title,

IIF(COUNT(*) = 1,T1.SeriesPart,"") AS SeriesPart,

FROM TitleT AS T1 INNER JOIN TitleT AS T2

ON T2.SeriesID = T1.SeriesID

AND T2.Title <= T1.Title

GROUP BY T1.SeriesID, T1.Title, T1.SeriesPart

ORDER BY T1.SeriesID, T1.Title;

If that returns the correct result table then you can add the SeriesT table to the query and return the Series column rather than the SeriesID:

SELECT SeriesT.Series,

IIF(COUNT(*) = 1,T1.Title,"") AS Title,

IIF(COUNT(*) = 1,T1.SeriesPart,"") AS SeriesPart,

FROM SeriesT INNER JOIN (TitleT AS T1 INNER JOIN TitleT AS T2

ON T2.SeriesID = T1.SeriesID AND T2.Title <= T1.Title)

ON SeriesT.SeriesID = T1.SeriesID

GROUP BY SeriesT.Series, T1.Title, T1.SeriesPart

ORDER BY SeriesT.Series, T1.Title, T1.SeriesPart;

You can of course include the SeriesID and TitleID columns in the SELECT and GROUP BY clauses if you wish, but usually these would be surrogate keys with arbitrary values of no semantic significance.

Was this answer helpful?

1 person found this answer helpful.
0 comments

48 additional answers

Sort by: Newest
Most helpful Newest Oldest
  1. Anonymous
    2020-01-22T12:20:26+00:00

    A thought occurred to me.  You said early on in this thread 'I have it set up as a form because I want to be able to filter, review and open another form based on the data shown on the form.'   Rather than using a bound form, have you considered using an unbound form with two correlated list boxes?  The first would list the distinct Series/Universe values, while the second would be correlated with this to list the Title values for whatever row is selected in the first.

    It should be relatively easy to set up the correlated controls.  It would then be a simple task to open another form filtered on the basis of other selections i9n the list box.  You'll find an example of correlated list boxes using Northwind data in Correlated.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file the list boxes are both multi-select, but whether you'd need that or conventional list boxes in which only one row can be selected would depend on your requirements for filtering the other form.  In my case the lists filter the form in which they are located, but could just as well open a separate bound form.

    Was this answer helpful?

    0 comments
  2. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  3. Anonymous
    2020-01-21T23:58:42+00:00

    I'm the same way. I've learned Access just by using the design mode feature and switching it to SQL, googling questions and all the super helpful people on this site helping fill in the blanks when I can't figure out what I'm missing! Based on the results you showed it that looks to be exactly what I need and would love to see how you achieved it.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2020-01-21T23:58:28+00:00

    .............what do you need to see exactly so i can try and get you some sample data?

    The file would need to contain all the tables which contribute to the query, each with a subset of rows such that each table is correctly normalized to at least Third Normal Form, and provides a basis for returning a result table in the desired format.   Post the file to publicly shared folder in OneDrive or similar, and post the link here.

    Was this answer helpful?

    0 comments