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. -
Private Sub cmdLocationID_Change()
-
-
Me.cmdLocationID.RowSourceType = "Value List"
-
-
Dim db As Database
-
Dim qdf As QueryDef
-
Dim rs As DAO.Recordset
-
Dim intX As Integer
-
-
Set db = CurrentDb()
-
Set qdf = db.QueryDefs("qrySearchType")
-
qdf.Parameters("SearchTXT") = Me.cmdLocationID.Text
-
Set rs = qdf.OpenRecordset()
-
-
'remove all items in the combo box before querying to add them
-
With rs
-
With Me.cmdLocationID
-
For intX = .ListCount - 1 To 0 Step -1
-
Call .RemoveItem(intX)
-
Next intX
-
End With
-
End With
-
-
'any results = add to list box
-
If rs.RecordCount <> 0 Then
-
With rs
-
.MoveFirst
-
While Not .EOF
-
Me.cmdLocationID.AddItem .Fields("LocationID") & ";" & .Fields("FileLocation")
-
.MoveNext
-
Wend
-
End With
-
End If
-
-
rs.Close
-
db.Close
-
Set rs = Nothing
-
Set db = Nothing
-
-
If Nz(Me.cmdLocationID.Text, "") <> "" Then
-
Me.cmdLocationID.Dropdown
-
End If
-
-
End Sub
-
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: - Option Compare Database
-
Option Explicit
-
-
Private bLookupKeyPress As Boolean
-
-
Private Sub cboLookup_Change()
-
Dim sSQL As String
-
Dim sNewLookup As String
-
-
If Not bLookupKeyPress Then
-
sNewLookup = Nz(Me.cboLookup.Text, "")
-
sSQL = "SELECT Field2 FROM [tblBunchOfStuff] "
-
If Len(sNewLookup) <> 0 Then
-
sSQL = sSQL & " WHERE Field2 LIKE '*" & sNewLookup & "*'"
-
End If
-
Me.cboLookup.RowSource = sSQL
-
Me.cboLookup.Dropdown
-
End If
-
bLookupKeyPress = False
-
End Sub
-
-
Private Sub cboLookup_KeyDown(KeyCode As Integer, Shift As Integer)
-
Select Case KeyCode
-
Case vbKeyDown, vbKeyUp
-
bLookupKeyPress = True
-
Case Else
-
bLookupKeyPress = False
-
End Select
-
End Sub
-
-
Private Sub cboLookup_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
-
bLookupKeyPress = True
-
End Sub
-
27 4328
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: - Private Sub cmdLocationID_Change()
-
Dim strSQL As String
-
strSQL = "SELECT * FROM qrySearchType " & _
-
"WHERE SearchTXT ='" = Me.cmdLocationID.Text & "';"
-
Me.cmdLocationID.RowSource = strSQL
-
Me.cmdLocationID.Requery
-
End Sub
Hope this helps.
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: -
SELECT tblLocation.LocationID, tblLocation.FileLocation
-
FROM tblLocation
-
WHERE (((InStr(1,[tblLocation].[FileLocation],[SearchTXT]))>0))
-
ORDER BY tblLocation.FileLocation;
-
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: - Private Sub txtSearch_AfterUpdate()
-
Dim strSQL As String
-
strSQL = "SELECT LocationID, FileLocation FROM qrySearchType " & _
-
"WHERE SearchTXT ='" = Me.txtSearch & "' "
-
"ORDER BY FileLocation;"
-
Me.cmdLocationID.RowSource = strSQL
-
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....
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?
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....
Query SQL - SELECT tblLocation.LocationID, tblLocation.FileLocation
-
FROM tblLocation
-
WHERE (((InStr(1,[tblLocation].[FileLocation],[SearchTXT]))>0))
-
ORDER BY tblLocation.FileLocation;
-
SearchTXT Function -
Function SearchTXT() As String
-
SearchTXT = "Like *" & glSearchTXT & "*"
-
End Function
-
And the textbox after update sets glSearchTXT=Me.txtSearch -
glSearchTXT = txtSearch
-
-
Dim strSQL As String
-
strSQL = "SELECT LocationID, FileLocation FROM qrySearchType " & _
-
"WHERE SearchTXT ='" = Me.txtSearch & "' " & _
-
"ORDER BY FileLocation;"
-
Me.cmdLocationID.RowSource = strSQL
-
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): - Private Sub txtSearch_AfterUpdate()
-
Dim strSQL As String
-
strSQL = "SELECT LocationID, FileLocation " & _
-
"FROM tblLocation " & _
-
"WHERE FileLocation Like '" = Me.txtSearch & "*' "
-
"ORDER BY FileLocation;"
-
Me.cmdLocationID.RowSource = strSQL
-
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.
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"
In the code above in Post #8,
insert the following between lines 6 & 7:
and post what is shown in the immediate window. If the Immediate window is not displayed, hit Ctrl-G.
The debug print just says: False
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.
-
-
Private Sub txtSearch_AfterUpdate()
-
-
Dim strSQL As String
-
strSQL = "SELECT LocationID, FileLocation " & _
-
"FROM tblLocation " & _
-
"WHERE FileLocation Like '" = Me.txtSearch & "*' " & _
-
"ORDER BY FileLocation;"
-
Debug.Print strSQL
-
Me.cmdLocationID.RowSource = strSQL
-
End Sub
-
And the error is occurring at line 10?
Are there any records that "should" match the search criteria?
This is really strange.....
This part is getting evaluated instead of concatenated: - "WHERE FileLocation Like '" = Me.txtSearch & "*' " &
The Equal sign should be an Ampersand: - "WHERE FileLocation Like '" & Me.txtSearch & "*' " &
@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.........
And I had that wrong all the way back in Post #2!!!
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?
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: - SELECT LocationID, FileLocation
-
FROM tblLocation
-
WHERE FileLocation Like 'New Amsterdam*'
-
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.
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: - Option Compare Database
-
Option Explicit
-
-
Private bLookupKeyPress As Boolean
-
-
Private Sub cboLookup_Change()
-
Dim sSQL As String
-
Dim sNewLookup As String
-
-
If Not bLookupKeyPress Then
-
sNewLookup = Nz(Me.cboLookup.Text, "")
-
sSQL = "SELECT Field2 FROM [tblBunchOfStuff] "
-
If Len(sNewLookup) <> 0 Then
-
sSQL = sSQL & " WHERE Field2 LIKE '*" & sNewLookup & "*'"
-
End If
-
Me.cboLookup.RowSource = sSQL
-
Me.cboLookup.Dropdown
-
End If
-
bLookupKeyPress = False
-
End Sub
-
-
Private Sub cboLookup_KeyDown(KeyCode As Integer, Shift As Integer)
-
Select Case KeyCode
-
Case vbKeyDown, vbKeyUp
-
bLookupKeyPress = True
-
Case Else
-
bLookupKeyPress = False
-
End Select
-
End Sub
-
-
Private Sub cboLookup_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
-
bLookupKeyPress = True
-
End Sub
-
@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.....
By using the ComboBox.Text property instead of the ComboBox.Value.
I thought I had tried that without success (note the head scratching....). Nice work!
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..... :-)
Bingo! AutoExpand was the key.
Now I know how to do this!
Thanks much!
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!
I really like the way that works. I'll probably end up using it in choice places. Thanks for the Question and Hepp.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: farseer |
last post by:
Hi,
I have a combobox who's data source i set to an array of objects (call
it MyObject). these objects have get properties: key, value, descr. i
set ValueMember to "key", DisplayMember to...
|
by: juststarter |
last post by:
Hello,
I have an aspx file where i've put a placeholder element. On load
(page_load) i create dynamically an html table which contains a checkbox and
a radiobuttonlist in each tablerow . The...
|
by: Brian Henry |
last post by:
say I have a databound combo box, which allows for text entry still
(DropDownStype = DropDown in this case).
now, If the user doesn't enter manually an item in the data bound list,
nothing...
|
by: Smokey Grindle |
last post by:
Say I have a combo box with the following simple object
Public class MyObject
public ID as integer
public Name as string
public overrides sub ToString() as string
return name
end sub
end...
|
by: Academia |
last post by:
I have a combobox that when it drops down the selected item is not
highlighted.
I'm guessing there is a property that controls this but can't find one.
Is there?
Should it always be...
|
by: kurtzky |
last post by:
i created a form that should function as follows:
i will enter a number in a textbox..then it should query from the database all the records which has that number..these records will have a...
|
by: sbandalli |
last post by:
Hello,
I have a Datagridview which has a combobox,and 2 textbox, The combobox is bound to a Datasource(Database Sql Server and the table name is Category)
,and Datagridview is not bounded to any...
|
by: Tyler Wiebe |
last post by:
First, here's a few things...
#1 -- The ContextMenu
this.Tray_Icon_Context_Menu.MenuItems.Add("Open Tray", new System.EventHandler(Open_Tray));...
|
by: sheela gupta |
last post by:
I want to populate the combobox based on the selected item in the first combobox
The following code is written in vb.net 2008
Public Class Form1
Sub fillcombo()
strsql = "select *...
|
by: doubler |
last post by:
This seems like it should be fairly simple, but it has me stumped.
I have a combobox that is bound to a dictionary. I also have a datagridview that is supposed to populate the combobox when a row...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
| |