VBA: Combobox ID Value as Query Parameter not longer working

Joined
Oct 9, 2018
Messages
31
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

Active Member
Joined
Jul 16, 2010
Messages
471
You need to populate the parameters, something like:

Code:
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
 
Joined
Oct 9, 2018
Messages
31
You need to populate the parameters, something like:

Code:
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

Active Member
Joined
Jul 16, 2010
Messages
471
The way I posted should automatically evaluate each parameter without explicitly coding each one separately.

Code:
[COLOR=#0000ff]For Each Param In qdfFillDictionaryOptional.Parameters
    Param.Value = Eval(Param.Name)
Next Param[/COLOR]
 
Joined
Oct 9, 2018
Messages
31
The way I posted should automatically evaluate each parameter without explicitly coding each one separately.

Code:
[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
 

Forum statistics

Threads
1,078,356
Messages
5,339,729
Members
399,319
Latest member
sut3k

Some videos you may like

This Week's Hot Topics

Top