Items not auto selected in list box

[jtan]

Hi guys.



Appreciate if someone can help.

in a form, user has to select site and date range. The program will query out and select all the inspectors in that site and date range. But, when change to another site, the program will query out the inspectors, but the selection doesn't work. See attachment. What's wrong?

[Bob Fitz]

Perhaps you need to run your code in the after update event of the site combo as well.

[June7]

What event(s) is code in?
Why do you need to select? What happens with the selection?

[jojowhite]

you can also add VBA code to the Site combobox AfterUpdate event to select the Inspectors:

Private Sub Combobox1_AfterUpdate()
Call HiliteList
End Sub

Private Sub Form_Current()
Call HiliteList
End Sub

Private Sub HiliteList()
Dim X As Integer, Y As Integer
X = Me.InspectorList.ListCount - 1
For Y = 0 To X
    Me.InspectorList.Selected(Y) = True
Next
End Sub

[jtan]

Perhaps you need to run your code in the after update event of the site combo as well.

Hi Bob,
I had added the code in site combo box after update event. But, it still doesn't work after changing site.
See attachment

[Bob Fitz]

Can you post a copy of your db

[June7]

Do you mean changing site by selection in combobox or by navigating record?

[jtan]

Do you mean changing site by selection in combobox or by navigating record?

changing site by selection in combo box

[June7]

What debugging have you done? Did you set breakpoint and confirm the event actually runs?

Also suggest you provide db for analysis. Follow instructions at bottom of my post.

[jtan]

i'd imported the relevant objects into a new db. See attachmentdb.accdb

[davegri]

Replace the entire form code with this.

Option Compare Database
Option Explicit


Private Sub CustomRequery()
    Dim X As Integer, varItm As Variant
    For Each varItm In InspectorList.ItemsSelected
        InspectorList.Selected(varItm) = False
    Next
       
    Me!InspectorList.Requery
    X = Me.InspectorList.ListCount - Abs(InspectorList.ColumnHeads)
    For X = 0 To X - 1
        Me.InspectorList.Selected(X) = True
    Next
    Me.Refresh
End Sub


Private Sub EndDate_AfterUpdate()
    Call CustomRequery
End Sub


Private Sub Form_Current()
    Call CustomRequery
End Sub


Private Sub Site_AfterUpdate()
    DoEvents
    Call CustomRequery
End Sub


Private Sub StartDate_AfterUpdate()
    Call CustomRequery
End Sub

[Micron]

Methinks that if the list has headers, you need a way to have X loop to start at 1 instead of starting at zero, and increase the count by 1?
EDIT - to explain further

    X = Me.List2.ListCount
    y = Abs(List2.ColumnHeads)
    For X = y To X - 1
        Me.List2.Selected(X) = True
    Next

because if headers are used, the list count grows by 1. Thus a 4 item listcount is 5 but there are only 4 items to be selected.

[jtan]

Replace the entire form code with this.

Option Compare Database
Option Explicit


Private Sub CustomRequery()
    Dim X As Integer, varItm As Variant
    For Each varItm In InspectorList.ItemsSelected
        InspectorList.Selected(varItm) = False
    Next
       
    Me!InspectorList.Requery
    X = Me.InspectorList.ListCount - Abs(InspectorList.ColumnHeads)
    For X = 0 To X - 1
        Me.InspectorList.Selected(X) = True
    Next
    Me.Refresh
End Sub


Private Sub EndDate_AfterUpdate()
    Call CustomRequery
End Sub


Private Sub Form_Current()
    Call CustomRequery
End Sub


Private Sub Site_AfterUpdate()
    DoEvents
    Call CustomRequery
End Sub


Private Sub StartDate_AfterUpdate()
    Call CustomRequery
End Sub

Thank you very much for your code. Problem solved.