VBA: Combobox ID Value as Query Parameter not longer working

[Roy_Excel_Island_Apps]

Hi experts,

I have a VBA code that opens a recordset:

Private Sub Form_Load()
Dim qdfFillDictionaryOptional As DAO.QueryDef
Dim rsFillDictionaryOptional As DAO.Recordset


Set qdfFillDictionaryOptional = CurrentDb.QueryDefs("qryFillDictionaryOptional")

Set rsFillDictionaryOptional = qdfFillDictionaryOptional.OpenRecordset (here I get the error)

But on the last line I get a "Too few parameters. Expected 2" error message. The parameters ([Forms]![frmHidden]![txtEmployeeID] and [Forms]![frmHidden]![txtFunction_ID]) are 2 text fields on a hidden form and they contain an ID when I want to open the recordset.
When I run the query manually, it works just fine... But not when opening the recordset...WHY???

This is the SQL of the qryFillDictionaryOptional:
SELECT tblCompetence_Knowledge.Competence_Knowledge_ID, tblCategoryType.CategoryType, tblRole.Role, tblCategory.Category, tblCompetence_Knowledge.Competence_Knowledge, tblCompetence_Knowledge.Description, tblScale.ValueRangeMin, tblScale.ValueRangeMax, tblEmployee.EmployeePnr, tblRoleType.RoleType, tblEmployee.Employee_ID, tblFunction.Function_ID, tblScale.Scale_ID, tblCategory.Interesse, tblCategory.Energie, tblCategory.CategoryType_ID

FROM tblScale INNER JOIN (tblRoleType INNER JOIN (tblRole INNER JOIN (((tblFunction INNER JOIN (tblEmployee INNER JOIN tblLink_Employee_Function ON tblEmployee.Employee_ID = tblLink_Employee_Function.Employee_ID) ON tblFunction.Function_ID = tblLink_Employee_Function.Function_ID) INNER JOIN (tblCategoryType INNER JOIN (((tblCategory INNER JOIN tblCompetence_Knowledge ON tblCategory.Category_ID = tblCompetence_Knowledge.Category_ID) INNER JOIN tblLink_Categorie_Role ON tblCategory.Category_ID = tblLink_Categorie_Role.Categorie_ID) INNER JOIN tblLink_Function_Category ON tblCategory.Category_ID = tblLink_Function_Category.Category_ID) ON tblCategoryType.CategoryType_ID = tblCategory.CategoryType_ID) ON tblFunction.Function_ID = tblLink_Function_Category.Function_ID) INNER JOIN tblLink_Function_Role ON tblFunction.Function_ID = tblLink_Function_Role.Function_ID) ON (tblRole.Role_ID = tblLink_Function_Role.Role_ID) AND (tblRole.Role_ID = tblLink_Categorie_Role.Role_ID)) ON tblRoleType.RoleType_ID = tblLink_Function_Role.RoleType_ID) ON tblScale.Scale_ID = tblCategory.Scale_ID

WHERE (((tblEmployee.Employee_ID)=[Forms]![frmHidden]![txtEmployeeID]) AND ((tblFunction.Function_ID)=[Forms]![frmHidden]![txtFunction_ID]) AND ((tblRoleType.RoleType_ID)=3));

 

[stumac]

You need to populate the parameters, something like:

Dim qdfFillDictionaryOptional As DAO.QueryDef
Dim rsFillDictionaryOptional As DAO.Recordset
[COLOR=#0000ff]Dim Param As Parameter[/COLOR]
Set qdfFillDictionaryOptional = CurrentDb.QueryDefs("qryFillDictionaryOptional")
[COLOR=#0000ff]For Each Param In qdfFillDictionaryOptional.Parameters
    Param.Value = Eval(Param.Name)
Next Param[/COLOR]
Set rsFillDictionaryOptional = qdfFillDictionaryOptional.OpenRecordset

 

[Roy_Excel_Island_Apps]

You need to populate the parameters, something like:

Dim qdfFillDictionaryOptional As DAO.QueryDef
Dim rsFillDictionaryOptional As DAO.Recordset
[COLOR=#0000ff]Dim Param As Parameter[/COLOR]
Set qdfFillDictionaryOptional = CurrentDb.QueryDefs("qryFillDictionaryOptional")
[COLOR=#0000ff]For Each Param In qdfFillDictionaryOptional.Parameters
    Param.Value = Eval(Param.Name)
Next Param[/COLOR]
Set rsFillDictionaryOptional = qdfFillDictionaryOptional.OpenRecordset

Hi stumac, that's what I did to solve the problem but I couldn't continue working like this because now I need to set the parameter to:
qdfFillDictionaryOptional.Parameters("[cmbFilter_Competence_Knowledge_Optional]") =
"Iif([Forms]![frmEmployee_Competence_Profile_Overview]![cmbFilter_Competence_Knowledge_Optional] Is Not Null;[Forms]![frmEmployee_Competence_Profile_Overview]![cmbFilter_Competence_Knowledge_Optional];True)"

But this doesn't work because I don't know how to do it...

 

[stumac]

The way I posted should automatically evaluate each parameter without explicitly coding each one separately.

[COLOR=#0000ff]For Each Param In qdfFillDictionaryOptional.Parameters
    Param.Value = Eval(Param.Name)
Next Param[/COLOR]

 

[Roy_Excel_Island_Apps]

The way I posted should automatically evaluate each parameter without explicitly coding each one separately.

[COLOR=#0000ff]For Each Param In qdfFillDictionaryOptional.Parameters
    Param.Value = Eval(Param.Name)
Next Param[/COLOR]

I get a type mismatch on :
For Each param In qdfFillDictionaryOptional.Parameters

 

[Roy_Excel_Island_Apps]

I changed Dim param as parameter to Dim param as dao.parameter and that works! thanks!