By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,945 Members | 1,432 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,945 IT Pros & Developers. It's quick & easy.

Combobox Stop Autofilling Text portion from selected item

P: 64
Hi all,

I have a combobox on my data entry form. When a user types, the list section of the form auto-refreshes to display items containing the text. However, when a user then selects an item using the arrow keys, it automatically updates the text region. This isn't good for my purpose as it doesn't allow users to select items in the list box section beyond the top option. Is there a way to prevent this from happening?

I will post my code below for the auto-refreshing of the combobox to see if the problem lies in there but I don't think it's related to that.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdLocationID_Change()
  2.  
  3. Me.cmdLocationID.RowSourceType = "Value List"
  4.  
  5. Dim db As Database
  6. Dim qdf As QueryDef
  7. Dim rs As DAO.Recordset
  8. Dim intX As Integer
  9.  
  10. Set db = CurrentDb()
  11. Set qdf = db.QueryDefs("qrySearchType")
  12. qdf.Parameters("SearchTXT") = Me.cmdLocationID.Text
  13. Set rs = qdf.OpenRecordset()
  14.  
  15. 'remove all items in the combo box before querying to add them
  16. With rs
  17.     With Me.cmdLocationID
  18.         For intX = .ListCount - 1 To 0 Step -1
  19.             Call .RemoveItem(intX)
  20.         Next intX
  21.     End With
  22. End With
  23.  
  24. 'any results = add to list box
  25. If rs.RecordCount <> 0 Then
  26.     With rs
  27.         .MoveFirst
  28.         While Not .EOF
  29.             Me.cmdLocationID.AddItem .Fields("LocationID") & ";" & .Fields("FileLocation")
  30.             .MoveNext
  31.         Wend
  32.     End With
  33. End If
  34.  
  35. rs.Close
  36. db.Close
  37. Set rs = Nothing
  38. Set db = Nothing
  39.  
  40. If Nz(Me.cmdLocationID.Text, "") <> "" Then
  41.     Me.cmdLocationID.Dropdown
  42. End If
  43.  
  44. End Sub
  45.  
Feb 3 '15 #1

✓ answered by jforbes

This looked like a fun problem, so I mocked up a Form with just a ComboBox and tried to get it to work. I did, but it is a little quirky as Access really, really wants to autocomplete this for the user. I also tapped into the KeyDown Event on the ComboBox to ignore an Up or Down keypress. Mouse Clicks were throwing it off, so I added that to the ignore list also.

Hopefully, the code will get you closer to what you want:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private bLookupKeyPress As Boolean
  5.  
  6. Private Sub cboLookup_Change()
  7.     Dim sSQL As String
  8.     Dim sNewLookup As String
  9.  
  10.     If Not bLookupKeyPress Then
  11.         sNewLookup = Nz(Me.cboLookup.Text, "")
  12.         sSQL = "SELECT Field2 FROM [tblBunchOfStuff] "
  13.         If Len(sNewLookup) <> 0 Then
  14.            sSQL = sSQL & " WHERE Field2 LIKE '*" & sNewLookup & "*'"
  15.         End If
  16.         Me.cboLookup.RowSource = sSQL
  17.         Me.cboLookup.Dropdown
  18.     End If
  19.     bLookupKeyPress = False
  20. End Sub
  21.  
  22. Private Sub cboLookup_KeyDown(KeyCode As Integer, Shift As Integer)
  23.     Select Case KeyCode
  24.         Case vbKeyDown, vbKeyUp
  25.             bLookupKeyPress = True
  26.         Case Else
  27.             bLookupKeyPress = False
  28.     End Select
  29. End Sub
  30.  
  31. Private Sub cboLookup_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
  32.     bLookupKeyPress = True
  33. End Sub
  34.  

Share this Question
Share on Google+
27 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,487
David,

please explain this a little bit better:
When a user types, the list section of the form auto-refreshes to display items containing the text. However, when a user then selects an item using the arrow keys, it automatically updates the text region. This isn't good for my purpose as it doesn't allow users to select items in the list box section beyond the top option.
I'm not sure I understand what is happening. What is meant by
it automatically updates the text region
?

Additionally, is "cmdLocationID" your Combo Box? It is named as a Command Button, which is very confusing to me (as well as anyone else who might have to troubleshoot/modify your database in the future). You also refer to it in your code as a "List Box". It might better be named "cboLocationID".

However, I am even more thoroughly confused by your code itself. Apparently, after a user changes the value of the combo box, you then reset the list of items in the combo box based on the selection of the combo box???????

