Print selected records

[DDawson]

Dear Access Gurus

I have an inspections database and I want to print out the survey results
for the selected sites in a report.

I have no problems populating the fields in the userform and selecting the
fields. The problem occurs when I try to print. The report get the selected
records from a query and I am trying to output the selected records from the
form to the query.

Here is the printbutton on the userform

Private Sub btnPrintReport_Click()
Dim vItm As Variant
Dim strCriteria As String
Dim intView As Integer
Dim intWindowMode As Integer

strCriteria = "[Site] In ("
For Each vItm In Me!mslbxSites.ItemsSelected
strCriteria = strCriteria & "'" & _
Trim(Me!mslbxSites.ItemData(vItm)) & "', "
Next vItm
strCriteria = Left(strCriteria, Len(strCriteria) - 2) & ")"
'For CheckBox named ckPreview, where Default=True
If ckPreview Then
intView = acPreview
intWindowMode = acDialog
Else
intView = acViewNormal
intWindowMode = acWindowNormal
End If
DoCmd.OpenReport "rptSiteSurvey", intView, strCriteria, intWindowMode
End Sub

It appears to work fine, except, when it tries to open the report
rptSiteSurvey. A popup appears with the comment
"frmSiteSurveyRpt!mslbxSites". I have examined this and believe it comes down
to one line in the query. Here is the query:

SELECT SurveyData.[Date of Survey], SurveyData.Site, SurveyData.Element,
SurveyData.Condition, SurveyData.PriorityRef, SurveyData.[Revenue Cost],
SurveyData.[Revenue Comments], SurveyData.[Remaining Life],
SurveyData.[Capital Cost], SurveyData.[Capital Comments]
FROM SurveyData
WHERE (((SurveyData.Site)=[frmSiteSurveyRpt]![mslbxSites]))
ORDER BY SurveyData.[Date of Survey], SurveyData.Site, SurveyData.Element;

I believe it comes down to the WHERE (((SurveyData.Site)=... line, because,
when I changed it to ...[frmSiteSurveyRpt].[mslbxSites])) the popup comment
changed to this also. I'm sure this is something quite simple to fix, but I
can't put my finger on it.

DDawson

 

[Sam]

try: = forms![frmSiteSurveyRpt]![mslbxSites]
and make sure the form is open with a value in that field

Sam

 

[DDawson]

Hi Sam

I tried it, but it doesn't work. You could be on the right track though,
because the popup no longer appears. But, it still returns a blank report,
when I select several sites. Any other suggestions for me to try?

try: = forms![frmSiteSurveyRpt]![mslbxSites]
and make sure the form is open with a value in that field

Sam

Dear Access Gurus

I have an inspections database and I want to print out the survey results
for the selected sites in a report.

I have no problems populating the fields in the userform and selecting the
fields. The problem occurs when I try to print. The report get the selected
records from a query and I am trying to output the selected records from the
form to the query.

Here is the printbutton on the userform

Private Sub btnPrintReport_Click()
Dim vItm As Variant
Dim strCriteria As String
Dim intView As Integer
Dim intWindowMode As Integer

strCriteria = "[Site] In ("
For Each vItm In Me!mslbxSites.ItemsSelected
strCriteria = strCriteria & "'" & _
Trim(Me!mslbxSites.ItemData(vItm)) & "', "
Next vItm
strCriteria = Left(strCriteria, Len(strCriteria) - 2) & ")"
'For CheckBox named ckPreview, where Default=True
If ckPreview Then
intView = acPreview
intWindowMode = acDialog
Else
intView = acViewNormal
intWindowMode = acWindowNormal
End If
DoCmd.OpenReport "rptSiteSurvey", intView, strCriteria, intWindowMode
End Sub

It appears to work fine, except, when it tries to open the report
rptSiteSurvey. A popup appears with the comment
"frmSiteSurveyRpt!mslbxSites". I have examined this and believe it comes down
to one line in the query. Here is the query:

SELECT SurveyData.[Date of Survey], SurveyData.Site, SurveyData.Element,
SurveyData.Condition, SurveyData.PriorityRef, SurveyData.[Revenue Cost],
SurveyData.[Revenue Comments], SurveyData.[Remaining Life],
SurveyData.[Capital Cost], SurveyData.[Capital Comments]
FROM SurveyData
WHERE (((SurveyData.Site)=[frmSiteSurveyRpt]![mslbxSites]))
ORDER BY SurveyData.[Date of Survey], SurveyData.Site, SurveyData.Element;

I believe it comes down to the WHERE (((SurveyData.Site)=... line, because,
when I changed it to ...[frmSiteSurveyRpt].[mslbxSites])) the popup comment
changed to this also. I'm sure this is something quite simple to fix, but I
can't put my finger on it.

DDawson

 

[DDawson]

Can anyone else help with this?

I've tinkered with it and searched, but I can't get it to work.

The closest I have found is - Use Multiselect listbox to limit records in
report by Dev Ashish at http://www.mvps.org/access/reports/rpt0005.htm. I
tried to create a new form using this example, but the code gets stuck at Dim
loqd As QueryDef, how do I define the SQL string in Access 2000? Is there
another way to define the query from the form and then update my query? Or,
is there anything I can change in the original code to get it to work?

Dylan Dawson