Manual selection of items, creating report from them..

[kroenc17]

Access '07

1. I need to create a form that lists all the Projects in my database. They need to have a check box next to them.

2. I need to be able to select multiple projects and then generate a report based on which projects are selected.

3. I need a report which shows the selected projects most recent update. I already have a query to do this, but it's not based off of the above form which limits the projects to only those selected.

Thanks for any help!

 

[jzwp22]

Have you already built the report based on the query you created?

Might I suggest an unbound form that has a multi-select list box for your users to select the projects of interest? You can then use some code to gather up the selected projects and pass that as a filter to the report. I've attached a simple database that provides an example.

 

[kroenc17]

THANK YOU this is perfect! Yes, I've been trying to use a List Box for this, not just a check box next to all the projects because there are several. I've been trying to get this to work by macroing a requery of the list box on the after update and setting the data source for the "Project Name" on my report to [Forms]![Form1]![List1] but that's not working.

I see the filter you're running on the report. Yes, I've already got the report built - how do I set it to filter like that? I'm doing the same thing you've got going with the List Box on the form.

 

[jzwp22]

I don't know if you can do this with a macro. If you look at the code behind the button on the form, you will see what I have done. Basically, I am creating a string that includes the primary key values that correspond with each project (not the project numbers or names) selected in the list box which is then used to filter the report. You have to loop through the list box and find those items that are selected and include those in the string.

 

[kroenc17]

So here's your code:

Private Sub cmdClear_Click()
Me.[COLOR="Red"]lstProjects[/COLOR] = Null

End Sub

Private Sub cmdPrintPreviewRPT_Click()
On Error GoTo Err_cmdPrintPreviewRPT_Click
Dim lngloop As Long
Dim strIDs As String
Dim myprojkeys As String



If Me.[COLOR="red"]lstProjects[/COLOR].ItemsSelected.Count <> 0 Then
        If Me.[COLOR="red"]lstProjects[/COLOR].ItemsSelected.Count > 0 Then
            For lngloop = 0 To Me.[COLOR="red"]lstProjects[/COLOR].ItemsSelected.Count - 1
            If lngloop = 0 Then
            strIDs = strIDs & Me.[COLOR="red"]lstProjects[/COLOR].ItemData(Me.[COLOR="red"]lstProjects[/COLOR].ItemsSelected(lngloop))
            Else
            strIDs = strIDs + "," & Me.[COLOR="red"]lstProjects[/COLOR].ItemData(Me.[COLOR="Red"]lstProjects[/COLOR].ItemsSelected(lngloop))
            End If
            Next lngloop
        End If
        myprojkeys = myprojkeys & "(" & strIDs & ")"
        strIDs = ""
Else
    MsgBox "You must select at least one project from the list"
    Me.[COLOR="red"]lstProjects[/COLOR].SetFocus
    Exit Sub
End If




    Dim stDocName As String

    stDocName = "[COLOR="red"]rptProjectList[/COLOR]"
    DoCmd.OpenReport stDocName, acPreview, , "[COLOR="red"]pkProjectID[/COLOR] in " & [COLOR="red"]myprojkeys[/COLOR]
    

Exit_cmdPrintPreviewRPT_Click:
    Exit Sub

Err_cmdPrintPreviewRPT_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrintPreviewRPT_Click
    
End Sub

I've highlighted what I think I need to replace with the proper names for my DB .. am I missing anything else? Thanks so much for the help.. I don't really know any programming (><)

 

[jzwp22]

You are correct with one exception.

The myprojkeys is a variable that I set up in the code, it contains the list of the key values relating to the selected project. You should not change it.


As to the other items in which you were correct:

lstProjects is the name of the list box control
rptProjectList is the name of the report
pkProjectID is the primary key field of the project table.

The key to having this code work is the bound column of the list box which is the primary key field in the project table (pkProjectID in my example). That field is numeric and unique. That field must also be included in the query or table that supplies the data to the report since the code uses it to do the filtering.

 

[kroenc17]

Thanks for your reply again.. I that one myprojkeys was a mistake, didn't mean to highlight it.

My ID field is also the first column in my table, so I'm guessing that my list box should likewise be bound to column 1. It's already a part of the query for the report.

For your list box, I noticed your row source includes a few things more than mine. Mine only shows the Project Name and Project ID fields - this it automatically does when I go through the steps to create a list box. Should I have more than just those two fields?

 

[jzwp22]

For your list box, I noticed your row source includes a few things more than mine. Mine only shows the Project Name and Project ID fields - this it automatically does when I go through the steps to create a list box. Should I have more than just those two fields?

That's your choice. You would include what you want your users to see in the list box. So if just having the two fields meets your needs that is fine. The code only looks at the project ID field. Just to verify, what is the datatype of your project ID field?

 

[kroenc17]

AutoNumber (Long Integer)

 

[jzwp22]

You should be OK with the autonumber field. Please let me know if you are able to get it to work in your application.

 

[kroenc17]

I thought I'd run into this is an issue... my ID fields name is "Project ID". There's a space (I know that's a no-no, but I'm not the original creator of this DB).

After I hit the report button I get the error:

Syntax error (missing operator) in query expression 'Project ID in (75)'.

I surrounded Project ID with ""s and then I tried ''s, no luck... am I SOL because of the name of the field?

 

[jzwp22]

You have to use square brackets: [Project ID]

 

[kroenc17]

It works PERFECTLY! Thank you so much for your help - LIFE SAVER!

 

[jzwp22]

You're welcome! Good luck with your project as you move forward.

 

[kroenc17]

So two little things... how do I change the code to have the form automatically close once you generate the report, and how do you generate the report in report view and not print preview? I'm having a go at it now trying to figure it out. Thanks so much again for your help!

 

[jzwp22]

In the DoCmd to open the report change acPreview to acNormal

From this:

DoCmd.OpenReport stDocName, acPreview, , "pkProjectID in " & myprojkeys

To this:

DoCmd.OpenReport stDocName, acNormal, , "pkProjectID in " & myprojkeys

It might be a good idea to notify the user that the report has been printed and then close the form. That code would look like this (I'll start at the DoCmd)

DoCmd.OpenReport stDocName, acNormal, , "pkProjectID in " & myprojkeys
msgbox "Your report has been printed"

DoCmd.Close acForm, "YourFormNameHere", acSaveNo

 

[kroenc17]

Thanks for that - Can I have the report open in 'Report View' instead of just go straight to print?

Form is closing now

Edit 2: AcViewReport ... got 'em. Thanks again for everything!

 

[jzwp22]

Sorry about that, I got my syntax wrong in the command; glad you got it worked out.

 

[kroenc17]

When I get the problem down to something that specific ie. one piece of code I should be able to figure it out - but starting from scratch? Couldn't have done it without your help, so thanks again!

 

[jzwp22]

You're welcome. BTW, I started out with this book Access VBA Programming For Dummies several years ago; it might be worth a look if you want to get more involved with coding.