Below is a recursive query I am using.
I was wondering if it would be better if the inline query portion was called as a stored query instead. Would it save anytime. It isn't a very complicated query.
Function FindNextParentKeyEquip(ParamKeyEquipUp As Long) As String
Dim strSQL As String
Dim Db As DAO.Database
Dim rs As DAO.Recordset
'Recursive function
'The function will continue to call itself until it finds a PWR-PL or PF-PL or it will return Null
strSQL = " SELECT "
strSQL = strSQL & " tblEquip.strFPN "
strSQL = strSQL & " , tblCable.keyEquipUp , tblCable.keyEquipDn "
strSQL = strSQL & " FROM tblEquip LEFT JOIN tblCable ON tblEquip.keyEquip = tblCable.keyEquipDN "
strSQL = strSQL & " WHERE tblEquip.keyEquip= " & ParamKeyEquipUp & " AND tblEquip.keyShip = " & txtHoldOptionValue
Set Db = CurrentDb
Set rs = Db.OpenRecordset(strSQL)
If IsNull(rs!keyEquipUp) Then
FindNextParentKeyEquip = "Null"
Exit Function
End If
If Not rs.EOF Then
'If InStr(rs!strFPN, "PWR-PL-") > 0 Then
If ((InStr(rs!strFPN, "PWR-PL-")) Or (InStr(rs!strFPN, "PF-PL-"))) > 0 Then
FindNextParentKeyEquip = rs!strFPN
Else
FindNextParentKeyEquip = FindNextParentKeyEquip(rs!keyEquipUp)
End If
End If
rs.Close
Set rs = Nothing
Db.Close
Set Db = Nothing
End Function