Results 1 to 5 of 5
  1. #1
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Functions forNext/Previous value.

    I attach a demo with examples of functions you want.

    They are just in a module, so to run them you would need to use the immediate window.

    At the moment they test for Prev and Next not exisiting, and returning zero in this case. You will need to change that to what you want.

    What about the case where the value passed to the function is not currently a value in the table. You might need to change the fidFirst to >= or <= ? I am not sure what you want in this case. Maybe it is not an issue.

    Added by John later - I realised that I left out rs.close in the functions. This should go before set rs = nothing in each function,
    Regards
    John



  2. #2
    Star Lounger
    Join Date
    May 2006
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Functions forNext/Previous value.

    Thanks John.

    That looks like it will do the trick with a little modification.

    Here is an edited excerpt for others to view for now. Does not include your SQL statements or the error trapping.
    -------------------------------------
    If Not rs.EOF Then
    rs.FindFirst "[Key]=" & varCurrent
    rs.MoveNext
    ' Use rs.MovePrevious for the funcPrev routine
    funcNext = rs("key")
    --------------------------------------
    I will work on this and provide sample of final code that I use.
    Thank you again for your efforts.

  3. #3
    Star Lounger
    Join Date
    May 2006
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need functions forNext/Previous value.-SOLVED

    Perhaps these functions exist, but I have been unable to locate them, don't even know what to call them.
    But they must exist because they would be used in things like tax tables.

    Environment:
    tblTest records have key values that are numbers. Normally entered by 10,s with room for expansion, like old basic programming statements.
    At times new records will be inserted in between so sorting can be done by key value, order maintained, no renumbering needed.
    Sample key values: 10,13,15,20,25,30,35,37,40,50. The tblTest has default sort by this key field.

    I need two functions to get next/previous serial, non-sequential number, relative to a known value:
    A. funcPrev(varCurrent as Int): Need the highest key value beneath value submitted. funcPrev(40) wlll get back 37.
    B. funcNext(varCurrent as Int): Need the next highest value above value submitted. funcNext(30) will get back 35.

    Actually, thinking a bit more about it, the function should return the value submitted if it exists, if not exist then get the Next/Prev value .
    So funcPrev(40) returns 40, funcPrev(39) returns 37, funcNext(13) returns 13, funcNext(33) returns 35.

  4. #4
    Star Lounger
    Join Date
    May 2006
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need functions forNext/Previous value.-SOLVED

    Thanks again for your starting code John.
    I made some minor changes:
    in funcPrev(), I had to change rs.FindFirst to rs.FindLast
    In both functions I removed rs.MoveNext or rs.MovePrevious
    I also decided not to return the varCurrent if it existed, so I did not use "<=" or ">="
    And I also had problems if there was no higher/lower number so I inserted a Message Box for now.
    So, I finally ended up with the following two functions.:
    --------------------------------------
    This is funcPrev(value) that returns the next lowest number.<pre>Public Function funcPrev(varCurrent As Long) As Long

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    On Error GoTo funcPrev_Error

    Set db = CurrentDb
    Dim sql As String
    sql = "Select tbltest.key from tbltest order by tbltest.key"

    Set rs = db.OpenRecordset(sql, dbOpenDynaset)

    If Not rs.EOF Then
    rs.FindLast "[Key]<" & varCurrent
    If rs("key") > varCurrent Then
    MsgBox ("No Lower Values")
    funcPrev = varCurrent
    Else
    funcPrev = rs("key")
    End If
    End If

    rs.Close
    Set rs = Nothing

    Exit_funcPrev:
    Exit Function

    funcPrev_Error:
    If Err.Number = 0 Then
    Resume Next
    ElseIf Err.Number = 3021 Then
    MsgBox "No Previous record. Already at beginning."
    funcPrev = 0
    Resume Exit_funcPrev
    Else
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in funcPrev ", , ""
    Resume Exit_funcPrev
    End If

    End Function</pre>

    --------------------------------------
    This is funcNext(value) that returns the next highest number.
    <pre>Public Function funcNext(varCurrent As Long) As Long

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    On Error GoTo funcNext_Error

    Set db = CurrentDb
    Dim sql As String
    sql = "Select tbltest.key from tbltest order by tbltest.key"

    Set rs = db.OpenRecordset(sql, dbOpenDynaset)

    If Not rs.EOF Then
    rs.FindFirst "[Key]>" & varCurrent
    If rs("key") < varCurrent Then
    MsgBox ("No Higher Values")
    funcNext = varCurrent
    Else
    funcNext = rs("key")
    End If
    End If

    rs.Close
    Set rs = Nothing

    Exit_funcNext:
    Exit Function

    funcNext_Error:
    If Err.Number = 0 Then
    Resume Next
    ElseIf Err.Number = 3021 Then
    MsgBox "No Next record. Already at end."
    funcNext = 0
    Resume Exit_funcNext
    Else
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in funcPrev", , ""
    Resume Exit_funcNext
    End If

    End Function</pre>

    --------------------------------------
    John's actual MsgBox values are more intelligent, but have been abbreviated in the code above for this forum.
    The actual error lines looked like this:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure funcPrev of Module Module1", , "Error in procedure funcPrev"

    Thanks again to John, these will go into my library.

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Need functions forNext/Previous value.-SOLVED

    The Error Messages come from MZ Tools MZ Tools .

    With a right click it inserts error handling using a template that you can customise.

    Of course that is not all it does, and it is free.

    Smart ident is the other add-in that I am grateful for every day.
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •