If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sub form combo issue
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 |
#2
|
|||
|
|||
Sub form combo issue
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 & "*';" ---- HTH Dale "Lez" wrote: 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 |
#3
|
|||
|
|||
Sub form combo issue
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 "Dale Fye" wrote in message ... 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 & "*';" ---- HTH Dale "Lez" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|