Regardless, lines 15-33 of your code are completely unnecessary, and in fact your code might be cut down significantly. I would recommend changing your code to reflect building a query and then simply assigning that query as the Row Source for your Combo Box:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdLocationID_Change()
  2.     Dim strSQL As String
  3.     strSQL = "SELECT * FROM qrySearchType " & _
  4.         "WHERE SearchTXT ='" = Me.cmdLocationID.Text & "';"
  5.     Me.cmdLocationID.RowSource = strSQL
  6.     Me.cmdLocationID.Requery
  7. End Sub
Hope this helps.
Feb 3 '15 #2

P: 64
The combo box has a list of locations, so when the user types, the list of options should only contain the locations which contain the string that the user has typed.

When the user pressed the down arrow, it selects the first option in the list portion, then updates the text in the textbox region as the same as the listbox portion and then updates the listbox... essentially not working as expected.

e.g. currently happening

Typing New in text portion -> filters results to New Amsterdam, New England, New York -> user presses down arrow wanting to select New England -> the combobox immediately updates the text portion to New Amsterdam as this is the selected option -> list box updates to contain just New Amsterdam

what i need to happen:

Typing New in text portion -> filters results to New Amsterdam, New England, New York -> user presses down arrow wanting to select New England -> textbox stays just saying New so the listbox part isn't requeried -> user highlights New England and presses enter -> job done!

Why I called it cmd rather than cbo... that is anyone's guess!

I have tried to implement your new code, the row source is just blank and has no options. I guess I have probably done something wrong!

The SQL for the query is:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblLocation.LocationID, tblLocation.FileLocation
  2. FROM tblLocation
  3. WHERE (((InStr(1,[tblLocation].[FileLocation],[SearchTXT]))>0))
  4. ORDER BY tblLocation.FileLocation;
  5.  
Feb 3 '15 #3

