Greetings.  I need help writing a Query or Visual Basic code in MS
Access.

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 MrExcel.com bulletin board, but could
not get a decent solution to my problem.  Read more here.
 http://www.mrexcel.com/board2/viewtopic.php?t=49897&highlight=

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

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
        rstSKU.MoveFirst
        ' Set first and last value for
        ' looparound
        strFirstSKU = "p-" & rstSKU!SKU
        rstSKU.MoveLast
        strLastSKU = "p-" & rstSKU!SKU
        rstSKU.MoveFirst
        ' Clone the recordset so we can
        ' keep two bookmarks
        Set rstSKUClone = rstSKU.Clone
        rstSKUClone.MoveNext
        ' 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
                rstSKUClone.MoveNext
            Else
                strNext = strFirstSKU
            End If
            strSQL = "INSERT INTO tblLinkedList (SKU, PreviousSKU,
NextSKU) VALUES ('" & strSKU & "', '" & strPrev & "', '" & strNext &
"');"
            cnn.Execute (strSQL)
            strPrev = "p-" & rstSKU!SKU
            rstSKU.MoveNext
        Loop
    End If
    
ExitMe:
' Clean up memory
strSQL = ""
strSKU = ""
strPrev = ""
strNext = ""
strFirstSKU = ""
strLastSKU = ""
If rstSKUClone.State = adStateOpen Then
    rstSKUClone.Close
End If
Set rstSKUClone = Nothing
If rstSKU.State = adStateOpen Then
    rstSKU.Close
End If
Set rstSKU = Nothing
Set cnn = Nothing
Exit Sub
    
ErrHandler:
    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
years.

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

    BuildLinkedList
    RunBuildLinkedList = 1

End Function

' ****** Code End

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

- Hammer

Excellent service, clear communication, and very pleased with the
answer and service received.  Highly recommended.  Thanks so much for
your help.

Respree,

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

- Hammer