Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2010
    Posts
    2

    Unanswered: Filtering lists by form

    I'm building a db for tracking support issues.

    The form has two lists which I want to update based on selections elsewhere in the form, and I want to open selected records in the form when selections in the lists are double clicked.

    The details:
    ID - Autonumber field used as support ID
    cboUID - User ID
    cboAssetID - Asset ID

    listAssetSupportHistory
    listUserSupportHistory

    The lists have the following SQL code to pull their contents (this is for listAssetSupportHistory, listUserSupportHistory pulls the field tblSupport.AssetID and uses tblSupport.UID as the selection criteria):

    Code:
    SELECT tblSupport.ID, tblSupport.UID, tblSupport.Date, Left([tblSupport.Problem],50) AS Problem
    FROM tblSupport
    WHERE (((tblSupport.AssetID)=Forms!formSupport!cboAssetID))
    ORDER BY tblSupport.ID;
    Problem is a memo field in tblSupport and so I'm converting to a text field for use in the lists.

    The VBA code for the form:

    Code:
    Private Sub ID_Change()
    On Error GoTo Err_Update_Click
    
        [listUserSupportHistory].Requery
        [listAssetSupportHistory].Requery
        
    Exit_Update_Click:
        Exit Sub
    
    Err_Update_Click:
        MsgBox Err.Description
        Resume Exit_Update_Click
    End Sub
    
    Private Sub cboUID_Dirty(Cancel As Integer)
    'On dirty requery User Support History
    On Error GoTo Err_Update_Click
    
        listUserSupportHistory.Requery
        
    Exit_Update_Click:
        Exit Sub
    
    Err_Update_Click:
        MsgBox Err.Description
        Resume Exit_Update_Click
    End Sub
    
    Private Sub cboAsset_Dirty(Cancel As Integer)
    'On dirty requery Asset Support History
        
    On Error GoTo Err_Update_Click
    
        [listAssetSupportHistory].Requery
        
    Exit_Update_Click:
        Exit Sub
    
    Err_Update_Click:
        MsgBox Err.Description
        Resume Exit_Update_Click
        
    End Sub
    The goal:
    As I'm scrolling through the records in the form, I want the two lists to update with all the records from tblSupport that correspond to cboUID or cboAssetID.

    Also, when creating a new record, selecting the affected user and/or asset will show the support history for each.

    Neither of these fields are required in the database as there are issues that correspond to users and not to assets and vice versa.

    When a record is open in the form, opening the queries results in a correct selection. However, the form itself doesn't work.

    I'm not getting any error messages when I scroll through the form or when I select new entries in the cboUID or cboAssetID. But I'm pretty sure the issue is in the VBA code.

  2. #2
    Join Date
    Jun 2010
    Posts
    2
    I've found a partial solution. Now when I make a selection in either the cboUID or cboAssetID, the lists update appropriately. And selecting an entry in the list brings up the appropriate record in the form.

    The SQL queries have not changed.

    Here's the new VBA code:

    Code:
    Private Sub cboUID_Change()
    'requery User Support History list
    On Error GoTo Err_Update_Click
    
        Me![listUserSupportHistory].Requery
        
    Exit_Update_Click:
        Exit Sub
    
    Err_Update_Click:
        MsgBox Err.Description
        Resume Exit_Update_Click
    End Sub
    
    Private Sub cboAsset_Change()
    'requery Asset Support History list
    On Error GoTo Err_Update_Click
    
        Me![listAssetSupportHistory].Requery
        
    Exit_Update_Click:
        Exit Sub
    
    Err_Update_Click:
        MsgBox Err.Description
        Resume Exit_Update_Click
        
    End Sub
    
    Private Sub ID_Change()
    On Error GoTo Err_Update_Click
    
        Me![listUserSupportHistory].Requery
        Me![listAssetSupportHistory].Requery
        
    Exit_Update_Click:
        Exit Sub
    
    Err_Update_Click:
        MsgBox Err.Description
        Resume Exit_Update_Click
    End Sub
    
    Private Sub listUserSupportHistory_AfterUpdate()
        ' Find the record that matches the control.
        Dim rs As Object
    
        Set rs = Me.Recordset.Clone
        rs.FindFirst "[ID] = " & Me!listUserSupportHistory
        If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End Sub
    
    Private Sub listAssetSupportHistory_AfterUpdate()
        'Set SoughtString with
        Dim rs As Object
    
        Set rs = Me.Recordset.Clone
        rs.FindFirst "[ID] = " & Me!listAssetSupportHistory
        If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End Sub
    However, navigation of the records in the form does not update either of the lists. I assume this means Private Sub ID_Change() isn't the correct trigger. This isn't a complete necessity as in operation the form will most frequently open with a new blank entry and most searching of the records will be based on the selections I've made while creating a new entry. But I want to know how to make it work this way to make it as user friendly as possible as well as to further my own knowledge of VBA.

    Also, because I don't want to leave the initial support record accidentally, I'd like to set the trigger (criteria) for opening the record selected in either list to a doubleclick and/or enter key press.

    I'd been trying to use DoubleClick as the event action trigger, but that had not been working until I changed it to Private Sub listAssetSupportHistory_AfterUpdate().

    Is there a way to set it to the DoubleClick and Enter events to trigger the record search?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •