Search as you type, in combo box

[Lesg]

Hi Guys, I have been following this thread https://www.accessforums.net/showthread.php?t=48811 to update a combo box to allow for searching/filtering records as the user types.



I have on an invoice line item form a combo box, the row source behind the combo is:


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 "*" & Me.cboProducts.Text & "*') AND ((tblPrices.CustID)=[Forms]![frmOrderPlacement]![txtCustID]) AND ((tblPrices.available)=False))
ORDER BY products.Item

My issue is that this errors here: & Me.cboProducts.Text & "*' where I am trying to link this to the combo box, so need to add something like: If Len(Me.cboProducts.Text) > 0 Then But do not know how to add this into the query designer of access:


Can anyone help with this please ?

[ranman256]

instead of running N queries as they type,
why not run 1 query when they finish typing?

[Micron]

Me.cboProducts.Text
You cannot use text property in a query. You could use .Value but most don't bother as it is the default property of several control types, one of which is the textbox. Also, you cannot use Me in a query either - only in code behind the form or report. Use the same syntax as you did for tblPrices.CustID

[Lesg]

Me.cboProducts.Text
You cannot use text property in a query. You could use .Value but most don't bother as it is the default property of several control types, one of which is the textbox. Also, you cannot use Me in a query either - only in code behind the form or report. Use the same syntax as you did for tblPrices.CustID

Thanks for that feedback, I have as suggested modified the row source:

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 "*" & [Forms]![frmOrderItems]![cboProducts] & "*") AND ((tblPrices.CustID)=[Forms]![frmOrderPlacement]![txtCustID]) AND ((tblPrices.available)=False))
ORDER BY products.Item;

However, this now opens a parameter box:



if I enter something valid, it then allows me to return to the combo control and shows items that match the term added, and remains for the next items in the order, so assume I need to add this query on the onChange event and not in the row source?

I Have tried onChange event, that did not work

[Lesg]

instead of running N queries as they type,
why not run 1 query when they finish typing?

sorry, I don't understand what you mean, can you clarify?

[Micron]

You're executing that sql in code or it's a query? If query, the form or control name is likely misspelled or the referenced form isn't open. You show 2 form names there - they must both be open, but not necessary to be visible. If vba code, you'll need to declare and set one or more parameters.

[Lesg]

You're executing that sql in code or it's a query? If query, the form or control name is likely misspelled or the referenced form isn't open. You show 2 form names there - they must both be open, but not necessary to be visible. If vba code, you'll need to declare and set one or more parameters.

Hi Micron,

The SQL is via query designer, both forms are open, the combo exists on frmOrderItems I have checked the names and they are correct, no misspelling., but it still opens the parameter box and once a value is used, that then persists for the next line entry. So I need to stop the parameter box opening, and also need to clear for the next line item!

[Welshgasman]

Sorry, Access does not prompt for a value if it knows what it is?
Common error is mispelling, then syntax. So if that combo is on a subform, the syntax is all wrong?

[CJ_London]

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

[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

Thanks for that feedback and suggestions, when I attempt this I cam getting the error message:


I've tried various alterations, but no look, can't spot the closing error?

I will try your edit suggestions in edits 1 & 2 once I have resolved this.

Thank you

[CJ_London]

can't spot the closing error?

my bad I left a closing bracket in after tblPrices.CustID

[Micron]

@WGM, good point about the subform.

[Lesg]

my bad I left a closing bracket in after tblPrices.CustID

Hi Ajax, thanks for that, I did try removing the closing, and have tried it after various positions but none are working ... sorry if I appear dumb, just cant close the statement, can you advise please?

[Lesg]

my bad I left a closing bracket in after <font color="#333333"><em>tblPrices.CustID<br>
<br>
</em></font>

<br>
<br>Hi Ajax,&nbsp; thanks for that, I did try removing the closing, and have tried it after various positions but none are working ... sorry if I appear dumb, just cant close the statement, can you advise please?&nbsp;

[CJ_London]

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