Report Header based on Items Selected on Multi-Select

  • Thread starter Thread starter LFriend via AccessMonster.com
  • Start date Start date
L

LFriend via AccessMonster.com

I'm trying to create a text box on a report header based on the items
selected on the multi-select box with the values listed one behind the
other (i.e., 440203, 440209, etc.) The multi-select box name is TempRCN.
The values are obtained from qryforMultiSelect.RCN. Any suggestions or
ideas will be greatly appreciated!!

This is the line of code set for the Multi-Select:
If Me.ReportType2.Value = "rptRCNSummary" Then
' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryMultiSelect")
'Loop through the selected items in the list box and build a text string
For Each varItem In Me!TempRCN.ItemsSelected
StrCriteria = StrCriteria & ", '" & Me!TempRCN.ItemData(varItem) &
"'"
Next varItem
'If user leaves BL block blank
If Len(StrCriteria) = 0 Then
StrCriteria = "qryforMultiSelect.RCN Like '*'"
strSQL = "SELECT * FROM qryforMultiSelect " & _
"WHERE qryforMultiSelect.FY = [Forms]![frmRCNReport]![ReportFY]
" & _
"AND qryforMultiSelect.CATEGORY=[Forms]![frmRCNReport]!
[ReportCat] " & _
"AND " & StrCriteria & ";"
Else
'If user chooses one or more BLs
StrCriteria = Right(StrCriteria, Len(StrCriteria) - 1)
strSQL = "SELECT * FROM qryforMultiSelect " & _
"WHERE qryforMultiSelect.FY = [Forms]![frmRCNReport]![ReportFY]
" & _
"AND qryforMultiSelect.RCN IN(" & StrCriteria & ");"
End If
'Apply the new SQL statement to the query
qdf.SQL = strSQL
'Open the query
DoCmd.OpenQuery "qryMultiSelect"
'Empty the memory
Set db = Nothing
Set qdf = Nothing

DoCmd.Close acQuery, "qryMultiSelect"
End If
 
It isn't clear to me, on brief examination, what your code is attempting to
do... it appears you are opening a query for the user and immediately
closing it -- as there is nothing between the DoCmd.OpenQuery and the
DoCmd.Close that will even slow down execution, much less halt it for long
enough for the user to do anything.

And, that does not seem to relate to what you describe in your question --
setting a Text Box in the Header of a Report. It appears that you may be
setting strCriteria (which is the default name for the WhereCondition
argument in DoCmd.OpenReport as generated by some Wizards, but it wouldn't
necessarily be used for that purpose). If so, this would not set a Text Box,
but would determine the records selected for reporting.

If you could clarify so that we don't have to try to figure out what you
perceive this code should be accomplishing, perhaps we could be helpful.

Larry Linson
Microsoft Access MVP
 
The qryMultiSelect has the keeps the values selected stored in the RCN
field (text field). Want to know if there's anyway possible to either
concatenate the values which are stored in the query or if there's a way to
be able to retrieve the values based on the items selected in the multi-
select box called TempRCN under the [Forms]![frmRCNReport]![TempRCN]. The
report appears with the values after the query has been executed and it
closes the query so that it doesn't appear as a separate screen for the
user to see.
 
Back
Top