I'm afraid the explanation seems as much of a mess as the code.
This code appears to be half way through an experimental stage. That's not very well appreciated. It puts the extra work of trying to work out what's going on down to the experts and that's not, nor should ever be, our responsibility.
You have various variables defined. Some are variant and some integer, yet you claim that the function should be returning a double.
- Public Function RootTransaction(FormulaItemNumber As Double, _
-
FocusNumber As Double) As Integer
-
Dim dblX As Double, dblY As Double
-
Dim blnLoop As Boolean
-
-
dblX = FormulaItemNumber
-
blnLoop = True
-
Do While blnLoop
-
dblY = DLookup("[Previousid]", _
-
"[FormulaItemsDescriptionView]", _
-
"[FormulaItemNumber]=" & dblX)
-
If dblY = 0 Then
-
dblX = 0
-
blnLoop = False
-
ElseIf (dblY = FocusNumber) Then
-
dblX = 1
-
blnLoop = False
-
Else
-
dblX = dblY
-
End If
-
Loop
-
-
RootTransaction = dblX
-
End Function
Try this out and see what happens.
If it fails then we need a clear and precise explanation of what goes wrong. Quote line numbers where necessary.
In future, please put your question together with a little more care
before posting.