Search as you type, in combo box

[Lesg]

provide the actual code you are using together with any error messages as I cannot see anything wrong with the code I provided once the extra ) is taken out - the previous error message you provided was quite clear as to what and where the problem was

This is the code currently with the extra closing bracket removed:

cboProducts.RowSource = "SELECT DISTINCTROW tblPrices.productID, products.Item, tblPrices.price, tblinventory.Sumofqty, tblPrices.CustID, tblPrices.available" & _
" FROM ((products INNER JOIN tblPrices ON products.productID = tblPrices.productID) INNER JOIN tblUnits ON products.unitsID = tblUnits.unitsID) LEFT JOIN tblinventory ON tblPrices.productID = tblinventory.productID" & _
" WHERE products.Item Like '*' & [cboProducts].text & '*' AND tblPrices.CustID=" & Me.[txtcustID] & " AND tblPrices.available=False" & _
" ORDER BY products.Item"

And this is the error I get:

[CJ_London]

that is saying that you have a null value for txtCustID - look at the error message - you have

....tblPrices.CustID=" & me.[txtCustID] & " AND....

and in the error message

...tblPrices.CustID= AND....

[Lesg]

that is saying that you have a null value for txtCustID - look at the error message - you have

....tblPrices.CustID=" & me.[txtCustID] & " AND....

and in the error message

...tblPrices.CustID= AND....

Ah ok, let me look into that, will update

[Lesg]

perhaps try in the cboProducts change event

cboProducts.rowsource="SELECT DISTINCTROW tblPrices.productID, products.Item, tblPrices.price, tblinventory.Sumofqty, tblPrices.CustID, tblPrices.available" & _
                      " FROM ((products INNER JOIN tblPrices ON products.productID = tblPrices.productID) INNER JOIN tblUnits ON products.unitsID = tblUnits.unitsID) LEFT JOIN tblinventory ON tblPrices.productID = tblinventory.productID" & _
                      " WHERE products.Item Like '*' & [cboProducts].text & '*' AND tblPrices.CustID)=" & me.[txtCustID] & " AND tblPrices.available=False" & _
                      " ORDER BY products.Item"

Edit: you may need to cater for when a user types something then backspaces to clear the entry so there is a zls or null value, note also the change for "*" to '*'
Edit2: perhaps make it conditional on users typing more than three characters or trigger a timer event on each keypress and if no further timing after say 1/2 second (timer interval=500) the timer event populates the combo rowsource

Hi Ajax, sorry for delay in coming back, had a few other things to resolve. So I have corrected that issue now, the error was the format for the txtCustID I was adding, but have fixed this now.

The issue now I have is that when I type a name into the combo, I have a few issue, nothing is populated in the drop down as I type (maybe not an issue is the user clicks enter it does show values in the dropdown), however, on clicking enter, I get a message box box stating that the value entered is not in the list:

I am thinking I could add something to not show the message box, currently I have now value in the notinlist event:

But then for the next line item, the combo does not filter the values by the entered text, so I think that is what you are referring to in the timer event, to repeater the method for the first row?

I can see the timer function, but not sure what I would add?

many thanks for the advise.

[CJ_London]

according to the code you have posted you have not used the change event, you have used the dirty event. Fix that first

[Lesg]

I have changed that now to onChange, I had been testing with other settings trying to resolve

[CJ_London]

your message appears to be a custom one - so you need to find out what triggers it - it may be settings - field is required, or combo limit to list immediately come to mind, or perhaps some code in the control or form before update event or another control

[Lesg]

For the combo box, the events are just the AfterUpdate and OnChange

Private Sub cboProducts_AfterUpdate()Dim strFilter As String




DoCmd.OpenQuery "TempCustomerPriceCheck2"


    strFilter = "ProductID=" & Me!cboProducts
            Me!price = DLookup("price", "Temppricecheck", strFilter)
            Me!units = DLookup("unitsID", "temppricecheck", strFilter)
    Me.CustID = Forms!frmOrderPlacement!txtcustID


   Me.txtqty.SetFocus
        
End Sub




Private Sub cboProducts_Change()
cboProducts.RowSource = "SELECT DISTINCTROW tblPrices.productID, products.Item, tblPrices.price, tblinventory.Sumofqty, tblPrices.CustID, tblPrices.available" & _
                      " FROM ((products INNER JOIN tblPrices ON products.productID = tblPrices.productID) INNER JOIN tblUnits ON products.unitsID = tblUnits.unitsID) LEFT JOIN tblinventory ON tblPrices.productID = tblinventory.productID" & _
                      " WHERE products.Item Like '*' & [cboProducts].text & '*' AND tblPrices.CustID=Forms!frmOrderPlacement!txtcustID AND tblPrices.available=False" & _
                      " ORDER BY products.Item"
End Sub

This allows standard products to use 'custom' pricing for the current customer.

I have tried in the

Private Sub cboProducts_NotInList(NewData As String, Response As Integer)DoCmd.SetWarnings False
End Sub

but this does not work and the alert still pops up, the filtering works fine for the next product, I just need to stop this message box appearing now?

[CJ_London]

and your settings?

[Lesg]

can you clarify where you mean in terms of settings please?

[CJ_London]

where you mean in terms of settings please?

the combo properties

[Lesg]

thanks for that feedback. very strange thing going on, as I have a version of the frontend that that the combo works perfectly as required, not not in list message, and each row allows for a new search entry.

But I have another backup version, that was just a copy of that form, and it shows the pop up not in list message. I am going to check and compare both, but looks like its working now. so thanks so much for your help on this

[Lesg]

Hey Ajax,

Sorry to open this again, but today, for some reason, and I have no idea why, I am getting the pop alert message again now. I have checked those settings as suggested and all set as recommended, but today this popup has started again!

Can you suggest something for the 'is not in list' event handler that would stop it?

[CJ_London]

sorry no. Suggest you upload a copy of your db - remove tables/forms etc not relevant to your question, compact then zip before uploading. Populate with dummy data if you need to

[Lesg]

so have added the following and seems to work, just need to test now:

Private Sub cboProducts_NotInList(NewData As String, Response As Integer)
Response = acDataErrContinue
Me.cboProducts.Dropdown
End Sub