Problem changing the rowsource of a List Box filtered by combo box

[g3000]

I am using Access 2003 on Win2K3 Server EE

I created a form with one combo box an one list box.

The combo box is based on a select distinct of last names from a table.

After the combo box is updated the list box will filter on that value.
Below is my code:

Private Sub Combo3_AfterUpdate()
Dim myQuery As String

myQuery = "SELECT PERSONAL.ID, " & _
"PERSONAL.SALUTATION, " & _
"PERSONAL.FNAME, " & _
"PERSONAL.LNAME, " & _
"PERSONAL.CITY, " & _
"PERSONAL.STATE " & _
"FROM PERSONAL " & _
"WHERE PERSONAL.LNAME =" & Me.Combo3.Value & _
"ORDER BY [LNAME];"

[List7].RowSource = myQuery
[List7].Requery
End Sub

When I update the combo box the list box is not updated.
Is this the hard way. Is there a better way?

Thanks for your time. Im new to VBA not programming.

 

[Douglas J Steele]

I'm assuming that when you say "the list box is not updated", you mean
nothing appears, rather than its content doesn't change.

Odds are that field LNAME in table PERSONAL is a text field, which means
that values being used as a criteria need to be enclosed in quotes.

myQuery = "SELECT PERSONAL.ID, " & _
"PERSONAL.SALUTATION, " & _
"PERSONAL.FNAME, " & _
"PERSONAL.LNAME, " & _
"PERSONAL.CITY, " & _
"PERSONAL.STATE " & _
"FROM PERSONAL " & _
"WHERE PERSONAL.LNAME =" & _
Chr$(34) & Me.Combo3.Value & Chr$(34) & _
" ORDER BY [LNAME]"

Using Chr$(34) (which equates to ") means that names with apostrophes in
them (such as O'Hara) will not cause an error.

 

[g3000]

Thanks Doug,
Apparently the issue was that there was not a space between
the end of the value of lname and the start of ORDER BY

After adding a space it worked.

BUT, why does my combo box after selecting a value pop up a Enter
parameter value textbox?

After entering the value for parameter it works but dont know why the
parameter box is coming up.

I checked to see if I based the combo box on a named query and I didnt.
The query is
select distinct lname from personal

 

[g3000]

Never mind,

I added the change to the query as you posted. Seems that if the lname
did not have spaces it worked prompting for the parameter based on the
"&"

So now it works fine. Thank you.