none
Access 2013 Combo Box in Form RRS feed

  • Question

  • I am using a combo box to select fields in a query, I have one box for Month and another for year. I would like to be able to select all months or all years. So far I have tried * and Like "*" in the combo box , neither works. The combo boxes are tied to tables, I added * and Like "*" to each of the table with no success.  tblMonth  field monthname is data type Short Text. tblYear field YearField is data type Short text.
    Thursday, May 12, 2016 12:52 PM

Answers

  • To select multiple parameter values you should use multi-select list boxes in the dialogue form.   A multi-select list box has no value, however, so cannot be referenced as a parameter.  Rather, it has an ItemsSelected collection, which is a collection of variants, each of which identifies a row selected in the control.  It is necessary therefore to loop through the collection in code and assign each value to a text box (usually hidden) in the dialogue form to create a value list.  Normally in a query the IN operator would be used to return rows which match any of the values in the list.  However, the IN operator does not support a parameter as its argument, only a literal string expression.  Microsoft has published two functions,  InParam and GetToken, which can be used to simulate the use of the IN operator with a parameter.

    You'll find an example of a mullti-select list box used in this way in MultiSelect.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file the option to 'Restrict Report's Underlying Query on One Field' opens a form in which one or more values (employees) can be selected.  The query for the report in question is:

    PARAMETERS [Forms]![frmEmployeeDlgParam]![txtEmployeeIDList] TEXT (255);
    SELECT DISTINCT Employees.*, FirstName & " " & Lastname AS FullName, Projects.Project
    FROM Projects RIGHT JOIN (Employees LEFT JOIN ProjectEmployees
    ON Employees.EmployeeID=ProjectEmployees.EmployeeID)
    ON Projects.ProjectID=ProjectEmployees.ProjectID
    WHERE InParam([Employees].[EmployeeID],[Forms]![frmEmployeeDlgParam]![txtEmployeeIDList])=TRUE;

    In my example only one list box is used, but it would be a simple matter to use three in the same way, each with a hidden text box control in the form to hold the value list.  The query's WHERE clause would then simply need to call the InParam function three times, using a Boolean AND expression to return those rows where the three columns contain values which match any item selected in the list box relevant to each column.

    Ken Sheridan, Stafford, England

    Friday, May 13, 2016 10:48 PM

All replies

  • I'm not sure how the entries in these combo boxes are being used to form the SQL for your query but it seems to me you need to allow the month to be blank or the year to be blank. And in that case you simply omit that part of the WHERE clause. E.g when both Month and Year are specified:

    WHERE MyMONTH = '01' AND MyYEAR = '2005'

    and when only Month is specified:

    WHERE MyMONTH = '01'

    Thursday, May 12, 2016 10:32 PM
  • I would simply add a row to each combo box's referenced table with values such as 'Any Month' and 'Any Year'.  the query which references the combo boxes as parameters would then be along these lines:

    SELECT *
    FROM YourTable
    WHERE (YourYearColumn = Forms!YourForm!cboYear
    OR NZ(Forms!YourForm!cboYear,"Any Year") = "Any Year")
    AND (YourMonthColumn = Forms!YourForm!cboMonth
    OR NZ(Forms!YourForm!cboMonth,"Any Month") = "Any Month");

    Ken Sheridan, Stafford, England

    Thursday, May 12, 2016 10:47 PM
  • Hi Matt Slammer,

    please check the suggestion given by the Ken Sheridan. it can able to solve your issue.

    please check it and let us know it worked for you or not.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, May 13, 2016 6:27 AM
    Moderator
  • Hopefully this adds more clarity to what I am doing: I created a From, called frmSelector. This form has the combo boxes for TaskType, Year and Month. Each combo box gets its choices from a table. One table per combo. tblTasks, tblYear, tblMonth. The query then uses the value from the combo box as the criteria it uses to filter table M6_Data_CT. I want to be able to select one or more month or year or task, as well as all the years, tasks and months. as thins are now , I can only select one month, one year, one task. Here is a portion of the SQL as it is now:


    GROUP BY
    M6_Data_CT_Compl.TASK_TYPE,
    MonthName(Month([DD_TASK_COMPL_DT]),True),
    Year([DD_TASK_COMPL_DT]),
    M6_Data_CT_Compl.USERID,
    M6_Data_CT_Compl.EMPLOYEE_NAME,
    IIf([SAL RANGE]="UNI",0,IIf([SAL RANGE]="A",1,IIf([SAL RANGE]="B",1,IIf([SAL RANGE]="C",1,IIf([SAL RANGE]="D",2,3))))),
    UCase([SUPV CORPID]),
    M6_Data_CT_Compl.State

    HAVING
    (((M6_Data_CT_Compl.TASK_TYPE)=[Forms]![FrmSelector]![cmbTaskType])
    AND ((MonthName(Month([DD_TASK_COMPL_DT]),True))=[Forms]![FrmSelector]![cmbMonth])
    AND ((Year([DD_TASK_COMPL_DT]))=[Forms]![FrmSelector]![cmbYear]))


    Friday, May 13, 2016 8:16 PM
  • To select multiple parameter values you should use multi-select list boxes in the dialogue form.   A multi-select list box has no value, however, so cannot be referenced as a parameter.  Rather, it has an ItemsSelected collection, which is a collection of variants, each of which identifies a row selected in the control.  It is necessary therefore to loop through the collection in code and assign each value to a text box (usually hidden) in the dialogue form to create a value list.  Normally in a query the IN operator would be used to return rows which match any of the values in the list.  However, the IN operator does not support a parameter as its argument, only a literal string expression.  Microsoft has published two functions,  InParam and GetToken, which can be used to simulate the use of the IN operator with a parameter.

    You'll find an example of a mullti-select list box used in this way in MultiSelect.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file the option to 'Restrict Report's Underlying Query on One Field' opens a form in which one or more values (employees) can be selected.  The query for the report in question is:

    PARAMETERS [Forms]![frmEmployeeDlgParam]![txtEmployeeIDList] TEXT (255);
    SELECT DISTINCT Employees.*, FirstName & " " & Lastname AS FullName, Projects.Project
    FROM Projects RIGHT JOIN (Employees LEFT JOIN ProjectEmployees
    ON Employees.EmployeeID=ProjectEmployees.EmployeeID)
    ON Projects.ProjectID=ProjectEmployees.ProjectID
    WHERE InParam([Employees].[EmployeeID],[Forms]![frmEmployeeDlgParam]![txtEmployeeIDList])=TRUE;

    In my example only one list box is used, but it would be a simple matter to use three in the same way, each with a hidden text box control in the form to hold the value list.  The query's WHERE clause would then simply need to call the InParam function three times, using a Boolean AND expression to return those rows where the three columns contain values which match any item selected in the list box relevant to each column.

    Ken Sheridan, Stafford, England

    Friday, May 13, 2016 10:48 PM