dynamic check boxes connected to Access

[nqueen]

Hi,

I have 3 check boxes of products (A B C)and a flexgrid besides it. I have a table in access containing information of those three products. (e.g. PRodID: A11 A34 B45 B45 C3 C4) where the first letter shows which product it is linked to.

When I click on one check box, I need to select only the records of that checked product to appear (if two are checked then the records of those two only must appear).

How would I do it? ( I am not sure only how it connects to access

thanks!

 

[Golom]

Sub chkA_Click()
   QuerySub
End Sub

Sub chkA_Click()
   QuerySub
End Sub

Sub chkA_Click()
   QuerySub
End Sub

Private Sub QuerySub()
   Dim SQL As String
   DIM InClause As String
   DIM rs As DAO.Recordset

   [COLOR=red]' This version gives ALL records if 
   ' none of the check boxes are checked.[/color]

   SQL = "Select f1, f2, f3 From myTable " & _

   If chkA.Value = vbChecked Then 
      InClause = "'A'"         
   End If

   If chkB.Value = vbChecked Then 
      If Len(InClause) > 0 Then
         InClause = InClause & ",'B'"         
      Else
         InClause = "'B'"
      End If
   End If
   
   If chkC.Value = vbChecked Then 
      If Len(InClause) > 0 Then
         InClause = InClause & ",'C'"         
      Else
         InClause = "'C'"
      End If
   End If

   If Len(InClause) > 0 Then
      SQL = SQL & " Where Left(fld,1) IN (" & InClause & ")"
   End If

   Set rs = db.OpenRecordset ( SQL )

   Set FlexGrid.Datasource = rs

End Sub

or

Private Sub QuerySub()
   Dim SQL As String
   DIM InClause As String
   DIM rs As DAO.Recordset

   [COLOR=red]' This version gives NO records if 
   ' none of the check boxes are checked.[/color]

   SQL = "Select f1, f2, f3 From myTable Where " & _
         "(" & chkA.Value = vbChecked & " AND Left(fld,1) = 'A') " & _
     " OR (" & chkB.Value = vbChecked & " AND Left(fld,1) = 'B') " & _
     " OR (" & chkC.Value = vbChecked & " AND Left(fld,1) = 'C') " 

   Set rs = db.OpenRecordset ( SQL )

   Set FlexGrid.Datasource = rs

End Sub

 

[nqueen]

Hi,
Thank you for answering us, the only thing is that we are not working with DAO it s with ADO connection. We thought that it might be easier to show what we have done so far. The code does not give the results we want. We would like to display the results(two fields) on the flexgrid depending on the check boxes that have been selected. (like a filter)

for now, the code we have displays just the first field (fund_option ID) of the fund_options table. where we want to display two fields per record.

The table we have in access is called funds_options and has two fields Fund_optionID and fund_optionName.
e.g. of fund_optionsID (EQ_001) (Bal_002)...

(Product A B C from the first thread are actually Equity, Balanced and Fixed Income.)
thanks in advance we are really stuck on this we would really appreciate your help!




here is the code:

Private Sub cmdSearch_Click()
Dim strFieldValue As String
Dim strFund As String



If chkEquity.Value = Unchecked And chkBalanced.Value = Unchecked And chkFixedIncome.Value = Unchecked Then
MsgBox "Please select one or more fund options", vbOKOnly, "Information"
Exit Sub
End If


If chkEquity.Value = Checked Then
strFieldValue = "EQ*"
End If

If chkBalanced.Value = Checked Then
strFieldValue = "Bal*"
End If

If chkFixedIncome.Value = Checked Then
strFieldValue = "FX*"
End If



strFund = "SELECT fund_optionID, Fund_optionName FROM Fund_options WHERE fund_options like '" & strFieldValue & "'"

cConnect



With rSL
MsgBox (strFund)
.Source = strFund
.ActiveConnection = cSL
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.CursorLocation = adUseClient
.Open "fund_options"
.Requery

hflgFunds.TextMatrix(1, 1) = !fund_optionsID


End With



cClose

 

[Golom]

Private Sub cmdSearch_Click()
    Dim strFund                     As String
    Dim WhereClause                 As String

    If chkEquity.Value = vbUnchecked And _
       chkBalanced.Value = vbUnchecked And _
       chkFixedIncome.Value = vbUnchecked Then
        MsgBox "Please select one or more fund options", _
               vbOKOnly, "Information"
        Exit Sub
    End If

    strFund = "SELECT fund_optionID, Fund_optionName " & _
              "FROM Fund_options WHERE "

    If chkEquity.Value = vbChecked Then
        WhereClause = " fund_options like 'EQ%'       "
    End If

    If chkBalanced.Value = vbChecked Then
        If Len(WhereClause) > 0 Then
            WhereClause = WhereClause & _
                          " OR fund_options  like 'Bal%'"
        Else
            WhereClause = " fund_options  like 'Bal%'  "
        End If
    End If

    If chkFixedIncome.Value = vbChecked Then
        If Len(WhereClause) > 0 Then
            WhereClause = WhereClause & _
                          " OR fund_options  like 'FX%'"
        Else
            WhereClause = " fund_options  like 'FX%'  "
        End If
    End If

    strFund = strFund & WhereClause

    cConnect

    With rSL
        MsgBox (strFund)
        .ActiveConnection = cSL
        .CursorType = adOpenStatic
        .LockType = adLockOptimistic
        .CursorLocation = adUseClient
        .Open strFund
        .Requery

        hflgFunds.TextMatrix(1, 1) = !fund_optionsID

    End With

    cClose

End Sub

 

[Andrzejek]

nqueen

In your code you assign values to strFieldValue in your IF statements, but if first IF assignes value to it, second IF will overwrite it, and so on.

You may want to have DISPLAY or FIND command button next to check boxes,

HTH

---- Andy

 

[nqueen]

Hi
Thanks again for helping us Golom.
We tried your code as it is and that s the error it gives us
(it points to the .open strfunds)

run time error -2147217904(8004Oe10)
no value given for one or more required parameters

would you know what it means?

 

[nqueen]

how would you do that andy?
like chkequity.display?
where would I put this in the code?

 

[nqueen]

We finally made it work! Thanks so much for your help guys!
we did two things:
we added .source=strfund in the with statements
and in the whereclause of each if statement the right name was fund_optionID instead of fund_options (which is the table)
here is the final code
thanks again!


Private Sub cmdSearch_Click()


Dim strFund As String
Dim WhereClause As String

If chkEquity.Value = vbUnchecked And _
chkBalanced.Value = vbUnchecked And _
chkFixedIncome.Value = vbUnchecked Then
MsgBox "Please select one or more fund options", _
vbOKOnly, "Information"
Exit Sub
End If

strFund = "SELECT fund_optionID, Fund_optionName " & _
"FROM Fund_options WHERE "

If chkEquity.Value = vbChecked Then
WhereClause = " fund_optionID like 'EQ%' "
End If

If chkBalanced.Value = vbChecked Then
If Len(WhereClause) > 0 Then
WhereClause = WhereClause & _
" OR fund_optionID like 'Bal%'"
Else
WhereClause = " fund_optionID like 'Bal%' "
End If
End If

If chkFixedIncome.Value = vbChecked Then
If Len(WhereClause) > 0 Then
WhereClause = WhereClause & _
" OR fund_optionID like 'FX%'"
Else
WhereClause = " fund_optionID like 'FX%' "
End If
End If
Dim j As Integer
Dim i As Integer
strFund = strFund & WhereClause

cConnect

With rSL
.Source = strFund
.ActiveConnection = cSL
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.CursorLocation = adUseClient
.Open
.Requery

End With


Set hflgFunds.DataSource = rSL
cClose
end sub