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: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2020-01-22T16:43:25+00:00

    Thanks! I started a new DB to test this and have run into some issues and have questions on that too. 

    I created all the queries then the form.. did you name it something besides Form1? and should the forms record source be the tblLink or tblLinkTemp? ColumnA1 in the form is not anywhere in either table... 

    then the module... i changed the name to RowNumber, right?

    Then i saved all that and tried to open the form and i get: 

    1. a popup that says:  You are about to delete 0 row(s) from the specified table. click yes/no (i clicked yes)

    2)popup: Run-time error '3085':

    Undefined function 'RowNumber' in expression

    and if i debug it highlights this line in the form open event: 

    dbs.Execute "qryModApp", dbFailOnError

    and last question about this. In my original DB the TitleT has WAY more data fields than just whats listed. Would these 2 tables be in addition and linked with relationships or should that tblLink include all of the other data as well?

    Was this answer helpful?

    0 comments
  2. Anonymous
    2020-01-22T17:00:20+00:00

    Ken, I've gotten the list to show all the data i need. I just need the grouping part (IIf(Count(*)=1,SeriesT.Series,"") AS SeriesGroup) to work. 

    I tried to use a clean new query to select the items from that query to get the same listing:

    SELECT TitleUnivGroup4Q.USG AS UniverseGroup, TitleUnivGroup4Q.Universe, TitleUnivGroup4Q.Series, TitleUnivGroup4Q.Title, TitleUnivGroup4Q.TitleID

    FROM TitleUnivGroup4Q

    GROUP BY TitleUnivGroup4Q.USG, TitleUnivGroup4Q.Universe, TitleUnivGroup4Q.Series, TitleUnivGroup4Q.Title, TitleUnivGroup4Q.TitleID;

    and then I added the count part to the first selected item: 

    IIf(Count(*)=1,TitleUnivGroup4Q.USG,"") AS UniverseGroup

    and nothing happens, i also added the ORDER BY with all the same items as the GROUP BY and still no change to the list when the query is ran. 

    Can you help me to understand why that is not working or if i'm missing something else?

    Was this answer helpful?

    0 comments
  3. Anonymous
    2020-01-22T18:06:34+00:00

    .................I just need the grouping part (IIf(Count(*)=1,SeriesT.Series,"") AS SeriesGroup) to work.

    You can only do that by joining two instances of the table, which can be a query's result table of course, not necessarily a base table.  The whole methodology is based on the fact that when two instances of the table are joined on the values in the column which identifies each subset of rows being equal, and the value in each row in each subset in one instance being less than or equal to the value in the currently referenced row in the other instance, the COUNT operator then returns what is effect is a set of sequential numbers from 1 upwards per subset, enabling the first row per subset to be identified.

    You haven't commented on my earlier post regarding the alternative solution of using correlated list boxes in an unbound form.  As I understand it the form which you are attempting to create will be used effect as a dialogue for filtering another form, so, to my mind, it would make sense to use an actual dialogue form rather than a bound form masquerading as such.

    Was this answer helpful?

    0 comments
  4. 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