twinnyfo
Expert Mod 2.5K+
P: 3,487
First, I would change the Combo Box Row Source Type to Table/Query and make sure (I think that was causing the error--and I'm not sure the .Requery is required in this case).

Then, in this case, I would recommend using a separate text box for a search field. Then, in the Text Box's AfterUpdate event:

Expand|Select|Wrap|Line Numbers
  1. Private Sub txtSearch_AfterUpdate()
  2.     Dim strSQL As String
  3.     strSQL = "SELECT LocationID, FileLocation FROM qrySearchType " & _
  4.         "WHERE SearchTXT ='" = Me.txtSearch & "' "
  5.         "ORDER BY FileLocation;"
  6.     Me.cmdLocationID.RowSource = strSQL    
  7. End Sub
Then you can use the values of cmdLocationID once the user selects the value (again, I would recommend the AfterUpdate Event of that control).

Hope this gets you closer....
Feb 3 '15 #4

P: 64
I'm sorry Twinnyfo but now I'm completely lost. Nothing works now XD It comes up saying that the RecordSource false specified on this form or report does not exist.

So I have to have an unbound text box which the user types into. This should then filter the combo box to just have the items which contain the string that is entered into the text box, correct?
Feb 3 '15 #5

twinnyfo
Expert Mod 2.5K+
P: 3,487
Yes, txtSearch should be an unbound text box.

Also, if you could post the SSSQL for qrySearchType, that would help us troubleshoot the error on the Query side of things....
Feb 3 '15 #6

P: 64
Query SQL
Expand|Select|Wrap|Line Numbers
  1. SELECT tblLocation.LocationID, tblLocation.FileLocation
  2. FROM tblLocation
  3. WHERE (((InStr(1,[tblLocation].[FileLocation],[SearchTXT]))>0))
  4. ORDER BY tblLocation.FileLocation;
  5.  
SearchTXT Function
Expand|Select|Wrap|Line Numbers
  1. Function SearchTXT() As String
  2.     SearchTXT = "Like *" & glSearchTXT & "*"
  3. End Function
  4.  
And the textbox after update sets glSearchTXT=Me.txtSearch
Expand|Select|Wrap|Line Numbers
  1. glSearchTXT = txtSearch
  2.  
  3. Dim strSQL As String
  4.     strSQL = "SELECT LocationID, FileLocation FROM qrySearchType " & _
  5.         "WHERE SearchTXT ='" = Me.txtSearch & "' " & _
  6.         "ORDER BY FileLocation;"
  7.     Me.cmdLocationID.RowSource = strSQL
  8.  
Feb 3 '15 #7

twinnyfo
Expert Mod 2.5K+
P: 3,487
So, let's streamline the whole thing:

Using the basics of Post #4 above, plus the information you just provided, we can do this all using the After Update Event of your text box (with no necessary global variables):


Expand|Select|Wrap|Line Numbers
  1. Private Sub txtSearch_AfterUpdate()
  2.     Dim strSQL As String
  3.     strSQL = "SELECT LocationID, FileLocation " & _
  4.         "FROM tblLocation " & _
  5.         "WHERE FileLocation Like '" = Me.txtSearch & "*' "
  6.         "ORDER BY FileLocation;"
  7.     Me.cmdLocationID.RowSource = strSQL    
  8. End Sub
Do you see how we are simply creating the entire SQL string programmatically? This can be very useful when you have many different controls affecting the parameters of your queries.
Feb 3 '15 #8

P: 64
I understand that, but it's still not working. It's still saying the same error:

"The record source 'False' specified on this form or report does not exist.

The name of the record source may be misspelled, the record source was deleted or renamed, or the record source exists in a different database.

In the Form or Report's Design View or Layout View, display the property sheet by clicking the Properties button, and then set the RecordSource property to an existing table or query"
Feb 3 '15 #9

twinnyfo
Expert Mod 2.5K+
P: 3,487
In the code above in Post #8,

insert the following between lines 6 & 7:

Expand|Select|Wrap|Line Numbers
  1. Debug.Print strSQL
and post what is shown in the immediate window. If the Immediate window is not displayed, hit Ctrl-G.
Feb 3 '15 #10

P: 64
The debug print just says: False
Feb 3 '15 #11

twinnyfo
Expert Mod 2.5K+
P: 3,487
Could you cut and paste the code you have in the AfterUpdate Event? Something is obviously not right. It should be a String, not a Boolean value.
Feb 3 '15 #12

P: 64
Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub txtSearch_AfterUpdate()
  3.  
  4. Dim strSQL As String
  5.     strSQL = "SELECT LocationID, FileLocation " & _
  6.         "FROM tblLocation " & _
  7.         "WHERE FileLocation Like '" = Me.txtSearch & "*' " & _
  8.         "ORDER BY FileLocation;"
  9.     Debug.Print strSQL
  10.     Me.cmdLocationID.RowSource = strSQL
  11. End Sub
  12.  
Feb 3 '15 #13

twinnyfo
Expert Mod 2.5K+
P: 3,487
And the error is occurring at line 10?

Are there any records that "should" match the search criteria?

This is really strange.....
Feb 3 '15 #14

jforbes
Expert 100+
P: 1,107
This part is getting evaluated instead of concatenated:
Expand|Select|Wrap|Line Numbers
  1. "WHERE FileLocation Like '" = Me.txtSearch & "*' " & 
The Equal sign should be an Ampersand:
Expand|Select|Wrap|Line Numbers
  1. "WHERE FileLocation Like '" & Me.txtSearch & "*' " & 
Feb 3 '15 #15

twinnyfo
Expert Mod 2.5K+
P: 3,487
@jforbes,

Thanks for the catch! That's what I get for freehanding code! I knew it had to be something simple.

@DavidAustin,

It should work now.........
Feb 3 '15 #16

twinnyfo
Expert Mod 2.5K+
P: 3,487
And I had that wrong all the way back in Post #2!!!
Feb 3 '15 #17

P: 64
Haha, it's always the simple things.

This updates the combobox as I had hoped, but I'm guessing there is no way to do it while the user is typing in the textbox so you can see the combobox being filtered while you type? This is why I tried it in the change event to begin with, but I'm guessing the combobox has no way of being dropped down when it doesn't have focus?
Feb 4 '15 #18

twinnyfo
Expert Mod 2.5K+
P: 3,487
Ahhhhhhhhhhhh! Now I finally understand what you are trying to accomplish! However, when I try to model this here, whenever I begin typing any value, because Combo Boxes select the first available values from their list as you are typing, my search always results in just one record (the first one that matches the letters typed).

Using your example, if my list has New Amsterdam, New England, New York, once I type in "N", "New Amsterdam" pops up in my combo box (because it is the first value that matches the "N". At this point, the combo box has "changed" and now holds the value for the LocationID associated with "New Amsterdam".

Now, in the OnChange Event, I am now creating a SELECT Statement that results in:

Expand|Select|Wrap|Line Numbers
  1. SELECT LocationID, FileLocation
  2. FROM tblLocation
  3. WHERE FileLocation Like 'New Amsterdam*'
  4. ORDER BY FileLocation;
And, one can never continue typing to even get to "New England" or "New York", because those values are no longer in the list.

Plus, when you use the txtSearch and try to either set the focus on the combo box or force a dropdown, Access won't allow it.

So, I guess one of my questions would be why this methodology for manipulating a Combo Box when the inherent, built-in functionality of the combo box is to move to the correct item in the list, based on your typing?

Yes, in your example, one would have to type "New Y" to get to "New York", which is only five key strokes, or one could type "N" and use the dropdown (or Alt-DownArrow) and then select "New York", which would be third on the list. Unless you have thousands and thousands of potential list items, many of which all have similar beginnings, I see no "real" advantage.

To illustrate, open any dictionary, which may contain 100,000 different words. How many of them are so similar in spelling that one must type seven or eight letters before the list is whittled down to less than a handful?

Although trying to manipulate your combo box may be possible, we must weigh the usefulness of the method. The txtSearch method will "work" but certainly not in the way you desire. But it is an option at this point.

This was also a good exercise for me (not only in proofreading), as I have learned a few things that may just not be possible to do in Access.

I hope we have at least explored some possibilities together--although your problem is still, technically speaking, unsolved.
Feb 4 '15 #19

jforbes
Expert 100+
P: 1,107
This looked like a fun problem, so I mocked up a Form with just a ComboBox and tried to get it to work. I did, but it is a little quirky as Access really, really wants to autocomplete this for the user. I also tapped into the KeyDown Event on the ComboBox to ignore an Up or Down keypress. Mouse Clicks were throwing it off, so I added that to the ignore list also.

Hopefully, the code will get you closer to what you want:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private bLookupKeyPress As Boolean
  5.  
  6. Private Sub cboLookup_Change()
  7.     Dim sSQL As String
  8.     Dim sNewLookup As String
  9.  
  10.     If Not bLookupKeyPress Then
  11.         sNewLookup = Nz(Me.cboLookup.Text, "")
  12.         sSQL = "SELECT Field2 FROM [tblBunchOfStuff] "
  13.         If Len(sNewLookup) <> 0 Then
  14.            sSQL = sSQL & " WHERE Field2 LIKE '*" & sNewLookup & "*'"
  15.         End If
  16.         Me.cboLookup.RowSource = sSQL
  17.         Me.cboLookup.Dropdown
  18.     End If
  19.     bLookupKeyPress = False
  20. End Sub
  21.  
  22. Private Sub cboLookup_KeyDown(KeyCode As Integer, Shift As Integer)
  23.     Select Case KeyCode
  24.         Case vbKeyDown, vbKeyUp
  25.             bLookupKeyPress = True
  26.         Case Else
  27.             bLookupKeyPress = False
  28.     End Select
  29. End Sub
  30.  
  31. Private Sub cboLookup_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
  32.     bLookupKeyPress = True
  33. End Sub
  34.  
Feb 4 '15 #20

twinnyfo
Expert Mod 2.5K+
P: 3,487
@jforbes,

How did you get the sub to look only at what was typed so far, rather than the first value that pops up? That is where I was running into snags.....
Feb 4 '15 #21

jforbes
Expert 100+
P: 1,107
By using the ComboBox.Text property instead of the ComboBox.Value.
Feb 4 '15 #22

twinnyfo
Expert Mod 2.5K+
P: 3,487
I thought I had tried that without success (note the head scratching....). Nice work!
Feb 4 '15 #23

jforbes
Expert 100+
P: 1,107
Ahh, also make sure the AutoExpand Property of the Combobox is set to No. It fixed the quirky behavior that I was experiencing with the above code.

How to configure autofill option for ComboBox
Feb 4 '15 #24

twinnyfo
Expert Mod 2.5K+
P: 3,487
I am still getting the same result. By thte time the code moves to the OnChange Event, the Combo box has already populated with the first list item.

What am I missing here?

Still scratching head..... :-)
Feb 4 '15 #25

twinnyfo
Expert Mod 2.5K+
P: 3,487
Bingo! AutoExpand was the key.

Now I know how to do this!

Thanks much!
Feb 4 '15 #26

P: 64
Thank you so much guys! This is perfect :D
I wasn't sure it was going to be possible for a while but once again you've come up trumps!
Feb 4 '15 #27

jforbes
Expert 100+
P: 1,107
I really like the way that works. I'll probably end up using it in choice places. Thanks for the Question and Hepp.
Feb 4 '15 #28

Post your reply

Sign in to post your reply or Sign up for a free account.