Filling a list box from a Combo Box Query

[Guest]

I have an appliciation that is driving me mad. It should be so simple. I have
a combo Box which gets its data from a Query, which all works fine. Based on
the results in the combo Box a List box should fill with data from the same
query.

Unforunately the system does not work, the Combo box has the correct data
but the list box stays empty.
Partsof the code I am using is as follows :

Option Explicit
Private Const StrSQL1 = "SELECT Name, Date, Source FROM QryNotArchived WHERE
source = '"

Private Sub FillList()
strSQL = StrSQL1 & Me!Combo15.Value
Me!LstBox.RowSource = strSQL
Me!LstBox.Requery
If Me!LstBox.ListCount = 0 Then
Me!lblLIst.Caption = "None"
End If
End Sub

Any Help would be extremely appreciated
Thanks
Murray

 

[Guest]

Hi Murray - it looks as if your quotes don't add up. Your constant has a
single quote after the equals sign while the first line of your subroutine
doesn't add an equivalent quote mark after the combobox value.

How about: strSQL = StrSQL1 & Me!Combo15.Value & "'"

or perhaps make both quotes more explicit and leave the single quote out of
the constant? Watch out though, if your combo box has a numeric value you
shouldn't have the quotes at all.

Quotes are a nightmare in SQL - have a search through the community for ways
round (Chr(34), quote functions, etc.).