VBA: Combobox ID Value as Query Parameter not longer working

Roy_Excel_Island_Apps

Board Regular
Joined
Oct 9, 2018
Messages
68
Office Version
  1. 365
Platform
  1. Windows
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));
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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
 
Upvote 0
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...
 
Upvote 0
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]
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top