Results 1 to 15 of 15
  1. #1
    ironfelix717 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    150

    Listbox Selection Problem - Selected Value does not trigger code

    Hi,

    Tonight I am dealing with a frustrating issue i've managed for some months now but in the current context, it has become an unavoidable issue I cannot resolve.

    Its not entirely worth explaining without asking to please download the attached simulation of my exact problem.



    ListBoxSelectedTest1.zip


    The goal:

    Tab2 is selected - The listTables listbox has first item "Manufacturer" selected and populates listData with correct information

    The Problem:

    Tab 2 is selected - The listTables listbox has first item "Manufacturer" selected - Does not populate listData with correct information.

    Note:

    Selecting Page1 of the tab control de-selects the listTables listbox on purpose as it reflects my legitimate problem.

    Notice you have to manually click the listbox for it to populate once Tab2 is refreshed.

    All functionality is handled in code.



    I am wondering if anyone has any ideas. Thanks




    Regards

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    If you had stepped through your code it would have become blindingly obvious what your problem is.
    TabControl values are zero based
    Once you fix that it will work

    NOTE:
    a) You do not need to repeat the line listTable.Selected(0) = True
    b) Select Case statement should not end in a colon :
    c) You don't need to use .Value after TabControl as that is the default property
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    this can also be done without code. (well minimal)
    Instead of making case statements for the listbox, This listbox can have 2 columns:
    col1, Label for user to select MANUF,etc,
    col2, (hidden) for the query.

    then the code is :
    Code:
    sub listTable_Afterupdate()
       lstData.Rowsource = lstTable.col(1)   'in vb, columns start at zero.
    end sub




  4. #4
    ironfelix717 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    150
    ranman246:

    Interesting approach.


    Isladogs:

    Ahh yes, the darn 0 based. Forgot... Regardless, has no affect on the issue... which it wouldn't. I welcome you to test that.



    Regards

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by ironfelix717 View Post
    Isladogs:

    Ahh yes, the darn 0 based. Forgot... Regardless, has no affect on the issue... which it wouldn't. I welcome you to test that.
    It is the cause of your issue. I'd already tested it when I changed the tab value to zero in your code
    As already stated, it then works
    Perhaps you didn't test it before replying?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    ironfelix717 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    150
    Hello,

    If you re-read my original post it would become blindingly obvious why your solution has little to no effect on the problem at hand.

    [.gif]
    Click image for larger version. 

