Filter a combo box? 1

[gmacg]

Ok, picture this. Here is a menu form:

Open Department A Form
Open Department B Form
Open Department C Form

Each of the items on menu opens the same form. The form has an unbound combo box that pulls data from a query. What I would like to do is have the combo box selections limited to the employees in whichever department is selected on the menu form. The columns in the combo box are as follows

Column1
last name, first name

Column2
Department


Is there a way to do this? I don't want to create seperate forms for each department.

I've searched for the answer to this, but I have been unable to find what I want. Any help would be greatly appreciated.

 

[PHV]

Have a look at OpenArgs

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[gmacg]

Well, I've looked at that, but I don't know how to apply that to the items that the user can select in the combo box on the form that is opened. Currently, when the form is opened, it shows the usernames and departments for all of the different departments. I want the combo box limited to the usernames from department selected on the menu. Am I making sense?

 

[PHV]

In the Open event procedure of the form dynamically build the SQL code of the RowSource property of the combo box
with a WHERE clause taking in account the passed OpenArgs.


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[gmacg]

Thanks for the responses PH. I played around with the WHERE clause and the OpenArgs, but I just couldn't figure out how to get it to work. As usual I just approached the problem from a different direction and everything is working fine. I'll wait a few days and try it your way again. Sometimes I just can't see the forest for the trees. This web site has been a life saver for me.

I go to college to get a peice of paper. I come to Tek-Tips when I really want to learn something.

 

[TheAceMan1]

How are ya gmacg . . . . .

It appears you have what you need. Note in all code [blue]you![/blue] substitute proper names/text in [purple]purple[/purple].
[ol][li]In a module in the modules window, copy/paste the following function. The function is used to return OpenArgs to the query:

[blue]Public Function DeptArg()
   DeptArg = Forms![purple][b]CommonFormName[/b][/purple].OpenArgs
End Function[/blue]

[/li]
[li]In the [blue]criteria for Department[/blue] of the query/sql your using for the combobox, copy/paste the following:

[blue]DeptArg() Or DeptArg() Is Null[/blue]

[/li]
[li]Finally . . . [blue]for each call[/blue] to the commonform from your menuform, use the following line:

[blue]DoCmd.OpenForm "[purple][b]CommonFormName[/b][/purple]", , , , , , "[purple][b]DepartmentText[/b][/purple]"[/blue]

[/li][/ol]
Note: the code assumes [blue]Department is text[/blue]. If numeric, slight changes in code required, so let us know.

[purple]Thats it . . . give it a whirl and report any problems . . .[/purple]

See Ya! . . . . . .

 

[gmacg]

Thank you sooooo much, AceMan1. Works like a charm. All I needed was a little hand holding. Well....a lot of hand holding maybe. 'preciate it!