Multiple choice selection

[Moridan]

Greetings all,

Using 2007 for a project at work. Here is what I have:
- Set up of tables that flow Division -> Branch - > Missions - > Projects -> Updates


- Form that inputs worker updates into the Update table. This is working just fine.
- Form that I use for Management to output the Update information based upon a DATE RANGE and the DIVISION or BRANCHES.
- There are multiple Branches in the Division
- I have the form feeding a Query that has a criteria for the input date. This is also working fine.

*** My problem is in selecting the Branch. I have a combo box that works just fine but can only select a single Branch. But I need the ability to select multiple Branches. I have tried a list box but it wont feed the query properly for some reason. I have tried everything I can think of to make this work, but am out of ideas.

Any assistance would be greatly appreciated. Thank you!

[pbaldy]

One way:

http://www.baldyweb.com/multiselect.htm

[Moridan]

Thanks Baldy. I guess part of my problem is that I am very new to Access. I understand that what you linked was VB code, but I havent a clue how/where to add this. There are so many variables in this that I am not sure what I can change and what has to remain.

I really appreciate your help!

[pbaldy]

This should get you started:

http://www.baldyweb.com/FirstVBA.htm

[Moridan]

Ok, I think a little light is trickling into my brain. Will the same code work for a combo box, or only a list box?

Thanks!

[pbaldy]

I've never used the combo box bound to a multi-value field, so I'm not sure how those operate. My gut is that code will not work for those, but I'm not sure.

[Moridan]

Ok, finally back to this project. I transferred the code into my database but when I click the button to open the report a window pops up asking "Enter Parameter Value" on the top, the name of the selection that was made from the list box below that, and then a data entry space below that. If I type in the same data that was in list box it runs the report as it should. But why is it asking for this information again? The only thing I could think of was that somehow I didnt correctly transfer this line:

'open the report, restricted to the selected items
DoCmd.OpenReport "rptEmployees", acPreview, , "EmpID IN(" & strWhere & ")"

I replaced prtEmployees with the name of my report that is running and EmpID with the name of the field on the table where the selection is coming from.

What I dont understand is "acPreview" and the "IN" after "EmpID".

Any ideas what I am doing wrong? Thanks again for the help!

[pbaldy]

VBA help has info on those. EmpID has to be the name of the field in your data.

[Moridan]

Or is my problem with this section:

Set ctl = Me.lstEmployees
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ","

[Moridan]

Ahh I was going off the example db and mistyped this statement from the example code on your website:

'Use this line if your value is text
'strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"

One step closer to getting this done, but that was the major hurdle. Thanks!

[pbaldy]

No problemo!

[Moridan]

Now the last step is to get the report to output in Word. How can I modify the Report line to output this as a Word document?

[pbaldy]

I've never needed to output to Word. Right after the OpenReport line try using OutputTo.

[Moridan]

Doing some research on "OutputTo". Are you suggesting I start another DoCmd function on the line below the DoCmd.OpenReport function? I think if I replace the line than I lose the ability to restrict the selected items. I would even be Ok with opening in a pdf format.

[pbaldy]

Yes, I'm suggesting another line, for the reason you note. If the report is already opened filtered, then the OutputTo will be filtered.