Populating a combo box

[ColinSully]

I need help with a form I am creating.

I have a number of reports that are available to be printed by a user. The reports are based in 4 categories: Status, Budget, Schedule and Summary.

In each of these categories there are different reports. ex. Budget there is On budget, Under Budget, over budget, etc.

I want to be able to allow a user to select one of the categories in a combo box and then a second combo box will be populated with the coices of reports. I don't mind adding in extra tables to list the reports as they will not be changing

If someon can help me out here it would be great.

Regards.

 

[ColinSully]

Setting the control source

I have figured this partially out. I can set up a table for each of the categories with each listing the different reports available.

My problem now is that I don't know how to set the control source of the combo box.

I am thinking about using an _AfterUpdate event to set the control source of the second combobox based on the .Value of the first. Can someone please explain to me how I can set this up???

 

[SydneyGeek]

Hi Colin, you're on track.

This post shows a more complex system of 3 combos in sequence, but it shoudl give you the idea.

Denis

 

[ColinSully]

Thank you so much, that worked great.

Now I have one more issue that I hope someone can resolve.

I followed the instructions on the reffered post. I have a project summary report that is printed. This report is based on a query with a RequestedID.

This requested ID is what I have in the text box on my form. How can I pass this value along so that when I try to view the report, it simply takes the ID from the text field instead of asking for it?????

 

[SydneyGeek]

Hi Colin,

Open the form and then (with the form still open) go to the query that undelies the report. In Design view, find the field that you want to filter on. In the Criteria row for that row, click the Builder button (...). You'll end up in the Expression builder.
IN the first column, open Forms, then Loaded forms.
The second column has a list of all the controls on that form. Find the combo box whose value you want to filter by, and double-click it.
In the Expression space at the top, you'll see
=Forms![Your Form Name]![Your Combo Box Name]
Click OK, then close and save the query.
Now, if you don't have a button that launches the report, do this:
Switch to Design view in the form.
Display the Toolbox if it's not already there, and click the Wizards button so it looks activated.
Drag a Command Button onto the form. The Wizard launches.
Follow the prompts to Open (or Preview) a report, select the report name, and give the button a caption.
That should set you up.

Denis

 

[ColinSully]

Thanks, that kinda worked for me. I have a bunch of "expressions" built into the report and it doesn't seem to want to filter those.

The expressions are still returning for each of the records, even though I only want one there.

I know this is vague but I am truly confused with this.

Colin

 

[SydneyGeek]

Hi Colin,

does this mean that you have several sub-reports, or that you have a number of calculations pulling data into the system? Without seeing your report I can't comment too much but usually you will build a report around one main query or SQL expression. (IN the report's Prperties, click the DATA tab and click the Builder button on the Control Source row). This is the place where you can apply that filter.

If I have misunderstood, please clarify and I'll try to help.

Denis

 

[ColinSully]

I don't know why it worked but it did. I needed to put the

=Forms![Your Form Name]![Your Combo Box Name]

in 2 different spots in the query as criteria, one in the "RequestedID" and one as a "ProjectID"

I have no idea why there are 2 of them but it works just like I want it to and as the old saying goes, if it ain't broke, don't fix it.

So hopefully it won't break

Thanks a lot for all your help.