Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Report filters by checked box

Discussion in 'Information Technology' started by lmv, Jul 28, 2006.

  1. lmv

    lmv
    Expand Collapse
    Guest

    I have a cmd on my Form to open a report: rptReturn

    This needs to filter by the item(OrderID) AND returned ck box. I have read
    some things about needing to make a qry to return the ckbox true info but I
    don't know how. And then I need to filter the form... here is what I have so
    far. Can anyone help me to clarify exactly what to build as far as the qry
    goes.

    Form:OrdersWDetails

    Sub Form name:products List
    based on qry: Order Details Extended
    Control Source :purchaseOrderSubform

    check box
    Name: ckRet
    Control Source :Returned

    Private Sub cmdRefund_Click()
    On Error GoTo Err_cmdRefund_Click

    DoCmd.OpenReport "rptReturns", acPreview
    Reports![rptReturns].Filter = "OrderID = " & Me![Product List].ckReturned
    Reports![rptReturns].FilterOn = True

    Exit_cmdRefund_Click:
    Exit Sub

    Err_cmdRefund_Click:
    MsgBox err.Description
    Resume Exit_cmdRefund_Click

    End Sub

    Thanks!!
    lmv
     
  2. Loading...

    Similar Threads Forum Date
    UK British Sikh Report-2016 Breaking News Mar 24, 2016
    1984 The Story I Did Not Report In ’84 History of Sikhism Jun 28, 2015
    Opinion Top Green Beret Officer Forced to Resign Over Affair With WaPo Reporter Breaking News Jun 26, 2014
    United Sikhs New Report Shows Increasing Hate Attacks on Sikhs Sikh Organisations May 21, 2014
    Legal ACLU reports: Judge orders Sikh man, "Remove that rag, or Go to Jail! Breaking News Sep 27, 2013

  3. Michael H

    Michael H
    Expand Collapse
    Guest

    Hi.

    Either your post is a bit confusing, or I am not understanding it correctly.
    Is the subform name "Products List" (as you say) or "Product List" (as your
    code says)? Is the checkbox name "ckRet" or "ckReturned"? This would be
    easier if you correctly identified the names of all involved controls and
    fields. I'm going to assume the names in your code are the correct ones.

    I'm not sure what you mean by "needing to make a qry to return the ckbox
    true info". If running the report without a filter returns all records, then
    you can limit those records by applying a filter, which is exactly what you
    were attempting to do. The problem is the way you have structured the filter.

    The filter as you have written seems to be trying to show records where
    OrderID is equal to the value of the subform's checkbox, which can only be 0
    or -1 (or True/False). Also, there is a problem with the way you are
    referring to controls on the subform (for more info on this see
    http://www.mvps.org/access/forms/frm0031.htm ).

    Try something like this (watch for line wrap):
    Reports![rptReturns].Filter = "OrderID = " & Me![Product
    List].Form!YourOrderIDControl & " AND " & "YourCheckboxField = " &
    Me![Product List].Form!ckReturned

    The above filter is attempting to limit the report to records where the
    field named "OrderID" is equal to the value of the control named
    "YourOrderIDControl" on the "Product List" subform, AND where the field named
    "YourCheckboxField" is equal to the value of the control named "ckReturned"
    on the "Product List" subform. Make sure you change "YourOrderIDControl" and
    "YourCheckboxField" to the actual names used in your database.

    Also, try adding this line to the end of your code:
    Debug.Print Reports![rptReturns].Filter
    You can then view the Immediate Window (Ctrl-G) to see exactly what filter
    your report is using, which will help you diagnose problems (alternatively,
    use MsgBox instead of Debug.Print).

    -Michael


    "lmv" wrote:

    > I have a cmd on my Form to open a report: rptReturn
    >
    > This needs to filter by the item(OrderID) AND returned ck box. I have read
    > some things about needing to make a qry to return the ckbox true info but I
    > don't know how. And then I need to filter the form... here is what I have so
    > far. Can anyone help me to clarify exactly what to build as far as the qry
    > goes.
    >
    > Form:OrdersWDetails
    >
    > Sub Form name:products List
    > based on qry: Order Details Extended
    > Control Source :purchaseOrderSubform
    >
    > check box
    > Name: ckRet
    > Control Source :Returned
    >
    > Private Sub cmdRefund_Click()
    > On Error GoTo Err_cmdRefund_Click
    >
    > DoCmd.OpenReport "rptReturns", acPreview
    > Reports![rptReturns].Filter = "OrderID = " & Me![Product List].ckReturned
    > Reports![rptReturns].FilterOn = True
    >
    > Exit_cmdRefund_Click:
    > Exit Sub
    >
    > Err_cmdRefund_Click:
    > MsgBox err.Description
    > Resume Exit_cmdRefund_Click
    >
    > End Sub
    >
    > Thanks!!
    > lmv
    >
     
  4. lmv

    lmv
    Expand Collapse
    Guest

    my post is probably confusing... sorry
    I used what you posted and it returns the information for 8 records from the
    form...
    That would be the ones where the returned box is NOT checked (though the
    total still reflects ALL 9 records.)
    So HOW do we tell it that the ones we want are when the
    ckReturned(controlNAME) is checked?

    Reports![rptReturns].Filter = "OrderID = " & _
    Me![Products List].Form!OrderID & " AND " & "Returned = " & _
    Me![Products List].Form!ckReturned

    Thanks!!


    "Michael H" wrote:

    > Hi.
    >
    > Either your post is a bit confusing, or I am not understanding it correctly.
    > Is the subform name "Products List" (as you say) or "Product List" (as your
    > code says)? Is the checkbox name "ckRet" or "ckReturned"? This would be
    > easier if you correctly identified the names of all involved controls and
    > fields. I'm going to assume the names in your code are the correct ones.
    >
    > I'm not sure what you mean by "needing to make a qry to return the ckbox
    > true info". If running the report without a filter returns all records, then
    > you can limit those records by applying a filter, which is exactly what you
    > were attempting to do. The problem is the way you have structured the filter.
    >
    > The filter as you have written seems to be trying to show records where
    > OrderID is equal to the value of the subform's checkbox, which can only be 0
    > or -1 (or True/False). Also, there is a problem with the way you are
    > referring to controls on the subform (for more info on this see
    > http://www.mvps.org/access/forms/frm0031.htm ).
    >
    > Try something like this (watch for line wrap):
    > Reports![rptReturns].Filter = "OrderID = " & Me![Product
    > List].Form!YourOrderIDControl & " AND " & "YourCheckboxField = " &
    > Me![Product List].Form!ckReturned
    >
    > The above filter is attempting to limit the report to records where the
    > field named "OrderID" is equal to the value of the control named
    > "YourOrderIDControl" on the "Product List" subform, AND where the field named
    > "YourCheckboxField" is equal to the value of the control named "ckReturned"
    > on the "Product List" subform. Make sure you change "YourOrderIDControl" and
    > "YourCheckboxField" to the actual names used in your database.
    >
    > Also, try adding this line to the end of your code:
    > Debug.Print Reports![rptReturns].Filter
    > You can then view the Immediate Window (Ctrl-G) to see exactly what filter
    > your report is using, which will help you diagnose problems (alternatively,
    > use MsgBox instead of Debug.Print).
    >
    > -Michael
    >
    >
    > "lmv" wrote:
    >
    > > I have a cmd on my Form to open a report: rptReturn
    > >
    > > This needs to filter by the item(OrderID) AND returned ck box. I have read
    > > some things about needing to make a qry to return the ckbox true info but I
    > > don't know how. And then I need to filter the form... here is what I have so
    > > far. Can anyone help me to clarify exactly what to build as far as the qry
    > > goes.
    > >
    > > Form:OrdersWDetails
    > >
    > > Sub Form name:products List
    > > based on qry: Order Details Extended
    > > Control Source :purchaseOrderSubform
    > >
    > > check box
    > > Name: ckRet
    > > Control Source :Returned
    > >
    > > Private Sub cmdRefund_Click()
    > > On Error GoTo Err_cmdRefund_Click
    > >
    > > DoCmd.OpenReport "rptReturns", acPreview
    > > Reports![rptReturns].Filter = "OrderID = " & Me![Product List].ckReturned
    > > Reports![rptReturns].FilterOn = True
    > >
    > > Exit_cmdRefund_Click:
    > > Exit Sub
    > >
    > > Err_cmdRefund_Click:
    > > MsgBox err.Description
    > > Resume Exit_cmdRefund_Click
    > >
    > > End Sub
    > >
    > > Thanks!!
    > > lmv
    > >
     
  5. Michael H

    Michael H
    Expand Collapse
    Guest

    I was under the impression you wanted to return records where the checkbox
    field is equal to the value of the field on your subform. If you just want
    records where the control is checked, try:

    Reports![rptReturns].Filter = "OrderID = " & _
    Me![Products List].Form!OrderID & " AND Returned = -1"

    -Michael


    "lmv" wrote:

    > my post is probably confusing... sorry
    > I used what you posted and it returns the information for 8 records from the
    > form...
    > That would be the ones where the returned box is NOT checked (though the
    > total still reflects ALL 9 records.)
    > So HOW do we tell it that the ones we want are when the
    > ckReturned(controlNAME) is checked?
    >
    > Reports![rptReturns].Filter = "OrderID = " & _
    > Me![Products List].Form!OrderID & " AND " & "Returned = " & _
    > Me![Products List].Form!ckReturned
    >
    > Thanks!!
    >
    >
    > "Michael H" wrote:
    >
    > > Hi.
    > >
    > > Either your post is a bit confusing, or I am not understanding it correctly.
    > > Is the subform name "Products List" (as you say) or "Product List" (as your
    > > code says)? Is the checkbox name "ckRet" or "ckReturned"? This would be
    > > easier if you correctly identified the names of all involved controls and
    > > fields. I'm going to assume the names in your code are the correct ones.
    > >
    > > I'm not sure what you mean by "needing to make a qry to return the ckbox
    > > true info". If running the report without a filter returns all records, then
    > > you can limit those records by applying a filter, which is exactly what you
    > > were attempting to do. The problem is the way you have structured the filter.
    > >
    > > The filter as you have written seems to be trying to show records where
    > > OrderID is equal to the value of the subform's checkbox, which can only be 0
    > > or -1 (or True/False). Also, there is a problem with the way you are
    > > referring to controls on the subform (for more info on this see
    > > http://www.mvps.org/access/forms/frm0031.htm ).
    > >
    > > Try something like this (watch for line wrap):
    > > Reports![rptReturns].Filter = "OrderID = " & Me![Product
    > > List].Form!YourOrderIDControl & " AND " & "YourCheckboxField = " &
    > > Me![Product List].Form!ckReturned
    > >
    > > The above filter is attempting to limit the report to records where the
    > > field named "OrderID" is equal to the value of the control named
    > > "YourOrderIDControl" on the "Product List" subform, AND where the field named
    > > "YourCheckboxField" is equal to the value of the control named "ckReturned"
    > > on the "Product List" subform. Make sure you change "YourOrderIDControl" and
    > > "YourCheckboxField" to the actual names used in your database.
    > >
    > > Also, try adding this line to the end of your code:
    > > Debug.Print Reports![rptReturns].Filter
    > > You can then view the Immediate Window (Ctrl-G) to see exactly what filter
    > > your report is using, which will help you diagnose problems (alternatively,
    > > use MsgBox instead of Debug.Print).
    > >
    > > -Michael
    > >
    > >
    > > "lmv" wrote:
    > >
    > > > I have a cmd on my Form to open a report: rptReturn
    > > >
    > > > This needs to filter by the item(OrderID) AND returned ck box. I have read
    > > > some things about needing to make a qry to return the ckbox true info but I
    > > > don't know how. And then I need to filter the form... here is what I have so
    > > > far. Can anyone help me to clarify exactly what to build as far as the qry
    > > > goes.
    > > >
    > > > Form:OrdersWDetails
    > > >
    > > > Sub Form name:products List
    > > > based on qry: Order Details Extended
    > > > Control Source :purchaseOrderSubform
    > > >
    > > > check box
    > > > Name: ckRet
    > > > Control Source :Returned
    > > >
    > > > Private Sub cmdRefund_Click()
    > > > On Error GoTo Err_cmdRefund_Click
    > > >
    > > > DoCmd.OpenReport "rptReturns", acPreview
    > > > Reports![rptReturns].Filter = "OrderID = " & Me![Product List].ckReturned
    > > > Reports![rptReturns].FilterOn = True
    > > >
    > > > Exit_cmdRefund_Click:
    > > > Exit Sub
    > > >
    > > > Err_cmdRefund_Click:
    > > > MsgBox err.Description
    > > > Resume Exit_cmdRefund_Click
    > > >
    > > > End Sub
    > > >
    > > > Thanks!!
    > > > lmv
    > > >
     
  6. lmv

    lmv
    Expand Collapse
    Guest

    Thanks!

    "Michael H" wrote:

    > I was under the impression you wanted to return records where the checkbox
    > field is equal to the value of the field on your subform. If you just want
    > records where the control is checked, try:
    >
    > Reports![rptReturns].Filter = "OrderID = " & _
    > Me![Products List].Form!OrderID & " AND Returned = -1"
    >
    > -Michael
    >
    >
    > "lmv" wrote:
    >
    > > my post is probably confusing... sorry
    > > I used what you posted and it returns the information for 8 records from the
    > > form...
    > > That would be the ones where the returned box is NOT checked (though the
    > > total still reflects ALL 9 records.)
    > > So HOW do we tell it that the ones we want are when the
    > > ckReturned(controlNAME) is checked?
    > >
    > > Reports![rptReturns].Filter = "OrderID = " & _
    > > Me![Products List].Form!OrderID & " AND " & "Returned = " & _
    > > Me![Products List].Form!ckReturned
    > >
    > > Thanks!!
    > >
    > >
    > > "Michael H" wrote:
    > >
    > > > Hi.
    > > >
    > > > Either your post is a bit confusing, or I am not understanding it correctly.
    > > > Is the subform name "Products List" (as you say) or "Product List" (as your
    > > > code says)? Is the checkbox name "ckRet" or "ckReturned"? This would be
    > > > easier if you correctly identified the names of all involved controls and
    > > > fields. I'm going to assume the names in your code are the correct ones.
    > > >
    > > > I'm not sure what you mean by "needing to make a qry to return the ckbox
    > > > true info". If running the report without a filter returns all records, then
    > > > you can limit those records by applying a filter, which is exactly what you
    > > > were attempting to do. The problem is the way you have structured the filter.
    > > >
    > > > The filter as you have written seems to be trying to show records where
    > > > OrderID is equal to the value of the subform's checkbox, which can only be 0
    > > > or -1 (or True/False). Also, there is a problem with the way you are
    > > > referring to controls on the subform (for more info on this see
    > > > http://www.mvps.org/access/forms/frm0031.htm ).
    > > >
    > > > Try something like this (watch for line wrap):
    > > > Reports![rptReturns].Filter = "OrderID = " & Me![Product
    > > > List].Form!YourOrderIDControl & " AND " & "YourCheckboxField = " &
    > > > Me![Product List].Form!ckReturned
    > > >
    > > > The above filter is attempting to limit the report to records where the
    > > > field named "OrderID" is equal to the value of the control named
    > > > "YourOrderIDControl" on the "Product List" subform, AND where the field named
    > > > "YourCheckboxField" is equal to the value of the control named "ckReturned"
    > > > on the "Product List" subform. Make sure you change "YourOrderIDControl" and
    > > > "YourCheckboxField" to the actual names used in your database.
    > > >
    > > > Also, try adding this line to the end of your code:
    > > > Debug.Print Reports![rptReturns].Filter
    > > > You can then view the Immediate Window (Ctrl-G) to see exactly what filter
    > > > your report is using, which will help you diagnose problems (alternatively,
    > > > use MsgBox instead of Debug.Print).
    > > >
    > > > -Michael
    > > >
    > > >
    > > > "lmv" wrote:
    > > >
    > > > > I have a cmd on my Form to open a report: rptReturn
    > > > >
    > > > > This needs to filter by the item(OrderID) AND returned ck box. I have read
    > > > > some things about needing to make a qry to return the ckbox true info but I
    > > > > don't know how. And then I need to filter the form... here is what I have so
    > > > > far. Can anyone help me to clarify exactly what to build as far as the qry
    > > > > goes.
    > > > >
    > > > > Form:OrdersWDetails
    > > > >
    > > > > Sub Form name:products List
    > > > > based on qry: Order Details Extended
    > > > > Control Source :purchaseOrderSubform
    > > > >
    > > > > check box
    > > > > Name: ckRet
    > > > > Control Source :Returned
    > > > >
    > > > > Private Sub cmdRefund_Click()
    > > > > On Error GoTo Err_cmdRefund_Click
    > > > >
    > > > > DoCmd.OpenReport "rptReturns", acPreview
    > > > > Reports![rptReturns].Filter = "OrderID = " & Me![Product List].ckReturned
    > > > > Reports![rptReturns].FilterOn = True
    > > > >
    > > > > Exit_cmdRefund_Click:
    > > > > Exit Sub
    > > > >
    > > > > Err_cmdRefund_Click:
    > > > > MsgBox err.Description
    > > > > Resume Exit_cmdRefund_Click
    > > > >
    > > > > End Sub
    > > > >
    > > > > Thanks!!
    > > > > lmv
    > > > >
     

Share This Page