Display record in Subform

Question

Hye,

I'm new to access vba, I'd like to display the record in the subform.

First step I need to choose customer name in the check box.

Second, after the customer name has been chosen/clicked, the system will be automatically go through the record. For example, my customer name is A, so the system will go through the customer A order IDs in the Customer Table. Then, the system will take the customer A's order ID, and the order ID then being compared with the order ID in the Product Table. If the order ID from Product Table is same as the order ID of Customer Table. Then all the record (such as product name, expiry date, quantity) of the product under the same order ID will be displayed in the subform.

Here is my code. I got pending at the comment 'Display the details in subform.

Sub CustomerList_Review()

Dim db As DAO.Database
Dim rsCustLog As DAO.Recordset
Dim rsPrdLog As DAO.Recordset
Dim ordID As String

Set db = CurrentDb
Set rsCustLog = db.OpenRecordset("CustomerLog")
Set rsPrdLog = db.OpenRecordset("ProductLog")

Do Until rsCustLog.EOF
    If rsCustLog("CUSTOMER NAME").Value = Forms![Customer Review].cbxCustName Then
        ordID = rsCustLog("CUSTOMER NAME").Value

        Do Until rsPrdLog.EOF
            If rsPrdLog("ORDER ID").Value = ordID Then
                'Display the details in the subform
            End If

        rsPrdLog.MoveNext
    End If

    rsCustLog.MoveNext
Loop
End Sub

So whenever I click different customer name, the product details will change regarding to the customer name order IDs. If you got more simple code. Let me know.

Thanks for helping.

Read the following article and see if you can get your forms set up like that support.office.com/en-us/article/… — Jens

Nur Adhariah Nur Adhariah · Accepted Answer · 2015-01-23T08:28:31

By using LEFT OUTER JOIN or LEFT JOIN statement, I could get the the record I need to be displayed in the subform. Here is my simple code:

Sub CustomerList_Review()

Dim db As DAO.Database
Dim rsCustLog As DAO.Recordset
Dim rsPrdLog As DAO.Recordset
Dim custName As String

Set db = CurrentDb
Set rsCustLog = db.OpenRecordset("ORDER ID")
Set rsPrdLog = db.OpenRecordset("PRODUCT LOG")

custName = Forms![Customer Review]!cbxCustName.Text
Forms![Customer Review]!subform1.Form.RecordSource = _
    "SELECT [ORDER ID].[ORDER ID], [PRODUCT LOG].[SKU], " _
    & "[PRODUCT LOG].[PRODUCT NAME], [PRODUCT LOG].[LOT NO], " _
    & "[PRODUCT LOG].[EXP DATE], [PRODUCT LOG].[QUANTITY] " _
    & "FROM [ORDER ID] LEFT OUTER JOIN [PRODUCT LOG] " _
    & "ON [ORDER ID].[ORDER ID] = [PRODUCT LOG].[ORDER ID] " _
    & "WHERE([ORDER ID].[CUSTOMER NAME] = """ & custName & """);"
End Sub

Display record in Subform

1 Answers