General discussion


Access combo boxes: limiting rows shown

By mytfein ·

Some background:

I relatively new to access, and found and interesting article on this website, describing how to limit rows shown in a combo box.


While the code I wrote (inspired by article)
works, I seem to be losing, what the user typed in...If user types "G", words the combo box drops down with employees whose last name begins with "G"....

HOWEVER, the "G" has disappeared, the text portion of combo box is blank/null.....

Does anyone have ideas?
1) I tried to comment out the me.requery, and set auto expand = false, and still have the problem

2) If I modify the combo box's .text property,
using vba, after the .dropdown, the onChange event fires, and I end up in a loop of always running the onChange event.

Please see code below....I would most gladly send the mdb, if that would help....thx!
Private Sub cboByName_Change()

Dim strBackSql As String
Dim strFind As String

strFind = Me!cboByName.Text

Const TWIPS = 1440

strBackSql = "Select ssn" & _
" ,[Last name] " & _
" ,[First Name]" & _
" FROM STAFF1" & _
" WHERE [Last Name] LIKE " & _
pgc_strQuote & _
strFind & pgc_strAsterisk & _
pgc_strQuote & _
" ORDER BY [last name], [first name]"

Debug.Print strBackSql

With Me.cboByName
.RowSource = ""
.RowSourceType = "Table/Query"

.LimitToList = True
.ColumnCount = 3
.ColumnHeads = False
.BoundColumn = 1
.ListWidth = 4 * TWIPS
.ListRows = 8
.ColumnWidths = "0 in; 1.5105 in; 1.5938 in"
.LimitToList = True
.AutoExpand = False 'True
.RowSource = strBackSql
' .Requery

End With


End Sub

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Related Discussions

Related Forums