Hey, I managed to implement your solution. It does the trick, but there is a slight problem as I will try my best to detail below.
Main Form: Estimate Details
Tab: tabEstimateData
Page: Products Page
Subform: Estimate Products Subform
As you can see, the subform is in the tabbed page.
The subform is a datasheet of all the products for that estimate. Each product has its own row and the way I have designed the form, the search text box is a textbox for each row.
Problem is, that anything typed into the search box for any row is replicated in all rows including the "new record row" (although it doesn't commit anything to the table) and the combo box of each row is filtered accordingly.
So if the selected product in the combo box of the first row doesnt contain part of the string from the text box, then the combo box is just empty for the first row, although it doesnt affect the chosen product for that row.
How can I fix this? Should I have the subform work as a continuous form and configure it so that it looks and behaves like a datasheet but each row is seperate?
Thanks
Code:
Option Compare Database
Private Sub Form_Load()
Me.Product_Selector.ControlTipText = "Click on the down arrow" & vbNewLine & "to view products"
Me.Product_Search_Change
End Sub
Sub Product_Search_Change()
On Error Resume Next
Dim StrSql As String 'SQL statement for the record source
Dim sText As String 'Contents of the criteria control
sText = Trim(Me.Product_Search.Text)
If (sText = "") Then
sText = "*"
End If
StrSql = "SELECT [Products Extended].[Product ID], [Products Extended].Product FROM [Products Extended]"
'Is there any text to test?
If Not sText = "" And IsDelOrBack = False Then
StrSql = StrSql & "WHERE [Product] Like '*" & sText & "*';"
'Refresh the rowsource with the new SQL
Me.Product_Selector.RowSource = StrSql
End If
'Requery the list box to show results
Me.Product_Selector.Requery
End Sub
Private Sub Product_Selector_AfterUpdate()
'Take current sale price and stick it into the estimate items table
End Sub