Filter Report Using Check Boxes 1

[nyamrembo]

I have a labels report that I would like to filter using a tabular form with check boxes for the user to be able to select the name by ticking the check box then openning the report for printing. I am not good with code and I need some help. I have created a command button, cmdPrintLabel but it doesn't work because there is no code.
I would also like to be able to have the form refresh after the user has printed his/her selected labels.

Can you help with the code. Report name is labelsReport, and it's from a customer table. The check box is called chklabels

Thanks

 

[dhookom]

How far have you gotten? Do you have a tabular form?
Is there a field in the customer table to store the value of the check box?
Have you tried using the command button wizard to create a button to open your report?

Duane
Hook'D on Access
MS Access MVP

 

[nyamrembo]

Yes, I have the tabular form with customer names, check box next to each customer a command button that opens the report. I also have a check box on the customer table to store the value. What I am trying to accomplish is to be able to select the customer name that I want to print the label for and pull the report for only the checked names.

Thanks,

 

[MajP]

Since the check boxes are fields in the table, the query for the report just needs a criteria
select yourfieldname1, yourfieldname2 from yourTable where yourcheckBoxFieldname = True

 

[nyamrembo]

I see what you are saying. Actually, I don't want the check box field in the table now because the user will not be updating the check box as part of data entry. What I want to be able to do to select the name from the tabular form and print the label that corresponds to the name selected. I see how storing the value in the table is not going to be efficient. Can you advice please.

Thanks,

 

[MajP]

Although it may not be ideal, it is by far the easiest way to do it. If you are not good with code then you may want to stick with this. I use this trick often. Then you can build a simple update query to set the selection back to false to clear out the selections. You can not do this in a tabular form unless you put it as a field in a table like you have it, you can not use an unbound checkbox.

The other solution is a multiselect list box where you select all the names and run a query based on the listbox. This takes a little more code but there are several faqs on this.

There are some other solutions like using a listview which has checkboxes but that requires even more code.

 

[nyamrembo]

I am still struggling with this but I have managed to put some code together that is not working well enough. This is what the code below is doing right now, if I select one customer, the report comes up with that one customer label. But if i select more than one customer, lets say 3 names, then the report comes up with only the last name in the selection. But if nothing it selected, it pulls up all the records. I am hoping that you can help me fine tune it.



Private Sub cmdPrintReport_Click()

Dim stDocName As String
Dim stWhere As String

stDocName = "rptCustomerLabel"
If Me.chkLabel.Value = True Then
stWhere = "tblCustomer.CustID=" & Me![tblCustomert.CustD]
DoCmd.OpenReport stDocName, acPreview, , stWhere
Else
DoCmd.OpenReport "rptCustomerLabel", acPreview, ""
End If
End Sub

Thanks,

 

[MajP]

stWhere = "tblCustomer.yourYesNoField = True

 

[nyamrembo]

It's working....Thank you sooo much!

 

[nyamrembo]

Hi, the label report is not that stable. When I load the form and select some names for printing, the names come up on the report but the last selection is not coming up. For example, if i select records...1,2,3,4,5,6,7, I get 1-6 names to show on the report but the last name does not. Then if I decide to add some more customers to my selected list, then the whole report comes up. Any advice on what I could be missing?

Thanks again,
(-:

 

[MajP]

The last record may not yet be comitted to the database. The check is in the control but not saved to the underlying data. Try..

Me.dirty = false
now code to open report

 

[nyamrembo]

Forgive me but I quite didn't understand where I need to put the code.
Thanks,

 

[PHV]

Simply like this:

Private Sub cmdPrintReport_Click()
Me.Dirty = False
DoCmd.OpenReport "rptCustomerLabel", acViewPreview, , "tblCustomer.[i]yourYesNoField[/i] = True"
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886