Report filters by checked box

[lmv]

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 nameroducts List
based on qry: Order Details Extended
Control Source urchaseOrderSubform

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

 

[Michael H]

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

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 nameroducts List
based on qry: Order Details Extended
Control Source urchaseOrderSubform

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

 

[lmv]

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!!

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

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 nameroducts List
based on qry: Order Details Extended
Control Source urchaseOrderSubform

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

 

[Michael H]

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

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!!

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

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 nameroducts List
based on qry: Order Details Extended
Control Source urchaseOrderSubform

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

 

[lmv]

Thanks!

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

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!!

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


:

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 nameroducts List
based on qry: Order Details Extended
Control Source urchaseOrderSubform

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