Select all the Product in the list box

avan
3StarLounger
Posts: 223
Joined: 08 Oct 2017, 09:50

Select all the Product in the list box

Post by avan »

Me.Product.Requery

' Select the first Product in the list box
Me.Product = Me.Product.ItemData(0)

If i wish to select all the product in the list box, how to write the code?

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Select all the Product in the list box

Post by HansV »

Has the MultiSelect property of the Product list box been set to Simple or Extended?
Best wishes,
Hans

avan
3StarLounger
Posts: 223
Joined: 08 Oct 2017, 09:50

Re: Select all the Product in the list box

Post by avan »

Hi Hans, is simple.

avan
3StarLounger
Posts: 223
Joined: 08 Oct 2017, 09:50

Re: Select all the Product in the list box

Post by avan »

Below is my code

Code: Select all

Private Sub cmbNodun_AfterUpdate()
   
    Dim strWhere As String
   
    If Me.cmbNodun = "(Please Select)" Then
    grpOrdersstatus_AfterUpdate
    Else
    
    Select Case grpOrders
        Case 1, 2  ' DUN
            Select Case grpOrdersstatus
            Case 1   ' PH
                        strWhere = " WHERE PARTI_GABUNGAN = 'PH'"
                    Case 2
                        strWhere = " WHERE PARTI_GABUNGAN <> 'PH'"
                    Case 3 ' both
            strWhere = " WHERE PARTI_GABUNGAN <> ''"
                End Select
            End Select

        If Me.cmbYear <> "<All>" And Me.cmbYear <> "(Please Select)" Then
            strWhere = strWhere & " AND TAHUN='" & Me.cmbYear & "'"
        End If
        If Me.cmbState <> "<All>" And Me.cmbState <> "(Please Select)" Then
            strWhere = strWhere & " AND NEGERIID='" & Me.cmbState & "'"
        End If
        If Me.cmbParti <> "<All>" And Me.cmbParti <> "(Please Select)" Then
            strWhere = strWhere & " AND PARTI='" & Me.cmbParti & "'"
        End If
        If Me.cmbNodun <> "<All>" And Me.cmbNodun <> "(Please Select)" Then
            strWhere = strWhere & " AND NO_DUN='" & Me.cmbNodun & "'"
        End If
        Select Case grpOrders
            Case 1   ' Zone America
                    Select Case Frame
                        Case 1, 2, 3, 4, 5
                Me.cmbDun.RowSource = "SELECT DUN, First(CALONDUNID) As FirstCALONDUNID From tblCALON_DUN" & strWhere & "GROUP BY DUN 
                ORDER BY First(CALONDUNID)"
                ' Re-load the list box
                Me.cmbDun.Requery
                ' Select the first Product in the list box
                Me.cmbDun = Me.cmbDun.ItemData(0)
                        End Select
            Case 2   ' Zone Europe
                    Select Case Frame
                        Case 1, 2, 3, 4, 5
                Me.cmbParti.RowSource = "SELECT Dummy FROM tblAll UNION SELECT PARTI FROM tblPARTI" & strWhere
                        Case 1, 2, 3, 4, 5
                Me.cmbParti.RowSource = "SELECT Dummy FROM tblAll UNION SELECT PARTI FROM tblCALON_PARLIMEN" & strWhere
                Me.cmbNoparlimen.RowSource = "SELECT Dummy, 0 As CALONPARID FROM tblAll UNION SELECT NO_PARLIMEN, First(CALONPARID) FROM 
                tblCALON_PARLIMEN" & strWhere & " GROUP BY NO_PARLIMEN ORDER BY PARLIMENID"
                Me.cmbParlimen.RowSource = "SELECT Dummy, 0 As CALONPARID FROM tblAll UNION SELECT PARLIMEN, First(CALONPARID) FROM 
               tblCALON_PARLIMEN" & strWhere & "GROUP BY PARLIMEN  ORDER BY PARLIMENID"
                        End Select
        End Select
        End If
    ShowFilter
