Google Answers Logo
View Question
Q: MS Access Guru needed: Finding the "next" and "previous" record ( Answered 5 out of 5 stars,   1 Comment )
Subject: MS Access Guru needed: Finding the "next" and "previous" record
Category: Computers > Programming
Asked by: respree-ga
List Price: $5.00
Posted: 16 May 2003 09:36 PDT
Expires: 15 Jun 2003 09:36 PDT
Question ID: 204654
Greetings.  I need help writing a Query or Visual Basic code in MS

I'm not sure which is the solution.

I'm trying to create a table that will find the next and previous
sequential records in a table. Additionally, this query (or VBA) would
append text to the text value of the 'next' and previous' record, in
this case "p-."

[SKU] is given, [PREVIOUS] and [NEXT] are calculated based on [SKU].
Note fieldnames have been bracketed.

When properly calculated, the newly created table would look something
like this:

[SKU]       [PREVIOUS]    [NEXT]
B0587159901 p-B0587159979 p-B058715991X 
B058715991X p-B0587159901 p-B0587159928 
B0587159928 p-B058715991X p-B0587159936 
B0587159936 p-B0587159928 p-B0587159944 
B0587159944 p-B0587159936 p-B0587159952 
B0587159952 p-B0587159944 p-B0587159960 
B0587159960 p-B0587159952 p-B0587159979 
B0587159979 p-B0587159960 p-B0587159901 

Note: For the first record [SKU], the [PREVIOUS] field would show the
last record. For the last record [SKU], the [NEXT] field would show
the first record.  Also, the [SKU] numbers are NOT in any ascending or
decending order.  In the live application, they are in a random order
(and must stay that way), so the solution must involving getting the
next and previous record, rather than a <> solution.

I've posted this question on the bulletin board, but could
not get a decent solution to my problem.  Read more here.

If there is any additional information needed to clarify this
question, please ask.

Please let me know if writing the VBA involves more than a few lines
of code and I will tip accordingly.

Any help would be greatly appreciated.

Request for Question Clarification by hammer-ga on 16 May 2003 10:14 PDT
Which version of Access?

- Hammer

Clarification of Question by respree-ga on 16 May 2003 11:07 PDT
Hi Hammer:

Sorry, I should have mentioned that up front.

I am running: Access 2002 (10.4302.4219) SP-2
Subject: Re: MS Access Guru needed: Finding the "next" and "previous" record
Answered By: hammer-ga on 16 May 2003 13:05 PDT
Rated:5 out of 5 stars
Hi Respree,

Here is a VBA routine for you. Paste it into a new Access module. The
routine assumes that you have two tables already existing:

Table: tblSKU
Field Name   Type
SKU          Text  Primary Key

Table: tblLinkedList
Field Name   Type
SKU          Text  Primary Key
PreviousSKU  Text
NextSKU      Text

NOTE: No line of code should wrap. Code may need to be unwrapped after
pasting into a VBA module.

' ****** Code Begin

Public Sub BuildLinkedList()
On Error GoTo ErrHandler

Dim cnn As ADODB.Connection
Dim rstSKU As ADODB.Recordset
Dim rstSKUClone As ADODB.Recordset
Dim strSQL As String
Dim strSKU As String
Dim strPrev As String
Dim strNext As String
Dim strFirstSKU As String
Dim strLastSKU As String

    Set cnn = CurrentProject.Connection
    Set rstSKU = New ADODB.Recordset
    ' Clear existing records from table
    cnn.Execute ("DELETE * FROM tblLinkedList;")
    ' Select all SKUs in the order they currently
    ' appear in the SKU table. No sort is applied.
    strSQL = "SELECT SKU FROM tblSKU;"
    rstSKU.Open strSQL, cnn, adOpenStatic, adLockOptimistic
    strSQL = ""
    If Not rstSKU.BOF And Not rstSKU.EOF Then
        ' Set first and last value for
        ' looparound
        strFirstSKU = "p-" & rstSKU!SKU
        strLastSKU = "p-" & rstSKU!SKU
        ' Clone the recordset so we can
        ' keep two bookmarks
        Set rstSKUClone = rstSKU.Clone
        ' Now rstSKU is on the first record and
        ' rstSKUClone is on the 'next' record.
        strPrev = strLastSKU
        Do Until rstSKU.EOF
            strSKU = rstSKU!SKU
            If Not rstSKUClone.EOF Then
                strNext = "p-" & rstSKUClone!SKU
                strNext = strFirstSKU
            End If
            strSQL = "INSERT INTO tblLinkedList (SKU, PreviousSKU,
NextSKU) VALUES ('" & strSKU & "', '" & strPrev & "', '" & strNext &
            cnn.Execute (strSQL)
            strPrev = "p-" & rstSKU!SKU
    End If
' Clean up memory
strSQL = ""
strSKU = ""
strPrev = ""
strNext = ""
strFirstSKU = ""
strLastSKU = ""
If rstSKUClone.State = adStateOpen Then
End If
Set rstSKUClone = Nothing
If rstSKU.State = adStateOpen Then
End If
Set rstSKU = Nothing
Set cnn = Nothing
Exit Sub
    MsgBox Err.Description
    Resume ExitMe

End Sub

' ****** Code End

Please let me know if you need clarification on this. Good luck with
your project!

- Hammer

Request for Answer Clarification by respree-ga on 16 May 2003 14:13 PDT
Hi Hammer:

Thank you so much.  I never would have figured this out in a million

This is the final piece of a monster that runs more than 125 queries
(been working on it for weeks).  I would have been devastated if I
couldn't find an answer.

To be honest, I have no experience with VBA.  Your program runs
flawlessly from the Visual Basic Menu: Run->Run Sub/UserForm.  Can you
tell me the procedure of how to build this into the Macro Menu.  Been
fiddling with it for a 1/2 hour now, but can't seem to figure it out.

Thanks again.  You're awesome!

Clarification of Answer by hammer-ga on 16 May 2003 14:23 PDT
The macro menu will only run functions, not subroutines. Add this
function to the same module where you put the other routine.

' ****** Code Begin

Public Function RunBuildLinkedList() As Integer

    RunBuildLinkedList = 1

End Function

' ****** Code End

Now, you should be able to run RunBuildLinkedList from the Macro Menu
using the RunCode action.

- Hammer
respree-ga rated this answer:5 out of 5 stars and gave an additional tip of: $25.00
Excellent service, clear communication, and very pleased with the
answer and service received.  Highly recommended.  Thanks so much for
your help.

Subject: Re: MS Access Guru needed: Finding the "next" and "previous" record
From: hammer-ga on 17 May 2003 05:41 PDT

Thank you for the kind words and the generous tip. It's nice to be appreciated!

- Hammer

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy