By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,310 Members | 792 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,310 IT Pros & Developers. It's quick & easy.

Select blank line in multiselect list box and retrieve related data

P: 31
Hi All,

I currently have a form with a multiselect list box that shows Vehicle IDs (Column 0) and Vehicle Statuses (Column 1) by customer. The VehicleID column is the bound column. Occasionally the VehicleID is blank for the customer, but there is associated data with it that can be retrieved (the Vehicle Status is not blank). However, when I select the blank line in the list box I can not populate the query/report related to that selection.

My question is: How can I retrieve the related data in the query/report when I select a blank line? Can I replace the blank in the comma delimited list with Is Null?

The following code makes the selected Vehicle IDs into a comma delimited list for my query criteria:

Expand|Select|Wrap|Line Numbers
  1. On Error GoTo Err_Handler
  2.     'Purpose:  Open the report filtered to the items selected in the list box.
  3.     'Author:   Allen J Browne, 2004.   http://allenbrowne.com
  4.     Dim varItem As Variant      'Selected items
  5.     Dim strWhere As String      'String to use as WhereCondition
  6.     Dim strDescrip As String    'Description of WhereCondition
  7.     Dim lngLen As Long          'Length of string
  8.     Dim strDelim As String      'Delimiter for this field type.
  9.     Dim strDoc As String        'Name of report to open.
  10.  
  11.     strDelim = """"            'Delimiter appropriate to field type. See note 1."
  12.     strDoc = "rptAllCust_Customer"
  13.  
  14.     'Loop through the ItemsSelected in the list box.
  15.     With Me.VehID
  16.         For Each varItem In .ItemsSelected
  17.             If Not IsNull(varItem) Then
  18.                 'Build up the filter from the bound column (hidden).
  19.                 strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
  20.                 'Build up the description from the text in the visible column. See note 2.
  21.                 strDescrip = strDescrip & """" & .Column(0, varItem) & """, "
  22.             End If
  23.         Next
  24.     End With
  25.  
  26.     'Remove trailing comma. Add field name, IN operator, and brackets.
  27.     lngLen = Len(strWhere) - 1
  28.     If lngLen > 0 Then
  29.         strWhere = "[VEHICLE_ID] IN (" & Left$(strWhere, lngLen) & ")"
  30.         lngLen = Len(strDescrip) - 2
  31.         If lngLen > 0 Then
  32.             strDescrip = "Categories: " & Left$(strDescrip, lngLen)
  33.         End If
  34.     End If
  35.  
  36.     'Report will not filter if open, so close it. For Access 97, see note 3.
  37.     If CurrentProject.AllReports(strDoc).IsLoaded Then
  38.         DoCmd.Close acReport, strDoc
  39.     End If
  40.  
  41.     'Omit the last argument for Access 2000 and earlier. See note 4.
  42.     DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip
  43.  
  44. Exit_Handler:
  45.     Exit Sub
  46.  
  47. Err_Handler:
  48.     If Err.Number <> 2501 Then  'Ignore "Report cancelled" error.
  49.         MsgBox "Error " & Err.Number & " - " & Err.Description, , "PrevRelease_Click"
  50.     End If
  51.     Resume Exit_Handler
  52.  

Any help would be great. Thanks.
May 13 '11 #1

✓ answered by ADezii

The addition of Code Line #5 to the strWhere Clause should do the trick:
Expand|Select|Wrap|Line Numbers
  1. 'Remove trailing comma. Add field name, IN operator, and brackets.
  2.     lngLen = Len(strWhere) - 1
  3.     If lngLen > 0 Then
  4.       strWhere = "[VEHICLE_ID] IN (" & Left$(strWhere, lngLen) & ")"
  5.       strWhere = strWhere & " OR ([VEHICLE_ID] Is Null AND [Vehicle Status] Is Not Null);"
  6.  
  7.       lngLen = Len(strDescrip) - 2
  8.       If lngLen > 0 Then
  9.         strDescrip = "Categories: " & Left$(strDescrip, lngLen)
  10.       End If
  11.     End If

Share this Question
Share on Google+
3 Replies

ADezii
Expert 5K+
P: 8,740
The addition of Code Line #5 to the strWhere Clause should do the trick:
Expand|Select|Wrap|Line Numbers
  1. 'Remove trailing comma. Add field name, IN operator, and brackets.
  2.     lngLen = Len(strWhere) - 1
  3.     If lngLen > 0 Then
  4.       strWhere = "[VEHICLE_ID] IN (" & Left$(strWhere, lngLen) & ")"
  5.       strWhere = strWhere & " OR ([VEHICLE_ID] Is Null AND [Vehicle Status] Is Not Null);"
  6.  
  7.       lngLen = Len(strDescrip) - 2
  8.       If lngLen > 0 Then
  9.         strDescrip = "Categories: " & Left$(strDescrip, lngLen)
  10.       End If
  11.     End If
May 13 '11 #2

P: 31
Thanks ADezii. You are a lifesaver. I just tested it out and it works perfectly.
May 16 '11 #3

ADezii
Expert 5K+
P: 8,740
You are quite welcome, glad it worked out for you.
May 16 '11 #4

Post your reply

Sign in to post your reply or Sign up for a free account.