First of all my apology experts you provided some solutions on another post but this one appear to be unique, I have a combo box with data coming from SQL server as BE to MS access as FE. with total 300ks
Now when it populate data for users to select it populates everything hence making terribly slower like 45 minutes, one of the blogger mentioned to say I need to filter that combo box with one record at a time , this means that the combo box MUST LOAD EMPTY AND ONLY populate data as the user finish typing the first 3 letters. To that effect I was thinking that the code below will be easy for me but I'm getting stuck. Any help will be highly appreciated once again my apologies.
VBA code to be used
The Actual combo box query that populate data is below and all fields are mandatory populated but captured by VBA on after productID updates:
The VBA above is supposed to incorporate this query on the combo box rowsource = XXXXX, this is where I'm getting stuck.
The query has complex calculation done in the server and all fields must populate the subform , that is why it is quite trick , example one line at a time, so in this case just Fanta as a name all the above fields must show as one line just like that
Now when it populate data for users to select it populates everything hence making terribly slower like 45 minutes, one of the blogger mentioned to say I need to filter that combo box with one record at a time , this means that the combo box MUST LOAD EMPTY AND ONLY populate data as the user finish typing the first 3 letters. To that effect I was thinking that the code below will be easy for me but I'm getting stuck. Any help will be highly appreciated once again my apologies.
VBA code to be used
Code:
Private Sub Field1_Combo_Change()
Dim strText As String
strText = Nz(Me.Field1_Combo.Text, “”)
If Len(strText) > 2 Then
Me.Field1_Combo.RowSource = “Select keywords from ” _
& “My_Words ” _
& “where keywords like ‘” & strText & “*’ ” _
& “order by keywords”
Me.Field1_Combo.Dropdown
End If
End Sub
The Actual combo box query that populate data is below and all fields are mandatory populated but captured by VBA on after productID updates:
Code:
SELECT ViewtblProductSelect.ProductID, ViewtblProductSelect.BarCode, ViewtblProductSelect.ProductName, ViewtblProductSelect.TaxClass, ViewtblProductSelect.Prices, ViewtblProductSelect.RRP, ViewtblProductSelect.VatRate, ViewtblProductSelect.Insurance, ViewtblProductSelect.TourismLevy, ViewtblProductSelect.InsuranceRate, ViewtblProductSelect.TaxInclusive, ViewtblProductSelect.ItemCodes, ViewtblProductSelect.Tourism, ViewtblProductSelect.ExportPrice, ViewtblProductSelect.NoTaxes, ViewtblProductSelect.Sales, ViewtblProductSelect.TurnoverTax, ViewtblProductSelect.Bettings, ViewtblProductSelect.Excise, ViewtblProductSelect.ExciseRate
FROM ViewtblProductSelect
ORDER BY ViewtblProductSelect.ProductID DESC;
The VBA above is supposed to incorporate this query on the combo box rowsource = XXXXX, this is where I'm getting stuck.
The query has complex calculation done in the server and all fields must populate the subform , that is why it is quite trick , example one line at a time, so in this case just Fanta as a name all the above fields must show as one line just like that