Sub-form/form record selection problem

[Daff]

I have a problem with record selection. I have a form, with a sub-form, which is continuous, and displaying product information (making up an order). In the afterupdate event section of the product name field, I have the following code:

Private Sub product_name_AfterUpdate()
    Dim stDocName As String
    Dim strSQL As String
    Dim f As Form
    Dim splitArray() As String
     
    If (Len(product_name & &quot;&quot;) <> 0) Then
        stDocName = &quot;Product Search Result&quot;
        DoCmd.OpenForm stDocName, acNormal
        Set f = Forms(stDocName)
        splitArray() = split(product_name, &quot; &quot;, 3)
                
        If (UBound(splitArray) = 0) Then
            strSQL = &quot;SELECT * FROM tbl_product WHERE product_name LIKE '*&quot; & splitArray(0) & &quot;*';&quot;
        End If
        If (UBound(splitArray) = 1) Then
            strSQL = &quot;SELECT * FROM tbl_product WHERE product_name LIKE '*&quot; & splitArray(0) & &quot;*' AND product_name LIKE '*&quot; & splitArray(1) & &quot;*';&quot;
        End If
        f.RecordSource = strSQL
        f.Requery
    End If
    
    
End Sub

This opens another form, which displays the results of a search based on the information in the product_name field. This form is continuous forms, and each has a button beside it with the following code:

Forms![Create Invoice].[Create Invoice Sub-Form]!product_id = Me!product_id
'Forms![Create Invoice].[Create Invoice Sub-Form]!LastModified = Me!product_name
DoCmd.Close

This enables a particular product to be selected. The problem is that, when the commented line is used, ALL of the records in the continuous subform are set to the value selected.

I am using this method as the users are familiar with it and have requested it. Does anyone know how to solve this problem?

Is it possible to just copy the product_id, and do a look-up to display the product_name in the subform?

Input appreciated.

 

[JaneC]

If the lastmodifed field contains the product that was last modified on the order you could move it to the order header table and display it on the main form.

HTH

Jane