Sub form combo issue

[Lez]

Hi Guys,

I have a combo box on a subform that allows the user to search for items in
a combo based on the characters they enter. The issue is that it my subform
requires a FK as soon as you start to enter text into the combo box.

I have commented out the strProd="" to try to avoid entering a record at
this point but it still requires a FK and fails. if anyone can suggest a way
in which I can enter a value without causing a record to be written would be
helpful

Thank you

This is the combo action:

Option Compare Database
Dim strProd As String

' on clicking on the combo box, reset the variable strProd to "", and make
the combo box display blank: select the event handlers from the Event tab in
the properties window.

Private Sub cboProduct_Enter()
'strProd = ""
cboProduct = strProd
End Sub

' on each key press compile a string of the keys pressed and adjust the row
source query by adding a WHERE clause to search for the characters entered

Private Sub cboProduct_KeyPress(KeyAscii As Integer)
strProd = strProd & Chr(KeyAscii)
cboProduct.RowSource = "SELECT SKU_ManFRef, SKU_ProdName, SKU_ID FROM
dbo_vProductSKU_grouped WHERE SKU_ProdName Like '*" & strProd & "*';"
End Sub

 

[Dale Fye]

Lez,

You don't need to do this in the cboProduct_KeyPress event, a combo box will
automatically keep track of each key pressed, and will move to the focus of
the combo box to the first item which matches that key combination. BTW,
your code in the KeyPress event will add the backspace key to strProd if you
press it, or any other key, for that matter.

And I don't have a clue what you are trying to do with the cboProduct_Enter
event.

If what you are really trying to do is filter the combo box based on what
has been typed, I'd put a textbox right above (or maybe even overtop) of the
combo box, and use the textboxes Change event to modify the Rowsource of the
combo box.

Private Sub txt_FilterCombo_Change

me.cboProduct.RowSource = "SELECT SKU_ManFREF, KSU_ProdName, SKU_ID " _
& "FROM dbo_vProductSKU_grouped " _
& "WHERE SKU_ProdName " _
& "Like '*" &
me.txt_FilterCombo.text & "*';"

 

[Lez]

Hi Dale,

Sorry for the long delay getting back to you on this, I had some other stuff
to complete and come back to this. Thanks for the suggestion, it work fine,
with just the exception that. for each row it shows the value of the
previous/current search, is there a way I can clear the search field or just
have it once on the form to allow me to search for any row?

This is used within the line items of an invoice form, so for each line, it
is possible the user would want to search for a specific item.

Regards
Lez