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

  • Thread starter Thread starter g3000
  • Start date Start date
G

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.
 
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.
 
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
 
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.
 
Back
Top