combobox recordsource query using vba function?

[Joe]

The data to populate a combobox resides in a separate database. I need to
use the sql returned by my function below to be the rowsource. Is this
possible? The result of the function works in a query in design view.

I'm open to other suggestions.


Function GetNamesSql() As String

Dim strFile As String
Dim strSql As String

Const NamesDB As String = "NamesDataBase.mdb"

strFile = Application.CurrentProject.Path & "\Data\" & NamesDB

strSql = "SELECT DISTINCT Description AS Expr1"
strSql = strSql & " FROM [MS Access;DATABASE=" & strFile &
"].tblReportingPeriod"
GetNamesSql = strSql
End Function

 

[J_Goddard via AccessMonster.com]

Hi -

Almost right. You need the IN clause of the select statement:

Function GetNamesSql() As String

Dim strFile As String
Dim strSql As String

Const NamesDB As String = "NamesDataBase.mdb"

strFile = Application.CurrentProject.Path & "\Data\" & NamesDB

strSql = "SELECT DISTINCT Description AS Expr1"
strSql = strSql & " FROM tblReportingPeriod IN " & strFile
GetNamesSql = strSql
End Function

John

The data to populate a combobox resides in a separate database. I need to
use the sql returned by my function below to be the rowsource. Is this
possible? The result of the function works in a query in design view.

I'm open to other suggestions.

Function GetNamesSql() As String

Dim strFile As String
Dim strSql As String

Const NamesDB As String = "NamesDataBase.mdb"

strFile = Application.CurrentProject.Path & "\Data\" & NamesDB

strSql = "SELECT DISTINCT Description AS Expr1"
strSql = strSql & " FROM [MS Access;DATABASE=" & strFile &
"].tblReportingPeriod"
GetNamesSql = strSql
End Function