Do While (or maybe Until) Loop problem :-((

[JasonS]

Hi everybody!
I've got a table in MS Access. It contains 2 columns called NR1 and NR2.
These are the value of them:
NR1 | NR2
1 | 2
2 | 3
3 | 4
4 | 5
9 | 10
10 | 11
11 | 12
12 | 13
13 | 14
What I want to acheive is to have one number from NR1 "1" as value on input,
and then get "5" from NR2 on output of this procedure.
The algorytm is:
1. Find input value in NR1
2. Select value from NR2 (in the same row)
3. Try to find value from NR2 in NR1
4. Repeat these operations until after selecting value from NR2 there will
be no the same value in NR1, display the last found value in NR2...
NR1 is the primary key.
I know that it should be done using Do...Until loop, but have no idea how to
put the initial value from NR1 in it :-((
Any hints will be appreciated

Thank you in advance!

 

[yakule]

Code
-------------------

Public Function GetNumber() As Integer
Dim nrRS As ADODB.Recordset
Dim sSql As String
Dim NoMatch As Boolean

'create new recordset of table
Set nrRS = New ADODB.Recordset
sSql = "SELECT * FROM <TableName>"

With nrRS
'open the recordset as ReadOnly
.Open sSql, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
'Go to first record
.MoveFirst

Do Until NoMatch
'set return value equal to second column
GetNumber = .Fields("NR2").Value
'filter recordset to show only records that match second column in first column
.Filter = "NR1=" & .Fields("NR2").Value
'if the recordset is empty, exit the loop and return the last GetNumber
If .EOF And .BOF Then
NoMatch = True
End If
Loop
'close the recordset
.Close
End With
'clean up
Set nrRS = Nothing
End Function

 

[Michael]

Hello guys!
Try to find a solution some easier way, I figured out sometnig like this:

Dim a As Integer
Dim b As Integer
a = Me.Text0.Value

Do Until IsNull(a) = True
DoCmd.SetWarnings False
If IsNull(DLookup("[NR2]", "[Table1]", "[NR1] = " & a)) = True Then

Exit Do

Else

b = DLookup("[NR2]", "[Table1]", "[NR1] = " & a)
a = DLookup("[NR2]", "[Table1]", "[NR1] = " & b)

End If

Loop

MsgBox "Last value is " & a