CODEWITHSUNDEEP
vbams-access
MiniG34
MiniG34

Reputation: 332

How to use a SQL with Parameters as a Combobox Row Source in MS Access/VBA

I would like to define and use a SQL string with PARAMETERS as the RowSource of a form combobox. I am using PARAMETERS in order to prevent SQL Injection.

My SQL is defined as followed:

Dim strSql As String, strParm As String
Dim compID As Integer

compID = ...

strParm = "PARAMETERS [CompanyID] INT; "
strSql = strParm & "SELECT NAME, BUSINESS_NUMBER, FILE_ID FROM COMPANY WHERE COMPANY_ID = [CompanyID] ORDER BY CREATION_DATE;"

From the documentation I have read, such string is usually used with CreateQueryDef.

What is the proper way to use the result of this SQL using the value of compID as the RowSource of a combobox (Me!myComboBox.RowSource)?

As it is currently coded, the following works, but I would like to avoid string concatenation when building my SQL statement:

Me!myComboBox.RowSource = "SELECT NAME, BUSINESS_NUMBER, FILE_ID FROM COMPANY WHERE COMPANY_ID = " & compID & " ORDER BY CREATION_DATE;

Upvotes: 1

Views: 1462

Answers (1)

Erik A
Erik A

Reputation: 32682

Unfortunately, you can't directly use parameters in a rowsource.

You can, however, use parameters in a querydef, use that to create a recordset, and use that recordset to fill a combobox, e.g.:

With CurrentDb.CreateQueryDef("", strSql)
    .Parameters!CompanyID = compID
    Set myCombobox.Recordset = .OpenRecordset
End With

You can also use either TempVars or form-based parameters in row sources, e.g.

TempVars!CompID = compID
strSql = "SELECT NAME, BUSINESS_NUMBER, FILE_ID FROM COMPANY WHERE COMPANY_ID = TempVars!CompID ORDER BY CREATION_DATE;"

See How do I use parameters in VBA in the different contexts in Microsoft Access? for more info.

Upvotes: 4

Related Questions