A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Sub form combo issue



 
 
Thread Tools Display Modes
  #1  
Old September 18th, 2009, 02:17 PM posted to microsoft.public.access.forms
Lez[_6_]
external usenet poster
 
Posts: 22
Default 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  
Old September 18th, 2009, 06:45 PM posted to microsoft.public.access.forms
Dale Fye
external usenet poster
 
Posts: 2,651
Default 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  
Old October 28th, 2009, 05:16 PM posted to microsoft.public.access.forms
Lez[_6_]
external usenet poster
 
Posts: 22
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:31 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.