I run a Filter by Form on my main form and get the results I'm looking for. But when I try to print the results only to a report format I made, it wants to print all the records in that format and not just the results of my filter. I need YOUR help. Thanks.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Printing Filter Report
- Thread starter skriseman
- Start date
I had a similar problem and found a neat solution in the Northwind database.
In the queries, you will find an "Invoices Filter." In the forms, take a look at the "Orders" form. On it there is a command button to print a single invoice. It will print whatever invoice you have open at that moment because it is tied to the Invoice ID. It's much slicker than using a filter because once you use it it's done and doesn't hang around waiting to be reset in the same way a filter does.
Using the Northwind model, I did this...
1. Made a new copy of the invoice query, added all the fields, and then added an additional InvoiceNbr column which has the criteria [Forms]![frmInvoice]![InvoiceNbr]
2. Made a second copy of the Invoice report, and named it rptInvoiceFilter so that when the command button was pushed, to print the single invoice, it goes to that report copy, because I had other query parameters built into the main report that weren't necessary for the single print copy.
You will find the code for the command button, that you put on the invoice form, in Northwind and all you have to do is modify it to suit the names of your form, query and report.
Hope this helps a bit.
Tom
In the queries, you will find an "Invoices Filter." In the forms, take a look at the "Orders" form. On it there is a command button to print a single invoice. It will print whatever invoice you have open at that moment because it is tied to the Invoice ID. It's much slicker than using a filter because once you use it it's done and doesn't hang around waiting to be reset in the same way a filter does.
Using the Northwind model, I did this...
1. Made a new copy of the invoice query, added all the fields, and then added an additional InvoiceNbr column which has the criteria [Forms]![frmInvoice]![InvoiceNbr]
2. Made a second copy of the Invoice report, and named it rptInvoiceFilter so that when the command button was pushed, to print the single invoice, it goes to that report copy, because I had other query parameters built into the main report that weren't necessary for the single print copy.
You will find the code for the command button, that you put on the invoice form, in Northwind and all you have to do is modify it to suit the names of your form, query and report.
Hope this helps a bit.
Tom
- Thread starter
- #3
Thanks for the reply. Excuse my ignorance but you mentioned the Northwind database and some of the forms in it. Where do I find this "Northwind database". It sounds like it would be VERY helpful to me and answer some of my questions. Thanks again for your help.
Northwind is a sample database that comes with both Access 97 and Access 2000. If you are using Office 2002 (XP) I'm not sure whether or not it's with it.
Anyway, do a search for North*.mdb and you should find it. It will probably be in c:\Program Files\Microsoft Office\Samples
If it isn't there, it probably means that you have to install it. So run your Office set-up, and add the Northwind database.
I don't know of any reason it wouldn't be with your Office program, but on the off chance it isn't you may be able to download it from the Microsoft Knowledge Base...although I haven't checked that out.
If you still have problems, send me another note.
Good luck.
Tom
Anyway, do a search for North*.mdb and you should find it. It will probably be in c:\Program Files\Microsoft Office\Samples
If it isn't there, it probably means that you have to install it. So run your Office set-up, and add the Northwind database.
I don't know of any reason it wouldn't be with your Office program, but on the off chance it isn't you may be able to download it from the Microsoft Knowledge Base...although I haven't checked that out.
If you still have problems, send me another note.
Good luck.
Tom
- Thread starter
- #5
- Thread starter
- #6
Hi Tom
Just to let you know that your advised helped with some of the issues I was working on but I still can't figure out how to get my report to print out the whole recordset result of a filter. I keep getting just one line item printed out and not the others(the whole found recordset). Any help would be appreciated.
Just to let you know that your advised helped with some of the issues I was working on but I still can't figure out how to get my report to print out the whole recordset result of a filter. I keep getting just one line item printed out and not the others(the whole found recordset). Any help would be appreciated.
skriseman
If I understand you correctly, you have a number of records in a form, and you want to be able to print just one record, which you have selected, on a report. The report is based on a query.
If that's the case, here's what I did (bearing in mind that my names will be different from yours)...
1. The first copy of my query was called "qryInvoice." I made an extra copy of the query and called it "qryInvoiceFilter"
2. In the second copy I put an extra column for the InvoiceNbr and put in this criteria "[Forms]![frmInvoice]![InvoiceNbr]"
3. I then made a second copy of the report and used the "qryInvoiceFilter" as it's record source.
4. I placed a Print command button on the Invoice form, to print the one record I had selected.
Now, if this is not what you are looking for, or what I have outlined doesn't help, post for me the following things and I will try and help further...
1. The exact names of the Form, the Query and the Report you are working with?
2. Copy and past in the code you are putting on either the Preview or the Print command button from your Form, and I will have a look and compare with what worked for me.
Tom
If I understand you correctly, you have a number of records in a form, and you want to be able to print just one record, which you have selected, on a report. The report is based on a query.
If that's the case, here's what I did (bearing in mind that my names will be different from yours)...
1. The first copy of my query was called "qryInvoice." I made an extra copy of the query and called it "qryInvoiceFilter"
2. In the second copy I put an extra column for the InvoiceNbr and put in this criteria "[Forms]![frmInvoice]![InvoiceNbr]"
3. I then made a second copy of the report and used the "qryInvoiceFilter" as it's record source.
4. I placed a Print command button on the Invoice form, to print the one record I had selected.
Now, if this is not what you are looking for, or what I have outlined doesn't help, post for me the following things and I will try and help further...
1. The exact names of the Form, the Query and the Report you are working with?
2. Copy and past in the code you are putting on either the Preview or the Print command button from your Form, and I will have a look and compare with what worked for me.
Tom
- Thread starter
- #8
No, the problem is that just one record is printing on the report. I have a database of about 12,000 people. I will then filter that database for a few key things. From that 12,000 the filter will give me a list of a number of people with that certain background. When I try to print out the list of peoples names in my report, all I get is the first person and the rest of the report is blank. What I need is a way of telling the report to print out the whole recordset and not just the first person. Thanks
Oh, so the problem is not as I thought before. My solution had to do with printing just one record. Sorry that I misinterpreted the problem.
I'm not sure what is keeping your program from including in the print-out all that you have filtered. Something obviously is.
Obviously, I do not know the setup of your database, so am groping in the dark a little. But from what you describe - wanting to filter out a few records that are for "people of a certain background" - it sounds possible that making a new query, and then putting criteria in a column, so that the query selected only certain records, might work better than a filter. For example, using the criteria "Like x" (whatever x is in your situation) and then print a report based on that criteria.
If you can be more specific with your information, such as giving me the fields and what you are trying to pull out, I can try to help further. And if you want, rather than posting something more here, you can send a message to my private e-mail address, which is twatson@sentex.net
Guaranteed there is a way to do what you want to do. It's just finding the right method.
Tom
I'm not sure what is keeping your program from including in the print-out all that you have filtered. Something obviously is.
Obviously, I do not know the setup of your database, so am groping in the dark a little. But from what you describe - wanting to filter out a few records that are for "people of a certain background" - it sounds possible that making a new query, and then putting criteria in a column, so that the query selected only certain records, might work better than a filter. For example, using the criteria "Like x" (whatever x is in your situation) and then print a report based on that criteria.
If you can be more specific with your information, such as giving me the fields and what you are trying to pull out, I can try to help further. And if you want, rather than posting something more here, you can send a message to my private e-mail address, which is twatson@sentex.net
Guaranteed there is a way to do what you want to do. It's just finding the right method.
Tom
I'm struggling with the same issue. The criteria will not pass to the report. I have Access 2000 but not QueryDef so may be that a library is off. I've read every FAQ I can find but none offer a solution. I can't use a Query because my selection criteria are complicated. The input comes from three list boxes on a form. The code is something like:
Set conADOConnection = CurrentProject.Connection
conADO.ConnectionString = CurrentProject.BaseConnectionString
'first part of strSQL true in all events
strSQL = "SELECT rep_company, discl_topic, discl_name," & _
"discl_period, discl_para, disclosure, [etc] FROM " & _
"aud_ltr_dat WHERE discl_cls_opn <> 'C'"
' now get portion of SQL statement relating to period
Select Case strPeriod
Case ""
' nothing to do here - pulling all periods
Case Else
strSQL = strSQL & " AND (discl_period = '" & _
strPeriod & "' OR discl_period = '2000All')"
End Select
' now get portion of SQL statement relating to topic
Select Case strTopic
Case "All"
strSQL = strSQL & " AND Mid(discl_topic, 2, 7) <> 'Boilerp'"
Case Else
strSQL = strSQL & " AND discl_topic = '" & strTopic & "'"
End Select
' now get portion of SQL statement relating to company
Select Case strCompany
Case "zAll"
strSQL = strSQL & _
" ORDER BY rep_company, discl_topic, [etc]"
Case "Subs"
strSQL = strSQL & " AND rep_company <> 'ACME'" & _
" ORDER BY rep_company, discl_topic, [etc]"
Case Else
If strCompany = "ACME" Then
strSQL = strSQL & " AND (rep_company = '" & strCompany & _
"' OR rep_company = 'zAll')" & _
" ORDER BY rep_company, discl_topic, [etc]"
Else
strSQL = strSQL & " AND (rep_company = '" & strCompany & _
"' OR rep_company = 'zAll' OR rep_company = 'Subs')" & _
" ORDER BY rep_company, discl_topic, [etc]"
End If
End Select
Dim objRSetRpt As New ADODB.Recordset
objRSetRpt.Open strSQL, CurrentProject.Connection, _
adOpenStatic, adLockOptimistic
I know the SQL statement works because I can force my way through with a series of MsgBoxes. But the report result is all the records print if the main table is the source or no records print if the main table is not the source. Can a code-built recordset be the source for a report? IF so, how? Much appreciated.
Greg Gol
greg.golazeski@exeloncorp.com
Set conADOConnection = CurrentProject.Connection
conADO.ConnectionString = CurrentProject.BaseConnectionString
'first part of strSQL true in all events
strSQL = "SELECT rep_company, discl_topic, discl_name," & _
"discl_period, discl_para, disclosure, [etc] FROM " & _
"aud_ltr_dat WHERE discl_cls_opn <> 'C'"
' now get portion of SQL statement relating to period
Select Case strPeriod
Case ""
' nothing to do here - pulling all periods
Case Else
strSQL = strSQL & " AND (discl_period = '" & _
strPeriod & "' OR discl_period = '2000All')"
End Select
' now get portion of SQL statement relating to topic
Select Case strTopic
Case "All"
strSQL = strSQL & " AND Mid(discl_topic, 2, 7) <> 'Boilerp'"
Case Else
strSQL = strSQL & " AND discl_topic = '" & strTopic & "'"
End Select
' now get portion of SQL statement relating to company
Select Case strCompany
Case "zAll"
strSQL = strSQL & _
" ORDER BY rep_company, discl_topic, [etc]"
Case "Subs"
strSQL = strSQL & " AND rep_company <> 'ACME'" & _
" ORDER BY rep_company, discl_topic, [etc]"
Case Else
If strCompany = "ACME" Then
strSQL = strSQL & " AND (rep_company = '" & strCompany & _
"' OR rep_company = 'zAll')" & _
" ORDER BY rep_company, discl_topic, [etc]"
Else
strSQL = strSQL & " AND (rep_company = '" & strCompany & _
"' OR rep_company = 'zAll' OR rep_company = 'Subs')" & _
" ORDER BY rep_company, discl_topic, [etc]"
End If
End Select
Dim objRSetRpt As New ADODB.Recordset
objRSetRpt.Open strSQL, CurrentProject.Connection, _
adOpenStatic, adLockOptimistic
I know the SQL statement works because I can force my way through with a series of MsgBoxes. But the report result is all the records print if the main table is the source or no records print if the main table is not the source. Can a code-built recordset be the source for a report? IF so, how? Much appreciated.
Greg Gol
greg.golazeski@exeloncorp.com
Hi Greg
This is written in ADO
Querydefs are in DAO
I am not a programmer so most of the code i use is simple or plagiarized
ADO is new to me
finish work three yers ago and forgotten most of what I knew (back to the stage of threatening this ****** Computer
But if your database could be written in DAO there is code to let you search on 1 to 30+ fields to create a querydef which would then give you a record source.
But there is a few programmers out there who could answer your question in ADO
This is written in ADO
Querydefs are in DAO
I am not a programmer so most of the code i use is simple or plagiarized
ADO is new to me
finish work three yers ago and forgotten most of what I knew (back to the stage of threatening this ****** Computer
But if your database could be written in DAO there is code to let you search on 1 to 30+ fields to create a querydef which would then give you a record source.
But there is a few programmers out there who could answer your question in ADO
Hymn, thanks for the DAO clarification. I have the MS web site white paper on the differences. Maybe I can work backwards. Seems bizarre that a simple concept is so complicated. The VBA for Dummies and Access Bible both recommended ADO so that I tried to stay with it. It wasn't until I saw the Connection code on this site that I could get it to work. There are errors in the MS stuff so copying verbatim is no guarantee. This object-based programming seems overly technical to me. Thanks again!
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question