Combo Box Problems with Hyphens and Spaces

[rachelkm2]

Hi All,

I have an unbound combo box on a form called cbo_LastName. Because the row source for this combo box contains almost 100,000 distinct values, I have set it up to only load the row source after the first three characters has been entered.

So when the user starts typing, the combo box is empty. After the first three letters have been entered, say "smi" the combo box is populated and drops down. At "smi" the combo box shows "Smialy, Smikle, Smirnoff, etc" At "smit" the combo box shows "Smith, Smith Brown, Smith Hardy...Smith-Andrews, Smith-Brown, etc"

The problem comes when the user enters "smith " (smith followed by a space) or "smith-". Instead of showing all the values that begin with "smith " ie "Smith Brown" or that begin with "smith-" ie "Smith-Brown" the combo box goes blank...

The code for my combo box is below - if anyone can help, it would be much appreciated - I'm stumped!

FYI, I'm using Access as a front end to a SQL Server back end, and the row source I'm using is a pass-through query to a stored procedure. Not sure if this affects the behaviour of the combo box, but just in case.

Private Sub cbo_LastName_Change()

'Changes the contents of the LastName combo box after the first three letters have been entered

Dim db As DAO.Database
Set db = CurrentDb
Dim qdfLastName As DAO.QueryDef

Me.cbo_LastName.SetFocus

'Sets the Query Definition of the Last Name Lookup Query to take the value entered in the combo box
'as the input parameter
Set qdfLastName = db.QueryDefs("qry_Lkp_LastName")
qdfLastName.SQL = "usp_Lkp_LastName " & Me!cbo_LastName.Text

'When the number of letters entered in the combo box is greater than two,
'then set the rowsource of the combo box to the lookup query and drop it down
If Len(Me!cbo_LastName.Text) > 2 Then
    Me!cbo_LastName.RowSource = "qry_Lkp_LastName"
    Me!cbo_LastName.Dropdown
Else
    Me!cbo_LastName.RowSource = ""
End If

qdfLastName.Close

End Sub

 

[BBQ Kittens]

Im just throwing out an idea since I cant test it with your database, but it feels like the culprit is in your if statement. Perhaps removing '.text' ---> If Len(Me!cbo_Lastname) > 2. I get the feeling when you enter that 'space' or hypen, your rowsource is dropped...sorry if I am way off.

 

[rachelkm2]

Thanks for the quick response.

I had initially read that ".Text" was a potential issue, but when I try my If statement using either

Len(Me!cbo_Lastname) > 2

or

Len(Me!cbo_Lastname.Value) > 2

my IF statement stops working!

 

[BBQ Kittens]

Hmm, This is something I wrote earlier today. I made no reference to text or value and it works perfectly.

If Len([cboJobNumber]) = 7 Then
    Me.cboJobNumber2 = Left([cboJobNumber], Len([cboJobNumber]) - 3)
Else
    Me.cboJobNumber2 = Me.cboJobNumber
End If

How is this data formatted in your table? ie text, memo, etc

 

[rachelkm2]

When I don't use ".Text" in my IF statement, the combo box doesn't fill at all (no matter how many characters the user types in).

The data format (which originates in a SQL Server table) is Varchar(50).

 

[BBQ Kittens]

This one is baffling me. I wish I could have helped more, but my knowledge of SQL Server tables is nada. Hopefully someone more experienced will have the solution. Good Luck!

 

[rachelkm2]

In case anyone else comes across the same problem, here's the solution I just discovered (I have a vague understanding of why this works, but it does).

My original syntax was:

qdfLastName.SQL = "usp_Lkp_LastName " & Me!cbo_LastName.Text

Which worked fine until the last name had a space or a hyphen. The following handles the space or hyphen fine:

qdfLastName.SQL = "usp_Lkp_LastName " & "'" & Me!cbo_LastName.Text & "'"

 

[vbaInet]

This

qdfLastName.SQL = "usp_Lkp_LastName [COLOR=Red]" & "[/COLOR]'" & Me!cbo_LastName.Text & "'"

could be written as this

qdfLastName.SQL = "usp_Lkp_LastName '" & Me!cbo_LastName.Text & "'"

 

[missinglinq]

... it feels like the culprit is in your if statement. Perhaps removing '.text' ---> If Len(Me!cbo_Lastname) > 2.

While the cursor is still in a Control, which has never previously had data entered, the Control has Text, but does not have a Value. Under this circumstance you have to use the Text Property.

Linq ;0)>