VBA: Combobox ID Value as Query Parameter not longer working

Joined
Oct 9, 2018
Messages
34
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
34
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
34
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,082,126
Messages
5,363,321
Members
400,725
Latest member
excelingtolearn

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top