Report based on multi select

[captgnvr]

Dear All

I have list of names and their bio data.

I am able to get the information for the multi selected persons.

What I need is - steps for using multi select, to print out individual bio

data of the persons selected for ex. say selected three persons; and print outs of each name selected bio data .

Pls help. Will be watching for guidance.

 

[pbaldy]

Here's a way to filter a report using a multiselect listbox:

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

 

[boblarson]

And please don't post the same question twice (one in different categories).

The other has been deleted.

 

[captgnvr]

Good day mr Baldy
thnks for the quick response. I went thro the link you have given me but unable to figure out. Actually from the list of names, say about three names are selected. How to make a report to print out each one of the selected names information. Can you give me the vb code for it? I got the code running till the query opens with the selected names and information. But unable to proceed to the next step of printing their bio data sheets. pls help.

 

[pbaldy]

Are you saying you want 3 separate reports printed out? You can set the report up to break each person out onto a separate page and use that technique, or use this one within the loop in the first link:

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

 

[captgnvr]

Yes. If 10 crew are disembarking, I should select 10 names at a stretch but have each ones particulars printed in in a separate full page designed with company logo etc. By the by, our company still uses only access97. Thanks to your quick response, it is really so encouraging to learn. Thnks

 

[pbaldy]

Did you get it sorted out? Looks like you'll want to combine those two methods. The loop through the selected items, then OpenReport using the current selected item to print the report.

 

[captgnvr]

Sorry Mr. Paul, went off for the night and during the day no time to try out. I will sit up now and figure out and inform you.

 

[pbaldy]

No problem, I just wanted to make sure you weren't waiting on me.

 

[captgnvr]

D/Mr. Paul

Sorry. I tried but not making headway due to lack of VB knowledge.

I have put the code here """
For Each varItem In Me!F_MULTI.ItemsSelected
strSQL = "SELECT * FROM TBL_CREW " & _
"WHERE TBL_CREW.NAME IN(" & strCriteria & ");"
qdf.SQL = strSQL
DoCmd.OpenQuery "Q_MULTISELECT"
DoCmd.OpenReport "R_SIGNOFFPRINT", acViewNormal, "Q_MULTI"


The difficulty I am facing is to take the print out for the first person and then resume or the program to contnue. Is the code insertion at the right place?? Pls help

Short info about me-- I am on the ship and have cargo of crude transfers to other ships and so have this time only to try out. Would appreciate like yesterday, if you dont mind pls.

 

[pbaldy]

Well, I would do more like this (as noted on the link, the report would be set up to return all records if opened directly):

For Each varItem In Me!F_MULTI.ItemsSelected

  DoCmd.OpenReport "R_SIGNOFFPRINT", , , "[Name] = '" & Me!F_MULTI.ItemData(varItem) & "'"

Next varItem

 

[captgnvr]

D/Paul

I entered this code:

DoCmd.OpenReport "R_SIGNOFF PRINT", , , "[NAME] = '" & Me!LBOX.ItemData(varItem) & "'"

report name: R_SIGNOFF PRINT

The query for this report name is "Q_MULTISELECT" using table T_CREW

listbox name : LBOX

required field : [NAME] This field in T_CREW

So when I run this code, all the data in the query is deleted; and get an error saying that


"syntax error (missing operator) in query expression 'T_CREW.NAME IN ()'

What must I do?


Feeling bad with my little knowledge but willing to learn. Pls help.

 

[pbaldy]

The query should return all records, and should not refer to the listbox. Probably in testing your previous code, it got saved with a blank criteria. Open the query in design view, take out that criteria, and save it again. This line should handle restricting the report to a single person:

DoCmd.OpenReport "R_SIGNOFF PRINT", , , "[NAME] = '" & Me!LBOX.ItemData(varItem) & "'"

 

[captgnvr]

D/Mr. Paul

BINGO. Yes after I opened the query and put all the fields and then ran the code and worked like a charm. Thank you very much. Tempted to move on to next requirement after I spruce up this.

 

[pbaldy]

Happy to help. Good sailing!

 

[HGMonaro]

For future reference, another way of achieving this is to have a table of valid selections (either created with a query or maintained seperately) with a Yes/No field that you tick on/off to select the selections you want (displayed using a form if you want it to look nice). Run the report (with page breaks as described above) based on a query that selects the records with the selection field set to YES.

 

[captgnvr]

D/Mr.Hgmonaro
Almost missed ur sugestion since I have got the desired vb code. However, thnk u for the sugestion and since I needed a multi select and take a separate print out - it settled fine. But will try yours also when time permits and get back.

 

[captgnvr]

D/Mr/ Paul

Private Sub B_PRINT_Click()

Below is the code. When I tested it seemed to work. Now suddenly it is giving printouts without the names selected. Pls help me. No clue to check how it is happening. When I debug the selections seems to be ok. I feel that when docmnd report something is happening which may not be correct. pls see the code below:

' Declare variables
Dim PYES As Variant
Dim db As DAO.Database
Dim qdf As QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("Q_MULTISELECT")


PYES = InputBox("ENTER DATE")
Forms![F_MULTI]![INDATE] = PYES

' Loop through the selected items in the list box and build a text string
For Each varItem In Me!LBOX.ItemsSelected

DoCmd.OpenReport "R_SIGNOFF PRINT", , , "[NAME] = '" & Me!LBOX.ItemData(varItem) & "'"

DoCmd.OpenReport "R_SIGNOFF LIST OF BAGS", , , "[NAME] = '" & Me!LBOX.ItemData(varItem) & "'"

Next varItem

' Empty the memory
Set db = Nothing
Set qdf = Nothing

With Me!LBOX

For Each varItem In .ItemsSelected
.Selected(varItem) = False
Next varItem

End With



End Sub

 

[captgnvr]

d/mr paul

I figured out why it was happening. I also use the same query to see particulars of the multi selected crew. This one was changing the criteria in the query. Whereas when printing the multi select, is going direct to the report and no filter and hence blank report i guess.
Now I changed the query to see multiple crew particulars by copying the same query and gave a new name. If better way to do, pls do guide.