Small Coding Problems VBA MS ACCESS

[Rosh86]

I am having problems with my code.

This is what happens:

An example:

I'm getting this output:

Chain Origination Destination
P-00100 P-00100 P-00126
P-00130 P-00130 P-00100
P-00166 P-00166 P-00130

when I should be getting:

Chain Origination Destination
P-00100 P-00100 P-00126
P-00100 P-00130 P-00100
P-00100 P-00166 P-00130



BUT, if I swap the Origination and Destination values around for the first record in the Chain like so:

Chain Origination Destination
P-00100 P-00126 P-00100
P-00130 P-00130 P-00100
P-00166 P-00166 P-00130

I get this (which is correct):

Chain Origination Destination
P-00100 P-00126 P-00100
P-00100 P-00130 P-00100
P-00100 P-00166 P-00130

Can anyone please help me out and let me know what needs to be changed in my code. I believe I am very close to the code I need. Many thanks in anyway!!!

(code starts below broken line)
-------------------------------------------------------
Function relinkChains()
Dim db As Database
Dim rs As DAO.Recordset
Dim sChain As String
Dim sFrom As String
Dim sTo As String
Dim repeatLoop As Boolean

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT FromNode, ToNode FROM tblPort_Link " & _
"WHERE FromNode Not In (SELECT Origination FROM tblTempChain WHERE Chain <> 'Non-Outlet') " & _
"AND FromNode Not In (SELECT Destination FROM tblTempChain WHERE Chain <> 'Non-Outlet') " & _
"AND ToNode Not In (SELECT Origination FROM tblTempChain WHERE Chain <> 'Non-Outlet') " & _
"AND ToNode Not In (SELECT Destination FROM tblTempChain WHERE Chain <> 'Non-Outlet') " & _
"ORDER BY FromNode, ToNode")

rs.MoveFirst
repeatLoop = False
Do Until rs.EOF
sChain = rs!FromNode
sFrom = rs!FromNode
sTo = rs!ToNode

DoCmd.RunSQL "INSERT INTO tblTempChain (Chain, Origination, Destination) " & _
"VALUES ('" & sChain & "', '" & rs!FromNode & "', '" & rs!ToNode & "')"

'After the insertion of each record above we take the value in the PhysicalNode
'and compare it to all values in the FromNode and the ToNode fields in the tblPort_Link table.
rs.FindNext "FromNode = '" & sTo & "' OR ToNode = '" & sTo & "'"
If rs.NoMatch = False Then
If rs!FromNode = sTo Then
repeatLoop = True
sFrom = ""
sTo = rs!ToNode
ElseIf rs!ToNode = sTo Then
repeatLoop = True
sFrom = rs!FromNode
sTo = ""
End If
Do While repeatLoop = True
'If a match is found then a new record is inserted in tblTempChain consisting of the LinkName
'from tblOulet_Link and the FromNode and ToNode from tblPortLink
DoCmd.RunSQL "INSERT INTO tblTempChain (Chain, Origination, Destination) " & _
"VALUES ('" & sChain & "', '" & rs!FromNode & "', '" & rs!ToNode & "')"
If sFrom <> "" Then
rs.FindNext "FromNode = '" & sFrom & "'"
If rs.NoMatch = False Then
sFrom = ""
sTo = rs!ToNode
repeatLoop = True
Else
rs.FindNext "ToNode = '" & sFrom & "'"
If rs.NoMatch = False Then
sFrom = rs!FromNode
sTo = ""
repeatLoop = True
Else
sFrom = ""
sTo = ""
repeatLoop = False
End If
End If
ElseIf sTo <> "" Then
rs.FindNext "FromNode = '" & sTo & "'"
If rs.NoMatch = False Then
sFrom = ""
sTo = rs!ToNode
repeatLoop = True
Else
rs.FindNext "ToNode = '" & sTo & "'"
If rs.NoMatch = False Then
sFrom = rs!FromNode
sTo = ""
repeatLoop = True
Else
sFrom = ""
sTo = ""
repeatLoop = False
End If
End If
End If
Loop
End If
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing

End Function

 

[easyit]

Hi,

It is a bit complicated to understand what you are trying to achieve without the table data. My first impression would be that you can solve this with queries rather then code. have you tried that?

Could you provide the table data fromt blPort_Link - which should generate the desired result?


EasyIT

"Do you think that’s air you're breathing?

 

[TheAceMan1]

How are ya Rosh86 . . .

Explain the [blue]logic[/blue] of what your trying to do? . . .

See Ya! . . . . . .