Create a filtered list (form or report) from a parameter selected in a ComboBox

[Lara]

Hi - I could really do with some help.
I am trying to create an action that will open a list of items in Access 2007 that are filtered acording to what has been selected in a combolist.
An example of what I am trying to do is included in the Northwind Access 2007 database but I can't work out how to do it. In the Northwind Database they use this twice:
1) In the Login Screen When you press Login it opens a screen and passes a temporary variable into the new form (home form) that it opens to only show that employees items.
2) In the home screen at the top of the screen you can select the name of someone or change the selection and on select it changes the details in the open form accordingly.

I have been trying to work through the macros to replicate this functionality but I am finding it difficult.
What I have been able to do is:
* Open a form or a report filtered based on a parameter that Access prompts me for (eg CurrentEmployee) when I open it. But I need to enter it manually.
* Create the combobox that selects an employee and then either opens a form when you select the record or create a button that opens a form next to the selection box.

What I can't do and need help with is I can't work out how to get the field that I am selected in the ComboBox into the parameter when I open a form that requires that parameter.

An alternative approach would be to use the OpenForm macro when I press the button and use the "Where Condition". However, again I can't work out how to get the selected value as a temporary variable into the Where Condition. All the examples I find get the variable from a table rather than from the combobox.

Please help.

Thanks,
Lara

 

[Lara]

Hi - I didn't get any reply but I eventlually worked it out myself so thought I would post the solution as it was hard to come by. I was trying to use macros and avoid VB so that made it doubly hard. One of the problems I found was that not all of the books have a lot about using temporary variables, because it was new functionality with 2007 and so I guess there wasn't much time to explore how can use this before they had to run to publish. Also the form creation wizard is broken that creates the forms so that you can't base the opening on a variable. That meant I wasted time and money reading most of The missing Manual till I found out it wasn't there. Got what I needed in the Access 2007 Bible though although none of the books give it the attention it deserves.

This guy also touches on the topic and tals about the .value thing.

The main problem that I found when I tried to replicate the Northwind database was that you needed to add the .value to the end of the attribute that you are passing into the temporary variable.



End solution is on the combo box that you select the name from you have a macro in the 'after update' area within the event tab within the properties:

ACTION: SetTempVar

Argument: CurrentUserID, [Screen].[ActiveControl].[Value]



You can test with msgbox actions to see what the variables are coming over. That will give you the ID number related to the name you are selecting.

I also tested passing other variables and it worked using [cboSelectName] when that is what I called that Combo Box.



Then once you have done this you create a macro for a new button in the properties tab On Click:

Condition: Not IsNull ([cboSelectName])

Action: SetTempVar

Arguments: LogiID, [TempVars]![CurrentUserID].[Value]



Then

Action: OpenForm

Argument: Task Form, Form, [AssignTo]=[Tempvars]![LoginID], , Normal



Of course then you need to put in error management and refreshes etc but the above should give you the basics.

So that approach uses the Where condition. I didn't find a solution that feed the parameter when you open a report. If anyone know how to do that with a temp vars so it doesn't come up with a popup box I'd be curious.



EASY! So powerful though can't believe it is not in all the "Basics" sections in the manuals.

Good luck