1

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;
braX
  • 11,506
  • 5
  • 20
  • 33
MiniG34
  • 312
  • 2
  • 12

1 Answers1

3

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.

Erik A
  • 31,639
  • 12
  • 42
  • 67