Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Location
    Southern CA
    Posts
    8

    Unhappy Unanswered: SQL WHERE Condition too long, so DoCmd.OpenForm

    Hello! I have a set of 9 checkboxes on a form. I would like to be able to check ONE OR MANY of them, click a button, and have another form come up showing the filtered records. I created a macro for this (which worked fine), but the "Where" condition was too long (I could only filter 5 of the boxes) so they recommended I use the DoCmd.OpenForm method. I know nothing about code, but I tried this and I can't get it to work. The form comes up, and is formatted correctly, etc., but it will not filter (as if it cannot see the "Where" statement). The tricky thing is I need it to not be an ONLY filter, meaning that if I check the "AP" box, I not only want it to show me those records with ONLY "AP" checked, but ANY records with "AP" checked (could have "DP" checked also).
    Here is the code I have now... please help!!!

    Private Sub Command45_Click()
    On Error GoTo Err_Command45_Click

    DoCmd.OpenForm "ProdIDSubFrm", acFormDS, , [APCheck] = [Forms]![NewFrm2]![APCheck] And [DPCheck] = [Forms]![NewFrm2]![DPCheck] And [PLCheck] = [Forms]![NewFrm2]![PLCheck] And [PROCheck] = [Forms]![NewFrm2]![PROCheck] And [HOCheck] = [Forms]![NewFrm2]![HOCheck] And [COCheck] = [Forms]![NewFrm2]![COCheck] And [BICheck] = [Forms]![NewFrm2]![BICheck] And [FICheck] = [Forms]![NewFrm2]![FICheck] And [PMCheck] = [Forms]![NewFrm2]![PMCheck], acFormReadOnly, acWindowNormal

    Exit_Command45_Click:
    Exit Sub

    Err_Command45_Click:
    MsgBox Err.Description
    Resume Exit_Command45_Click
    End Sub

    P.S.-- I also hear I can do it as a "StrWhere", but I don't know how!!

  2. #2
    Join Date
    Dec 2002
    Location
    Southern CA
    Posts
    8

    Question Re: SQL WHERE Condition too long, so DoCmd.OpenForm

    I have also tried this way... It works partway, but only returns the records with ONLY those particular boxes checked. For example, if I check AP and PL, it returns the records with ONLY AP and PL checked, not all the records which have both AP and PL checked (such as one which has AP, DP and PL checked). Any answers???

    Private Sub Command45_Click()
    On Error GoTo Err_Command45_Click

    dim strWhere As String

    strWhere = "[APCheck] = " & Forms("NewFrm2").Controls("APCheck") & _
    " And [DPCheck] = " & Forms("NewFrm2").Controls("DPCheck")& _
    " And [PLCheck] = " & Forms("NewFrm2").Controls("PLCheck")& _
    " And [PROCheck] = " & Forms("NewFrm2").Controls("PROCheck")& _
    " And [HOCheck] = " & Forms("NewFrm2").Controls("HOCheck")& _
    " And [COPCheck] = " & Forms("NewFrm2").Controls("COCheck")& _
    " And [BICheck] = " & Forms("NewFrm2").Controls("BICheck")& _
    " And [FICheck] = " & Forms("NewFrm2").Controls("FICheck")& _
    " And [PMCheck] = " & Forms("NewFrm2").Controls("PMCheck")& _


    DoCmd.OpenForm "ProdIDSubFrm", acFormDS, , strWhere,
    acFormReadOnly, acWindowNormal

    Exit_Command45_Click:
    Exit Sub

    Err_Command45_Click:
    MsgBox Err.Description
    Resume Exit_Command45_Click
    End Sub

  3. #3
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Try using OR instead of And in your where statment
    let us know
    Jim

  4. #4
    Join Date
    Dec 2002
    Location
    Southern CA
    Posts
    8

    Angry

    OR instead of AND doesn't work at all... it brings up the form and says "Filtered" at the bottom, but it still shows me all the records. Help!

  5. #5
    Join Date
    Dec 2002
    Location
    Southern CA
    Posts
    8

    Smile SOLVED!!!

    I GOT IT!!!!!! Yes!!!! Someone in the Yahoo MSAccess Group helped me! I recommend that Group to all of you!!

    Thanks!
    Here it is...

    Private Sub Command45_Click()
    On Error GoTo Err_Command45_Click

    Dim strWhere As String

    'Only add the controls that are checked to filter condition
    With Forms("NewFrm2")
    If Nz(.Controls("APCheck"), 0) <> 0 Then
    strWhere = "[APCheck] = " & .Controls("APCheck")
    End If
    If Nz(.Controls("DPCheck"), 0) <> 0 Then
    If strWhere <> "" Then strWhere = strWhere & " And "
    strWhere = strWhere & "[DPCheck] = " & .Controls("DPCheck")
    End If
    If Nz(.Controls("PLCheck"), 0) <> 0 Then
    If strWhere <> "" Then strWhere = strWhere & " And "
    strWhere = strWhere & "[PLCheck] = " & .Controls("PLCheck")
    End If
    If Nz(.Controls("PROCheck"), 0) <> 0 Then
    If strWhere <> "" Then strWhere = strWhere & " And "
    strWhere = strWhere & "[PROCheck] = " & .Controls("PROCheck")
    End If
    If Nz(.Controls("HOCheck"), 0) <> 0 Then
    If strWhere <> "" Then strWhere = strWhere & " And "
    strWhere = strWhere & "[HOCheck] = " & .Controls("HOCheck")
    End If
    If Nz(.Controls("COCheck"), 0) <> 0 Then
    If strWhere <> "" Then strWhere = strWhere & " And "
    strWhere = strWhere & "[COCheck] = " & .Controls("COCheck")
    End If
    If Nz(.Controls("BICheck"), 0) <> 0 Then
    If strWhere <> "" Then strWhere = strWhere & " And "
    strWhere = strWhere & "[BICheck] = " & .Controls("BICheck")
    End If
    If Nz(.Controls("FICheck"), 0) <> 0 Then
    If strWhere <> "" Then strWhere = strWhere & " And "
    strWhere = strWhere & "[FICheck] = " & .Controls("FICheck")
    End If
    If Nz(.Controls("PMCheck"), 0) <> 0 Then
    If strWhere <> "" Then strWhere = strWhere & " And "
    strWhere = strWhere & "[PMCheck] = " & .Controls("PMCheck")
    End If

    End With

    DoCmd.OpenForm "ProdIDSubFrm", acFormDS, , strWhere, acFormReadOnly, acWindowNormal

    Exit_Command45_Click:
    Exit Sub

    Err_Command45_Click:
    MsgBox Err.Description
    Resume Exit_Command45_Click
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •