I have a combo box based on a table that has 4 entries, Dept1, Dept2, Dept3and Dept4. How can I limit the values that the combo box displays based on 4 bound check boxes, Chk1, Chk2, Chk3 and Chk4 that are on another form? I've trawled the group and the wider web and am struggling to find an answer.

Wayne wrote:
Maybe you could define your issue with more detail.

Why would you base a combo on values from another form? What happens if
that form is closed or gets closed.

You might want to refer to the values like
Forms!FormName!TextBoxName
as a filter in your query.

Or look at OpenArgs that passes a value when opening a form
Docmd.Openform......

Or create a hidden control that grabs the value from the other form,
maybe OnOpen or OnLoad events and refer to that.

Obviously, You would have to change the underlying record source of the
combobox. Perhaps something like the following.

SELECT Department
FROM Departments
WHERE (Department = "dept1" and Forms!NameOfOtherForm!Chk1=True)
OR (Department = "dept2" and Forms!NameOfOtherForm!Chk2=True)
OR (Department = "dept3" and Forms!NameOfOtherForm!Chk3=True)
OR (Department = "dept4" and Forms!NameOfOtherForm!Chk4=True)

Is there some relationship between the departments table and the other table?


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

On 4/20/2011 6:35 AM, Wayne wrote:

Thanks for the replies Salad and John. I probably haven't given enough detail. I have a "Users" table that has fields for the user's name, their Windows logon and 4 check boxes for the relevant departments. The Department check boxes are ticked by an administrator according to which departments a particular user has been given access to.

I'm using the "GetUser" function to determine who is logged on to the database based on their Windows logon. I then query the Users table to determinewhich departments they have access to. The values of the "Departments" check boxes are then stored in check boxes on a hidden form that never closes.

I want to use the values of the check boxes on the hidden form to determinewhich Departments appear in the Departments combo(s) that will be used on various other data entry and query forms. If only Departments 1 and 3 are ticked on the hidden form, then only Departments 1 and 3 will appear in the combo.