Referenceing a fleld in a lookup table to pull data from that row

[Thompyt]

I am trying in VBA to reference a table for data to compare to another table. I think the issue is that I am only checking 1 row in the lookup table. How do I get it to do a lookup until found, or until end of file? If no data then [NN] = 0 or null.

I am getting confused on the Do While Loop I guess.

Thanks

Private Sub SINCTest_Click()
Dim Uname As Variant
Dim I As Integer, Z As Integer
Dim NewWave As String, BDEs As String, Base As String, DIV As String, NN As String

Set rU = CurrentDb.OpenRecordset("NBOI") ' Main table
Set rN = CurrentDb.OpenRecordset("New_Net_ID") ' Lookup table

    V = " VHF"
    BDEs = "2BCT1ID"
    DIV = "1ID "
    
If rU.BOF And rU.EOF Then
                rU.Close          
            Else
              rU.MoveLast
              rU.MoveFirst
    Do While Not rU.EOF
            rU.Edit

    If rU![BN (Owning)] <> rU![BN (Operating)] Then
            BN = rU![BN (Operating)]
    ElseIf rU![BN (Owning)] = rU![BN (Operating)] Then
            BN = rU![BN (Owning)]
            Else: BN = ""
    End If
            
          Uname = BN
            
            Select Case BN
                Case Is = "BDE"
                    Base = BDEs
                    Z = 1
                Case Is = "DIV"
                    Base = DIV
                    Z = 0
                Case Else
                    Base = ""
            End Select

For I = 1 To 6

        If rU("SINCGARS  " & I) = rN("NBOI_NET") Then
                NN = rN("NETID")
        End If
   
    If rU("SINCGARS  " & I) = "METT" Then
              rU("SINC" & I) = "12500-METT-T" & V
    ElseIf rU("SINCGARS  " & I) Like "Div*" Then
              rU("SINC" & I) = NN & "-" & DIV & Right(rU("SINCGARS  " & I), 3) & V
    
    ElseIf rU("SINCGARS  " & I) Like "Bde*" Then
        NewWave = Base & Right(rU("SINCGARS  " & I), (Len(rU("SINCGARS  " & I)) - 3))
            rU("SINC" & I) = NN & "-" & NewWave & V
    
   ElseIf IsNull(rU("SINCGARS  " & I)) Or rU("SINCGARS  " & I) = "" Then
              rU("SINC" & I) = ""
         End If
    End If
Next I

            rU.Update
            rU.MoveNext

    Loop

End If

       rU.Close

       Set rU = Nothing
      Set rN = Nothing

End Sub

[ranman256]

there NO need for any of this code. This is what queries are for.
The query will do this for you, and 100 times faster.

[Thompyt]

This is only a portion of the code that is relevant to the question. I do see your point.

Example: Sported unit. This is a no specific and requires looking at a different control in that row to determine what unit is supported, then you need to find the unit name and the specific net from the table that designates the supported unit. The same would go for Medics assigned to one place but are signed out to support a subordinate unit.

Another example: Depending on the unit the plt referenced may have a name, but is assigned a number for the ID:


Crawl = 1
Walk = 2
Run = 3

I would have to make a column for each radio [SINCGARS 1] through [SINCGARS 6]:

A start and it needs to have PLTs added

SINC2a: IIf([SINCGARS 2]="CO CMD",[NetID] & "-" & [CO (Operating)] & " " & [Base] & " VHF",IIf([SINCGARS 2]=[NBOI_Net],[NetID] & "-" & [Netname] & " VHF",[SINC2]))