End Sub

avan
3StarLounger
Posts: 223
Joined: 08 Oct 2017, 09:50

Re: Select all the Product in the list box

Post by avan »

What I want is when I select "ALL" from cmbNodun list box, the cmbDun will select all and highlight.

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Select all the Product in the list box

Post by HansV »

Replace

' Select the first Product in the list box
Me.Product = Me.Product.ItemData(0)

with

Code: Select all

                        If Me.cmbNoDun = "<All>" Then
                            ' Select all items
                            Dim i As Long
                            For i = 0 To Me.cmbDun.ListCount - 1
                                Me.cmbDun.Selected(i) = True
                            Next i
                        Else
                            ' Select the first Product in the list box
                            Me.cmbDun = Me.cmbDun.ItemData(0)
                        End If
Remark: in the part

Code: Select all

            Case 2   ' Zone Europe
                    Select Case Frame
                        Case 1, 2, 3, 4, 5
                Me.cmbParti.RowSource = "SELECT Dummy FROM tblAll UNION SELECT PARTI FROM tblPARTI" & strWhere
                        Case 1, 2, 3, 4, 5
                            ...
the second Case 1, 2, 3, 4, 5 will not be executed!
Best wishes,
Hans

avan
3StarLounger
Posts: 223
Joined: 08 Oct 2017, 09:50

Re: Select all the Product in the list box

Post by avan »

Hi Hanv

Thanks, but it not working as my expectation. It didn't highlight all the record.
You may refer to below picture.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Select all the Product in the list box

Post by HansV »

It would help if you could attach a working database. The previous ones were not usable.
Best wishes,
Hans

avan
3StarLounger
Posts: 223
Joined: 08 Oct 2017, 09:50

Re: Select all the Product in the list box

Post by avan »

Hi Hans, Please refer to the sample working.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Select all the Product in the list box

Post by HansV »

Unlike what you wrote, the MultiSelect property of cmbDun is set to None, so it is not possible to select more than one item. You should set it to Simple or Extended.
Best wishes,
Hans

avan
3StarLounger
Posts: 223
Joined: 08 Oct 2017, 09:50

Re: Select all the Product in the list box

Post by avan »

Hi Hans, after I change the MultiSelect property of cmbDun to Simple or Extended. The current filter and cmbNodun is not working when I click the cmbDun, you may refer to the picture before and after. I also attached the sample working after change the MultiSelect property of cmbDun.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Select all the Product in the list box

Post by HansV »

Do you REALLY need this? It will require rewriting much of the code, since a multi-select list box does not have a value.
It would probably to remove the items (Please Select) and <All> from the row source, and that would also require a lot of changes to the code.
Best wishes,
Hans

avan
3StarLounger
Posts: 223
Joined: 08 Oct 2017, 09:50

Re: Select all the Product in the list box

Post by avan »

Dear Hans, yes. I need that. Can you assist to guide me write a correct code? Thank you.

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Select all the Product in the list box

Post by HansV »

I'll try it, but it is very complicated, so it will take a while.
Best wishes,
Hans

avan
3StarLounger
Posts: 223
Joined: 08 Oct 2017, 09:50

Re: Select all the Product in the list box

Post by avan »

Hi Hans, sure. Thanks for your help.

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Select all the Product in the list box

Post by HansV »

I'm very sorry. I have taken a look at it, and it's too complicated. I'm not going to spend weeks to redesign the database.
Best wishes,
Hans

avan
3StarLounger
Posts: 223
Joined: 08 Oct 2017, 09:50

Re: Select all the Product in the list box

Post by avan »

Hi Hans, Can you guide me which part need to rewrite?

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Select all the Product in the list box

Post by HansV »

Most of the code for the form will have to be rewritten - everything that refers to cmbDun.
Best wishes,
Hans