Excel vba command to retrieve specific information from multiple access table

[william2306]

Hello,
I have a problem with importing data from ms access to excel with <acronym title="visual basic for applications">VBA</acronym>. I use "ADODB" to connect excel with acces.
I have a few tables in ms access. I want to get the ClientDesc and ProductDesc when I input on TrackNo and with a command button to run the VBA. I tried it with inner join (incomplete, without ProductTB), but i only get the first row of information.
so how can i get all the respective/ required info (ClientDesc and ProductDesc).
Thank you for your help.


below is my current code in excel module.

Option Explicit
Public Const DBLink As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Users\USER\Desktop\Mother.accdb;"
Public Sub SearchTRK(TrackNo As String)
  
    Dim CN As ADODB.Connection
    Dim Rs As ADODB.Recordset    
    Set Rs = New ADODB.Recordset   
    
    Set CN = New ADODB.Connection
    CN.Open DBLink
      
     Set Rs = CN.Execute("select MotherTb.Customer, ClientTb.ClientDesc from MotherTb inner join ClientTb on Mother.Customer = ClientTb.ClientID")
    
     If Not Rs.EOF Then
        Cells(2, 2) = Rs("ClientDesc")
    Else
        MsgBox "Record not found"
    End If
    
    Set Rs = Nothing
    
End Sub

current code in excel sheet

Private Sub Search1_Click()
SearchTRK (Cells(1, 2))
End Sub

Example of table in access
MotherTb
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]TrackNo[/TD]
[TD]Customer[/TD]
[TD]Product[/TD]
[/TR]
[TR]
[TD]2017-001[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2017-002[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2017-003[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2017-004[/TD]
[TD]1[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]



ClientTb
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]ClientID[/TD]
[TD]ClientDesc[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]AAA[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]BBB[/TD]
[/TR]
</tbody>[/TABLE]

ProductTb
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ProductID[/TD]
[TD]ProductDesc[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Item A[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Item B[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Item C[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Item D[/TD]
[/TR]
</tbody>[/TABLE]


Example in Excel (End-product)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]TrackNo[/TD]
[TD]2017-003 (input)[/TD]
[/TR]
[TR]
[TD]Client[/TD]
[TD]BBB (output)[/TD]
[/TR]
[TR]
[TD]Product[/TD]
[TD]Item C (output)[/TD]
[/TR]
</tbody>[/TABLE]