VBA and SQL for comboBox RowSource

[Telecom]

I'm trying to run code on the VBA Enter Event of a comboxBox that will select a specific RowSource based on another control information.

Here is a portion of my code:
Me.temp_fld_DLCI.RowSourceType = "Table/Query"
Me.temp_fld_DLCI.RowSource = ""
Me.temp_fld_DLCI.RowSource = "SELECT DISTINCT tbl_Circuits_Lines.fld_DLCI " & _
"FROM tbl_Circuits_Lines " & _
"WHERE (((tbl_Circuits_Lines.fld_DLCI)<>"" And (tbl_Circuits_Lines.fld_DLCI) Is Not Null)) " & _
"ORDER BY tbl_Circuits_Lines.fld_DLCI;"

temp_fld_DLCI is the name of my comboBox. I build the query off the comboBox first to make sure I enter the correct SQL code. I copied that code to VBA to make my SQL SELECT statement out of it.

When I select the pull down of the comboBox I get the following error message:
Synex error in string in query expression '(((tbl_Circuits_Lines.fld_DLCI)<>" And (tbl_Circuits_Lines.fld_DLCI) Is Not Null)) ORDER BY tbl_Circuits_Lines.fld_DLCI;'.

Am I doing something wrong with my SQL code? I tried multiple versions of the SQL statement and I still get the error.

Thanks!!!

When I manual enter the SQL code as the rowsource it runs fine.

 

[Telecom]

bump - please help.

 

[KenHigg]

Q. In the bigger picture - All you want is a list fld_DLCI's from the table tbl_Circuits_Lines and you want to make sure there are no duplicates or blank spaces and you want the list to update if a new fld_DLCI is added?

???
kh

 

[Telecom]

In the bigger picture everything you listed is correct but the last part. I don't need to update the list when a new fld_DLCI is added.

The code that I have listed is just a sample of what I'm trying to accomplish. With your assistance figuring out the SQL code with the .rowsource statement I should be able to figure out the rest.

Thanks!

 

[Mile-O]

"WHERE (((tbl_Circuits_Lines.fld_DLCI) <> """" And (tbl_Circuits_Lines.fld_DLCI) Is Not Null)) " & _


When using " in a string you have to double it up otherwise the VBA editor will assume you are terminating the string.

 

[Telecom]

Awesome, that worked! Thanks!

 

[KenHigg]

Thanks for bailing me out on that one SJ - I'm not very swift with SQL...

kh