So in my database, I've got a junction table that lists all of the "purposes" someone has for a loan. The code worked when just showing the values (in number form), but in my second loop where I search a second table for the actual name of the purpose, I'm running into issues. It displays the correct number of purposes, but unfortunately, the text repeats the first found purpose that number of times rather than displaying all applicable purposes.
Here is the code I'm using:
Private Sub cmdMsgBox_Click()
Dim DB As Database
Dim tblOpp2LP As Recordset
Dim tblLoanPurpose As Recordset
Dim ctlFindRecord As Control
Dim lngHoldOpportunityID As Variant
Dim intRecordCount As Integer
Dim valTestBox As String
Dim valLP As String
Dim valName As String
Set DB = CurrentDb
Set tblInputOpp2LP = DB.OpenRecordset("Opp2LP")
Set tblLoanPurpose = DB.OpenRecordset("LoanPurpose")
Set ctlFindRecord = Me.ctlFindRecord
lngHoldOpportunityID = CLng(ctlFindRecord)
valTestBox = ""
On Error GoTo ErrorHandling_Err:
tblInputOpp2LP.FindFirst "[OpportunityID] = " & lngHoldOpportunityID
If tblInputOpp2LP.NoMatch Then
MsgBox "No Matching Record Found"
Exit Sub
Else
Do Until tblInputOpp2LP.EOF
If lngHoldOpportunityID = tblInputOpp2LP![OpportunityID] Then
valLP = tblInputOpp2LP![LPID]
intCounter = intCounter + 1
Do Until tblLoanPurpose.EOF
If valLP = tblLoanPurpose![LPID] Then
valName = tblLoanPurpose![Name]
End If
tblLoanPurpose.MoveNext
Loop
If valTestBox = "" Then
valTestBox = valName
Else
valTestBox = valTestBox & ", " & valName
End If
End If
tblInputOpp2LP.MoveNext
Loop
txtMsgbox = valTestBox
End If
ErrorHandling_Exit:
Exit Sub
ErrorHandling_Err:
MsgBox Err.Description & " - " & Err.Number
Resume ErrorHandling_Exit
End Sub
Thoughts? Thanks in advance!
I got it!
As it turns out, what I needed to do was add an "exit do" in the second loop.
See the below code example.