sql statement as rowsource

[superemilio]

Hi, I would like to set a sql statement as the row source for a listbox in a form, something like:
select * from table WHERE FIELD = [cmbXX],
where cmbXX is a control , for example a combo box.
I can do this when I open a "normal" database (mdb), but when i try to do it in a project (adp) i get an error message.
Thanks

 

[cmmrfrds]

Return the variable with a function.

In standard module.
Public myvar as string

Public Function ReturnMyvar() as String
ReturnMyvar = myvar
End Function

In Onclick of combobox.
myvar = Me.ComboName.column(1)

On Open Event of Form
Dim mysql as string

mysql = "select * from table WHERE FIELD = " & _
chr(39) & ReturnMyvar() & chr(39)
Me.RecordSource = mysql

 

[superemilio]

Thanks so much! your idea was of great help. I modified it slightly so i didnt use the function, only setting the rowsource string in the click event of the listbox:

sql = "select * from Customers where Customerid = '" & cmbxx & "'"
Lista4.RowSource = sql