Name:	animate5.gif 
Views:	16 
Size:	142.4 KB 
ID:	37936



    CODE:
    Code:
    Private Sub tabControl_Change()
        If tabControl.Value = 0 Then 'page 1
            'Clear listTable listbox selections
            Dim varItem As Variant
            If listTable.MultiSelect = 0 Then
                listTable = Null
            Else
                For Each varItem In listTable.ItemsSelected
                    listTable.Selected(varItem) = False
                Next
            End If
            
            listdata.RowSource = ""
        
        Else  'page 2
            listTable.Selected(0) = True 'select first item
            Call listTable_Click 'trigger code
    
    
        End If
    End Sub
    
    
    Private Sub listTable_Click()
        Select Case listTable.Value
            Case "Manufacturer":
                listdata.RowSourceType = "Table/Query"
                listdata.RowSource = "SELECT * From Table1;"
                listdata.Requery
    
    
            Case "Type":
                listdata.RowSourceType = "Table/Query"
                listdata.RowSource = "SELECT * From Table2;"
                listdata.Requery
        End Select
    End Sub

  7. #7
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Maybe this:
    Code:
    
        If tabControl.Value = 0 Then 'page 1
        
            'Clear listTable listbox selections
            Dim varItem As Variant
            If listTable.MultiSelect = 0 Then
                listTable = Null
            Else
                For Each varItem In listTable.ItemsSelected
                    listTable.Selected(varItem) = False
                Next
            End If
            
            listdata.RowSource = ""
        
        Else  'page 2
            'listTable.Selected(0) = True 'select first item
            Call listTable_Click 'trigger code
            listTable.Selected(0) = True ' Must select first item again because code de-selects first item
        End If
        
        
    End Sub
    
    Private Sub listTable_Click()
        Select Case listTable.Column(0, 1)
            Case "Manufacturer":
                listdata.RowSourceType = "Table/Query"
                listdata.RowSource = "SELECT * From Table1;"
                listdata.Requery
            Case "Type":
                listdata.RowSourceType = "Table/Query"
                listdata.RowSource = "SELECT * From Table2;"
                listdata.Requery
        End Select
    End Sub

  8. #8
    ironfelix717 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    150
    davegri,

    I tested that with no luck. Although unsure what you are highlighting in red?

  9. #9
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Quote Originally Posted by ironfelix717 View Post
    davegri,

    I tested that with no luck. Although unsure what you are highlighting in red?
    Do you mean you are unable to see any red?
    The red highlights the changes to the code.

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I can't blame you for throwing my 'blindingly obvious' comment back at me.
    However, you seem to believe wrongly that I didn't understand what you were describing.
    I'll say once more that I tested it worked several times before my first reply and again before my second reply.
    Your responses are such that I'm not prepared to spend more time on this.

    Two comments that may possibly help
    1. Part of the code is unnecessary. It runs twice.
    2. Try slowing the code down slightly

    I'll leave you in davegri's capable hands
    Good luck solving your problem and with the rest of your project
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    I assume you want this:
    Whenever Type or Manufacturer is clicked on the left listbox, the right listbox should show the contents of table2 or table1 respectively.
    Whenever switching from page1 to page2 you want to see the contents of table1 (manufacturer) in the right listbox.
    Code:
    Option Compare Database
    Option Explicit
    
    
    Private Sub tabControl_Change()
        If tabControl.Value = 0 Then 'page 1
            'Clear listTable listbox selections
            Dim varItem As Variant
            If listTable.MultiSelect = 0 Then
                listTable = Null
            Else
                For Each varItem In listTable.ItemsSelected
                    listTable.Selected(varItem) = False
                Next
            End If
            listdata.RowSource = ""
        Else  'page 2
            listTable.Selected(0) = True 'select first item
            'Call listTable_Click 'trigger code
            Call setListData(listTable.Column(0, 0))
            listTable.Selected(0) = True ' Must select first item again because code de-selects first item
        End If
    End Sub
    Private Sub listTable_Click()
        Select Case listTable.Value
            Case "Manufacturer"
                listdata.RowSourceType = "Table/Query"
                listdata.RowSource = "SELECT * From Table1;"
                listdata.Requery
            Case "Type":
                listdata.RowSourceType = "Table/Query"
                listdata.RowSource = "SELECT * From Table2;"
                listdata.Requery
        End Select
    End Sub
    Private Sub setListData(arg As String)
        Select Case arg
            Case "Manufacturer":
                listdata.RowSourceType = "Table/Query"
                listdata.RowSource = "SELECT * From Table1;"
                listdata.Requery
            Case "Type":
                listdata.RowSourceType = "Table/Query"
                listdata.RowSource = "SELECT * From Table2;"
                listdata.Requery
        End Select
    End Sub

  12. #12
    ironfelix717 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    150
    Davegri,

    Your solution works and thank you. However, this is redundancy that should not be required. Lets compare the logic of my design (faulty) versus yours (working):

    Mine:
    Tab Change Event >>> Select First Item >>> Call ListTable Click Event >>> Data is displayed

    Yours:
    Tab Change Event >>> Select First Item >>> Call redundant procedure >>> Data is displayed

    So... to sum things up. The click action of a click event itself cannot be simulated with code? Hmmm.. well that's annoying.

    Now to find a way to eliminate this redundancy, I assume i could try the approach of:

    Tab Change Event >>> Select First Item >>> Call DisplayListData Function>>> Data is displayed
    Normal Click Event >>> Call DisplayListdata Function >>> Data is displayed


    That eliminates the redundancy. i'll test that theory. I just find it annoying the physical click event cannot be simulated. Thanks for your help, dave!!




    Isladogs,


    2. Try slowing the code down slightly
    If that was in fact a solution, you never mentioned it prior. I'm not sure--i will test for curiosity. What I am sure of is that setting the tab value to 0 simply fixes the code, but does not solve the problem we are discussing in this thread.

    Thanks for your input.


    Regards

  13. #13
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    How about this? Redundancy eliminated, call to click event working.
    Code:
    
    Option Compare Database
    Option Explicit
    Private Sub tabControl_Change()
        If tabControl.Value = 0 Then 'page 1
            'Clear listTable listbox selections
            Dim varItem As Variant
            If listTable.MultiSelect = 0 Then
                listTable = Null
            Else
                For Each varItem In listTable.ItemsSelected
                    listTable.Selected(varItem) = False
                Next
            End If
            listdata.RowSource = ""
        Else  'page 2
            listTable.Selected(0) = True 'select first item
            'Call listTable_Click 'trigger code
            Call setListData(listTable.Column(0, 0))
            listTable.Selected(0) = True ' Must select first item again because code de-selects first item
        End If
    End Sub
    
    Private Sub listTable_Click()
        Call setListData(listTable)
    End Sub
    
    Private Sub setListData(arg As String)
        Select Case arg
            Case "Manufacturer":
                listdata.RowSourceType = "Table/Query"
                listdata.RowSource = "SELECT * From Table1;"
                listdata.Requery
            Case "Type":
                listdata.RowSourceType = "Table/Query"
                listdata.RowSource = "SELECT * From Table2;"
                listdata.Requery
        End Select
    End Sub
    

  14. #14
    ironfelix717 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    150
    Dave,

    I was able to get it working by using the structure i mentioned above. It appears, your method would work as well.

    Thanks for your help. A shame we cannot simulate listbox_click events in code. Access design flaw?


    Regards,

  15. #15
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    I found a way to make your original code with call to click event work, with small change:
    Code:
    Option Compare Database
    Option Explicit
    Private Sub tabControl_Change()
        If tabControl.Value = 1 Then 'page 1
            'Clear listTable listbox selections
            Dim varItem As Variant
            If listTable.MultiSelect = 0 Then
                listTable = Null
            Else
                For Each varItem In listTable.ItemsSelected
                    listTable.Selected(varItem) = False
                Next
            End If
            listdata.RowSource = ""
        Else  'page 2
            'listTable.Selected(0) = True 'not needed
            Call listTable_Click 'trigger code
            listTable.Selected(0) = True ' Must select first item again because code de-selects first item
        End If
    End Sub
    
    Private Sub listTable_Click()
        Me.listTable.SetFocus           'Add this line
        Select Case listTable.Value
            Case "Manufacturer":
                listdata.RowSourceType = "Table/Query"
                listdata.RowSource = "SELECT * From Table1;"
                listdata.Requery
            Case "Type":
                listdata.RowSourceType = "Table/Query"
                listdata.RowSource = "SELECT * From Table2;"
                listdata.Requery
        End Select
    End Sub

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

Similar Threads

  1. Replies: 4
    Last Post: 04-12-2018, 09:09 AM
  2. Replies: 1
    Last Post: 05-21-2017, 12:31 AM
  3. Replies: 15
    Last Post: 10-01-2013, 09:31 PM
  4. Replies: 1
    Last Post: 09-10-2012, 11:21 PM
  5. Replies: 2
    Last Post: 07-24-2011, 08:50 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