CODEWITHSUNDEEP
sqlvb6
user1671624
user1671624

Reputation: 1

Have more than one checkbox ticked VB6?

I have a program which, when clicking Search, displays a list of products. When creating a new product there is an "Active" checkbox which is automatically set to True. The user can edit any product and, if desired, change the product to "Inactive".

On the search form, there are two checkboxes: Active and Inactive. When the program loads the Active checkbox is always ticked. If the user wants to view inactive products as well the should be able to have both ticked.

At the moment when I have active ticked, the listbox displays all active items and when I have Inactive only ticked it display Inactive items. However when I have both ticked, only inactive products are shown.

This is my code:

Private Sub cmdProdSearch_Click()
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim list_item As ListItem
    Dim itm As ListItem


    db_file = db_file & "ProductsDatabase1.mdb"
    Set conn = New ADODB.Connection
    conn.ConnectionString = _
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ProductsDatabase1.mdb;" & _
    "Persist Security Info=False" & _
    "Data Source=" & db_file & ";" & _
    "Persist Security Info=False"
    conn.Open

    Set rs = conn.Execute("Select * from Export where Product like '%" & _
        txtProduct.Text & "%'")
    If chkActive.Value = 1 Then
        Set rs = conn.Execute("Select * from Export where Inactive = '0'")
    Else
        Set rs = conn.Execute("Select * from Export where Inactive = '1'")


        ListView1.ListItems.Clear

        With ListView1
            .View = lvwReport
            .FullRowSelect = True
            Do While Not rs.EOF
                Set itm = .FindItem(txtProduct.Text, lvwText, , lvwPartial)
                Set list_item = .ListItems.Add(, , rs!Product)
                list_item.SubItems(1) = rs!barcode & ""
                list_item.SubItems(2) = rs!quantity & ""
                list_item.SubItems(3) = rs!Department & ""
                list_item.SubItems(4) = rs!Active & ""
                list_item.SubItems(5) = rs!Inactive
                rs.MoveNext
            Loop
        End With
    End If

End Sub

Also I am using an Access database to store product info, and am using SQL statements to find information.

Any suggestions as to be able to display bot Active and Inactive items when both checkboxes are ticked?

Upvotes: 0

Views: 900

Answers (1)

Daniel
Daniel

Reputation: 13122

You'll need to update your logic. As written ListView1 is only updated when chkActive.Value <> 1.

Normally I would make a separate string variable to hold my SQL and then build my search based upon criteria... here's an edit of your code which does this and includes logical paths for the different check boxes. It assumes your other checkbox is called chkInactive:

Private Sub cmdProdSearch_Click()
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim list_item As ListItem
    Dim itm As ListItem
    dim SQL as string

    db_file = db_file & "ProductsDatabase1.mdb"
    Set conn = New ADODB.Connection
    conn.ConnectionString = _
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ProductsDatabase1.mdb;" & _
    "Persist Security Info=False" & _
    "Data Source=" & db_file & ";" & _
    "Persist Security Info=False"
    conn.Open

    SQL = "Select * from Export where Product like '%" & txtProduct.Text & "%'"
    If chkActive.Value = 1 Then
        If chkInactive.Value = 1 Then
            'do nothing because criteria is already set.
        Else
            SQL = SQL & " AND Inactive = '0'"
        End If
    ElseIf chkInactive.Value = 1 Then
        SQL = SQL & " AND Inactive = '1'"
    Else
        'Do something because neither are checked?
    End If
    Set rs = conn.Execute(SQL)
    ListView1.ListItems.Clear

    With ListView1
        .View = lvwReport
        .FullRowSelect = True
        Do While Not rs.EOF
            Set itm = .FindItem(txtProduct.Text, lvwText, , lvwPartial)
            Set list_item = .ListItems.Add(, , rs!Product)
            list_item.SubItems(1) = rs!barcode & ""
            list_item.SubItems(2) = rs!quantity & ""
            list_item.SubItems(3) = rs!Department & ""
            list_item.SubItems(4) = rs!Active & ""
            list_item.SubItems(5) = rs!Inactive
            rs.MoveNext
        Loop
    End With
End Sub

Upvotes: 4

Related Questions