Select Items in List from a String

mwn1218 · February 7th, 2005, 05:08 PM

I have a string that I am storing in a table (strFilter). The string contains some of the projects that an employee is worked on (this was used to print a report and am sacing the string so that the users can select an existing report and not have to reselct all the items):

[ProjID] in ('1','2','5')

On a form there is a list box that contains all the projects an employee is worked on. The user can select an existing report and make changes to which projects they want to print. Say the user selects the abovce reoprt. How can I get those projects selected in the list box on the form. There is a drop down box showing all the existing reports.

Want it like this

[u]Project ID</u>
1
2
3
4
5
6
7
8
9
10

Thanks for the help.

mmcdonal · February 11th, 2005, 11:21 AM

Hi,

   I think I can help if you explain things a little better. I am not sure I know what you are doing:

You have a list of ProjectID's in a table seperate from an actual project table? If so, why?

On a form you have a list box. Does this display the project number or a project name? If the latter, which column is bound?
The user can select a report and make changes... do you mean they can select a form, make changes, and print a report? You can't make changes to a report. So what do you want to do, open a report or a form?

Then you say you have them select multiple projects by their ID number? Is that the case? Then you are having them open a form with just those projects. Is that really what you want them to do?

Then why is there also a combo box showing all the existing projects?

Here is what I would do if I wanted to select multiple projects and then open a form with just those projects so that I could make changes to those projects. (Printing a report is a different issue):

1. Create a main form.
2. Using the Combo Box wizard, create a combo box that looks up the project numbers from the projects table. This may or may not be the primary key of the table. It looks like it is. SO then I would have the combo box look up the NAME of the project, and hide the project number, unless the number is somehow significant, which it shouldn't be. I would then allow multiple selections from the combo box's properties dialog box.
3. Assuming I had created a form to display the projects called "frmProjects", I would use the Button wizard to Open the frmProjects form and link it to the combo box using the wizard.

This is the sort of code the wizard will create from the button:

'==========
    Dim stDocName As String
    Dim stProjectID As Long
    Dim stLinkCriteria As String

    stProjectID = Me.cboProjectSelect

    stLinkCriteria = "[ProjectID] = " & stProjectID

    stDocName = "frmProjects"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
'==========

Please note that when the PK is used, it is best to pass it as a long, and then not use single quotes in the link criteria. If it had been a string you were passing, then it would look like this:

'==========
    Dim stProject As String

    stProject = Me.cboProjectSelect

    stLinkCriteria = "[ProjectName] = " & "'" & stProject & "'"

'==========

   This last one assumes you are bound to column 2 in your combo box, and not column 1, which will contain the PK as Long.

HTH

mmcdonal