Multi-Select List box based on Two Parameters

[Stacey Crowhurst]

Hi. I've had success with multi-select list boxes using this MVPs code:
http://allenbrowne.com/ser-50.html. However, now I need some more help!

I have a report that shows costs by budget code [bcBudgetCodeID] and
[bcBudgetCodeDesc] (ex: furniture, construction, design, etc.) I want the
user to be able to select desired budget codes for a specific project
[budCCPID] to get the report to pop up and show the detailed costs. Right
now, when the user clicks for the report, the report includes budget code
detail on all projects.

Here is my list box sql: SELECT tblBudgets.budCCPID,
tblBudgetCodes.bcBudgetCodeID, tblBudgetCodes.bcBudgetCodeDesc FROM
(tblBudgets INNER JOIN tblBudgetCodes ON
tblBudgets.budBudgetCodeID=tblBudgetCodes.bcBudgetCodeID) INNER JOIN
tblProjects ON tblBudgets.budCCPID=tblProjects.prjCCPID WHERE
(((tblProjects.prjStatus)<="4")) GROUP BY tblBudgets.budCCPID,
tblBudgetCodes.bcBudgetCodeID, tblBudgetCodes.bcBudgetCodeDesc ORDER BY
tblBudgets.budCCPID, tblBudgetCodes.bcBudgetCodeID;

*the "4" part just tell is only active projects

Then the code for the command to open the report is here:
Private Sub cmdViewBudgetLinesbyCostCode_Click()
On Error GoTo Err_Handler

Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String

strDelim = """"
strDoc = "rptBudgetLinesbyCostCode"


With Me.listBudgetLines
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[bcBudgetCodeID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Budget Line(s): " & " " & Left$(strDescrip, lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler

End Sub

So I think I need to input in the code somewhere to also select [budCCPID]
project number, but I am not sure how to do that.

I don't think I did a very good job at explaining this problem, so let me
know if you need more information. Also, I may be going about this the wrong
way. There may be a simpler way to have the user select project number and
budget codes to filter the report on.

As always, I'm extra grateful for your help!!! Without the MVPs my database
would be in sad shape

Thanks,
Stacey

 

[MGFoster]

Hi. I've had success with multi-select list boxes using this MVPs code:
http://allenbrowne.com/ser-50.html. However, now I need some more help!

I have a report that shows costs by budget code [bcBudgetCodeID] and
[bcBudgetCodeDesc] (ex: furniture, construction, design, etc.) I want the
user to be able to select desired budget codes for a specific project
[budCCPID] to get the report to pop up and show the detailed costs. Right
now, when the user clicks for the report, the report includes budget code
detail on all projects.

Here is my list box sql: SELECT tblBudgets.budCCPID,
tblBudgetCodes.bcBudgetCodeID, tblBudgetCodes.bcBudgetCodeDesc FROM
(tblBudgets INNER JOIN tblBudgetCodes ON
tblBudgets.budBudgetCodeID=tblBudgetCodes.bcBudgetCodeID) INNER JOIN
tblProjects ON tblBudgets.budCCPID=tblProjects.prjCCPID WHERE
(((tblProjects.prjStatus)<="4")) GROUP BY tblBudgets.budCCPID,
tblBudgetCodes.bcBudgetCodeID, tblBudgetCodes.bcBudgetCodeDesc ORDER BY
tblBudgets.budCCPID, tblBudgetCodes.bcBudgetCodeID;

*the "4" part just tell is only active projects

Then the code for the command to open the report is here:
Private Sub cmdViewBudgetLinesbyCostCode_Click()
On Error GoTo Err_Handler

Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String

strDelim = """"
strDoc = "rptBudgetLinesbyCostCode"


With Me.listBudgetLines
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[bcBudgetCodeID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Budget Line(s): " & " " & Left$(strDescrip, lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler

End Sub

So I think I need to input in the code somewhere to also select [budCCPID]
project number, but I am not sure how to do that.

I don't think I did a very good job at explaining this problem, so let me
know if you need more information. Also, I may be going about this the wrong
way. There may be a simpler way to have the user select project number and
budget codes to filter the report on.

As always, I'm extra grateful for your help!!! Without the MVPs my database
would be in sad shape

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your code looks fine. I don't understand why you're sending the comma
delimited string of descriptions as an OpenArg. Does the report do
something w/ those values?

Some debug suggestions:

1. Make sure the report's RecordSource query has the column name you are
using in the WhereCondition parameter.

2. Make sure the ListBox's Bound Column is on the correct column (column
numbers start at one).

3. Put a Breakpoint at the line

lngLen = Len(strWhere) - 1

and see if the strWhere has the correct syntax.

4. Put a Breakpoint at the Report's Open event and see if everything
goes as you planned, re: the OpenArgs value.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSe/w14echKqOuFEgEQJvxACgxqDD2265+V5agyZ5AzXz3DXcVZ8AoPIc
IwS9MjsCmN9s8oIh9Fb58Xjm
=oq34
-----END PGP SIGNATURE-----