Create a report from whats been selected in a list box

[Rach!]

Hello everyone,

I'm a bit stuck with creating a report. I only want to show the results from whats selected from the main search and the comparison from the form listbox (frmAdvancedSearch). I've attached my database if anyone can help.

I have 2 searches on one form so that the user can compare battery cell results, i need to be able to display it in this way for the user., so i cant have the main and comparison on two different forms....Hope im explaining myself ok when you see the database.

 

[pbaldy]

I'm on an iPad right now so can't look at the sample, but does this help?

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

 

[Rach!]

Thanks,

Will take a look!

 

[pbaldy]

No problem, post back if you get stuck.

 

[Rach!]

Brilliant!! Thanks
I've got one of the query searches working on the report...fingers crossed for the second!!

 

[pbaldy]

Excellent! Glad it helped.

 

[Rach!]

Right,

Got the first search to work brilliantly, but bit at a loss how I can get the second (Comparison) one working on the same report as they run from the same tables? (but different queries) do you think i should have set it up differently from the queries?

I've attached an updated database

Rach

 

[pbaldy]

I'm not clear on the goal. Are you saying you're trying to use the same report for the two different queries? To do that, you'd have to change the record source of the report in its open event. In that event, I'd also do the filtering in the open event, similar to this:

http://www.granite.ab.ca/access/email/reporttomultiplerecipients.htm

But in your case I'd pass the string in OpenArgs so the report would get it from there instead of a form. That would look like:

Me.Filter = Me.OpenArgs

 

[Rach!]

Hi,

I've managed to get the report to show the two results from the form, which is just brilliant exactly what i needed. I have put the code in that requests to show only the record selected from Main and From Compare but i can seem to get the Compare to stop showing all records the code is:


Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click

Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant

'make sure a selection has been made
If Me.SearchList.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 Unit"
Exit Sub
End If

'add selected values to string
Set ctl = Me.SearchList
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ","
Next varItem
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)
'open the report, restricted to the selected items
DoCmd.OpenReport "rptCompareSearch", acPreview, , "UnitDetailsID IN(" & strWhere & ")"

Exit_cmdOpenReport_Click:
Exit Sub

Err_cmdOpenReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReport_Click

End Sub

Any idea where i can tell it to include the subreport to only show the selected record from the report?

Rach

 

[pbaldy]

Hard to test since there's only 1 record. It appears to be coded correctly, but I don't know what I'm supposed to select or see as a result.

 

[pbaldy]

Oh, and generally a subreport is kept in sync with the main report with the master/child links. If those aren't applicable to your situation, you'll need to base the subreport on a query that is filtered appropriately.