Results 1 to 4 of 4
  1. #1
    Jackfam58 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Apr 2011
    Posts
    7

    Multiselect to pull report from Access Form

    I have two fields called ErrorCodeDescription and ErrorCodeCorrections. I created an access form with the two fields. I changed the types to listbox and used multiselect of simple. I created a module with the following code (note this code only addresses the first field because I am not sure as to how to add ErrorCodeCorrections to the code. The name of the query is qry_tracking. The name of the report is TrackingReport. The objective is to be able to highlight multiple field contents in both ErrorCodeDescriptions and ErrorCodeCorrections, then click a button and show only those selected and display the in a report
    Private Sub cmdPreview_Click()
    On Error GoTo Err_Handler

    Dim varItem As Variant 'Selected items
    Dim strWhere As String 'String to use as WhereCondition
    Dim strDescrip As String 'Description of WhereCondition
    Dim lngLen As Long 'Length of string
    Dim strDelim As String 'Delimiter for this field type.
    Dim strDoc As String 'Name of report to open.


    strDoc = "TrackingReport" 'The name of the actual report

    With Me.ErrorCodeDescription
    For Each varItem In .ItemsSelected
    If Not IsNull(varItem) Then
    'Build up the filter from the bound column (hidden).
    strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
    'Build up the description from the text in the visible column. See note 2.
    strDescrip = strDescrip & """" & .Column(1, varItem) & """, "


    End If
    Next
    End With

    'Remove trailing comma. Add field name, IN operator, and brackets.
    lngLen = Len(strWhere) - 1
    If lngLen > 0 Then
    strWhere = "[ErrorCodeDescription] IN (" & Left$(strWhere, lngLen) & ")"
    lngLen = Len(strDescrip) - 2
    If lngLen > 0 Then
    strDescrip = "ErrorCodeDescription: " & Left$(strDescrip, lngLen)
    End If
    End If

    'Report will not filter if open, so close it. For Access 97, see note 3.
    If CurrentProject.AllReports(strDoc).IsLoaded Then
    DoCmd.Close acReport, strDoc
    End If

    'Omit the last argument for Access 2000 and earlier. See note 4.
    DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip
    Exit_Handler:
    Exit Sub
    I created a command button called cmdPreview and attached the code. When I run it I get the message Invalid Use of Me keyword

    Questions
    1. Why am I getting Invalid Use of Me keyword?
    2. How can I modify the code to address bothe fields I mentioned
    3. How can I get this to report only what I have highlighted in the two fields using multiselect.

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Questions
    1. Why am I getting Invalid Use of Me keyword?
    2. How can I modify the code to address bothe fields I mentioned
    3. How can I get this to report only what I have highlighted in the two fields using multiselect.
    1) The Me keyword can not be used in a standard module, only in a form or report module. "Me" is a "shourtcut" for "[Forms]![FormName]". Since the standard module is not related to a form/report, it doesn't know what the form name is.

    2) The easiest way is to move the code to a form/report module (code behind form)

    3) Need a clearer explanation.....
    Do you have two list boxes on the form?
    What is the row source for each of the list boxes?
    Maybe examples of the data in each of the list boxes.....

  3. #3
    Jackfam58 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Apr 2011
    Posts
    7
    Listboxes

    ErrorCodeDescription: row source is SELECT [Error_Codes_and_Corrections]![Error Codes and Corrections] & "-" & [Error_Codes_and_Corrections]![Error Code Description] AS [Error Code Description] FROM Error_Codes_and_Corrections ORDER BY [Error_Codes_and_Corrections]![Error Codes and Corrections] & "-" & [Error_Codes_and_Corrections]![Error Code Description];

    ErrorCodeCorrection: row source is SELECT [Error_Codes_and_Corrections]![Error Codes and Corrections] & "-" & [Error_Codes_and_Corrections]![Corrections] AS Corrections FROM Error_Codes_and_Corrections;

    Example
    ErrorCodeDescription
    1008-Invalid account number
    1015-Invalid date

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Still trying to understand...

    The first problem I found is a circular reference in the query for the list box "ErrorCodeDescription" See the RED text below. The alias must not be the same as another access object.
    Code:
    ErrorCodeDescription:  row source is SELECT   [Error_Codes_and_Corrections]![Error Codes and Corrections] & "-"   & [Error_Codes_and_Corrections]![Error Code Description] AS [Error  Code Description] 
    FROM Error_Codes_and_Corrections 
    ORDER BY  [Error_Codes_and_Corrections]![Error Codes and Corrections]  & "-"  & [Error_Codes_and_Corrections]![Error Code Description];
    Both list boxes are based on the same table??


    Problem 2 -
    Code:
    On Error GoTo Err_Handler
    there is not an error handler in the code



    The result of your code for strWhere is "[ErrorCodeDescription] IN (1008-Invalid account number,1015-Invalid date)"

    In the query for the report, is there a field named "[ErrorCodeDescription]" that has data like "1008-Invalid account number"???



    By chance do you have a field in a table defined as a "List box"??? (I hope not) I'm asking because you said "I have two fields called ErrorCodeDescription and ErrorCodeCorrections. I created an access form with the two fields. I changed the types to listbox". Maybe it is just termonology.....Forms have controls, tables have fields.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 03-21-2012, 10:36 AM
  2. Need single report to pull from 2 queries
    By Nathan Plemons in forum Access
    Replies: 2
    Last Post: 03-14-2012, 11:04 AM
  3. Replies: 1
    Last Post: 03-07-2011, 10:48 AM
  4. Replies: 1
    Last Post: 03-23-2010, 09:18 AM
  5. Pull Parameter Value from Form
    By dymondjack in forum Queries
    Replies: 1
    Last Post: 02-14-2007, 07:15 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums