Print at varying specific point on page 2

[THWatson]

Using Access 2000

Is it possible to print fields, in the Detail section of a report, at a specific location that is selected by the user?

Here's the scenario...
1. It's a page to record Donations in a Memorial book. The pages are special paper. The donations are recorded in the database.
2. Say that the following entries are to be pulled from the database and printed.
In memory of Violet Smith, donations were made by Sam Jones, Fred and Wanda McGruber, Tilly and Mac Franklin.

3. Next say that, a month later, the following entries are to be pulled from the database and printed...but the printing location to be 3/4 inch below the preceding entry lines.
In memory of Timothy Vector, donations were made by Brooke and Marcus Weldon, Jack and Gerta Hopper, etc.

4. The same scenario would repeat itself, in successive printings, until the page was full.

Obviously, with as few entries as I have used as example, there's not that much additional typing, so it can easily be done in Publisher or Word. However, there is the occasional situation where there are many more donations (a current situation has 145 donor names to be printed), so it would be neat if the user could determine where on the page to start printing.

Can this be done?

Tom

 

[BoxHead]

Tom,

If I'm understanding your post, you want to print part of a report on a piece of special paper and at a later date or time, put that same piece of paper into the printer and print ONLY additional information below the already-printed info?

Two thoughts:

First, if it's really easy in Publisher or Word, run a query to pull your text and copy and paste it into Word. Forget the typing.

Second, if you could add a [PrintedDate] field to your table and query, you can use the OnFormat event of the detail to set the forecolor of all controls to white if thay have already been printed. You'd just print the last page of your report each time and it would feed through the printer until it got to the record/s that haven't been printed.

If Not IsNull([PrintedDate]) Then
txt1.ForeColor = vbWhite
txt2.Forecolor = vbWhite
txtEtc.ForeColor = vbWhite
Else
txt1.ForeColor = vbBlack
txt2.Forecolor = vbBlack
txtEtc.ForeColor = vbBlack
End If


If you have a header or footer on your report, this would mean pre-printing that on the special paper and setting up your report to only print the detail section.

You would probably want to manage this from a form so your user could verify what is printing and/or reprint sheets that got jammed, low-toner, or otherwise destroyed.

HTH




John

Use what you have,
Learn what you can,
Create what you need.

 

[THWatson]

John
Some good ideas there.

What I meant when I said it could easily be done in Publisher, or Word, that's how it has been done to date. But it seemed to me rather ludicrous for the user to have to type in 145 names when that information is already sitting there in the Access file.

In any event, I will try your suggestions.

Thanks.

Tom

 

[BoxHead]

Thanks for the star, Tom.

Always appreciated.

Here's another thought if you only want to run the report for one record at a time. You know what will work best for your users.

In the Report Header, put a textbox with transparent border and back. Be sure the text box and the report header 'Can Grow' = Yes and 'Can Shrink' = No.

Declare a public string (mySpc) and set the Control Source for the text box to

=[mySpc]

The following code will allow you to enter the number of lines you will move the detail section down a page.

Option Compare Database
Public mySpc As String

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
Dim myInt As Integer
mySpc = ""
For myInt = 1 To InputBox("Enter # lines to push down")
mySpc = mySpc & vbNewLine  
Next myInt
End Sub

You would probably want to print up a template for the person printing so they can know what value to enter. You could make the template by using

mySpc = mySpc & vbNewLine & myInt

in the For Next statement.

Thanks again,

HTH




John

Use what you have,
Learn what you can,
Create what you need.

 

[THWatson]

John
I think this might be the better way to go. I thought there was a method, somewhere, for skipping labels, so that if part of a label sheet had been used, the user could indicate where to start printing on a page. Your suggestion here is similar to that, I think, and maybe better. I like this idea but will have to see. It's a matter of calculating the lines properly.

Actually, it's not a matter of "printing one record at a time." What is being printed is not records but data, in a string, from one field in a number of records. I'll explain.

1. The database is FrontEnd/BackEnd, and is a database for a church. tblTrinity keeps track of members. tblGivings keeps track of donations in a number of categories, one of which is Memorial donations.

Jack Smith and Sam Jones are records in tblTrinity. Both of those make a donation in memory of Judy Spratt. What is needed is to pull out their names and put them in a string, so what is printed is "Jack Smith, Sam Jones."

2. Additionally, there is a table called tblMiscellaneous. This keeps track of donations from non-members, again in a few categories.

Abel Durnsford and Milly Green make donations in memory of Judy Spratt. So their names need also to be pulled and added to the string, so that the entire string printed is "Jack Smith, Sam Jones, Abel Durnsford, Milly Green."

The problem with adding a [PrintedDate] to the table and query, as you suggested in your first post, is that would have to be added to both tables mentioned above.

I am also experimenting with doing a Mail Merge into Publisher. The interesting thing I have found here is that Publisher will only merge data from tables...it won't pull data from queries. I can get around that by using a Make Table query.

The other possibility would be to work out a way to collect the names in a string and then, again as you suggested in your first post, simply copy the string created in Access and paste it into either a Publisher or Word document.

Ideally, I guess, I wouldn't mind keeping it all within Access, as it's fewer steps for the secretary to use.

Anyway, those are my musings. Thanks for your interest.

Tom

 

[THWatson]

John
From quick testing, I think your MySpc method will work.

I did have to put the [MySpc] text box in the Detail section rather than in the Report Header section to make it work.

I am also wondering this...
I think that 6 lines is pretty much equivalent to 1 inch. If the user knew that the space to be left was 2 inches, she would enter a value of 12 lines. That explanation could be added to the Input Box, and eliminate the need for a template.

Tom

 

[BoxHead]

Tom, I was writing a reply while you posted. I'm glad to hear it could work.

Eliminating the template would be nice. Since you've got a grip on how many lines = how many inches, could she tell you how many lines are already printed on the page? I always worry when someone is estimating size.

Here's the post I was writing:

If you don't already have a simple way "to collect the names in a string" the following function might work for you.

It's like a DSum with text so it's slow to run with a lot of records, but it might work well here.


The function should be pasted into a new module and called from a query.

DonorList:ConcatField("[DonorNameField]", ", ", "nameOfYourQuery", "[DoneeNameField] = " & """" & DoneeNameField & """")

________________________________

Public Function ConcatField(MyFld as String, MyBreak As String, _ 
TblQryName as String, Optional MyCrt As String) As String

Dim myString As String, strSql As String
Dim db as Database, rst as Recordset

If myCrt = “” Then
StrSql = “SELECT “ & myFld & “ FROM “ & TblQryName & “;”
Else
StrSql = “SELECT “ & myFld & “ FROM “ & TblQryName & “ WHERE “ & MyCrt & “;”
End If

Set db = CurrentDb
Set rst = db.Openrecordset(strSql, dbOpenDynaset)

Do Until rst.EOF
If InStr(myString, rst.Fields(0) & MyBreak) = 0 Then
MyString = myString & rst.Fields(0) & myBreak
End If
Rst.MoveNext
Loop

If Len(myString) > Len(myBreak) Then
ConcatField = Left(myString, Len(myString) – Len(MyBreak))
Else
ConcatField = myString
End If

End Function

This should give you a field for copying and pasting, etc.

John

John

Use what you have,
Learn what you can,
Create what you need.

 

[THWatson]

John
I was fooling around with a similar method, but the code kept conking out. (see my new post in the Modules section of this forum)

As for your code, there are two things...
1. The following line won't compile
ConcatField = Left(myString, Len(myString) – Len(MyBreak))

2. The code conks out on the following line
Set rst = db.Openrecordset(strSql, dbOpenDynaset)
exactly the same as in the method I posted.

With your help, I'll get this sooner or later.

Tom

 

[BoxHead]

I'll take a look at your post and see if I can figure out the 'conking' sound (my car does that sometimes, too).

As for the line not compiling, delete the minus sign and retype it. Sometimes in copying and pasting from these forums, a minus sign gets read and written as an em-dash.

HTH


John

Use what you have,
Learn what you can,
Create what you need.

 

[THWatson]

John
You were right about the minus sign being interpreted as an em dash. Now that line is fine.

But now the code fails , stating there is an error in the FROM clause.

I'm getting bleary eyed from looking at this, and can't find stuff now.

Has PHV raised an issue in the other post, when he asks if this is a paramaterized query? It is. The user selects a Year from which to pull records. Does this have to be included in the module somewhere?

Tom

 

[BoxHead]

Tom,

I think PHV is on top of it.

I put your code amd mine into a query on a table and they worked fine.

I put your code and mine into a query on a saved query and they worked fine.

I changed the query to ask for a parameter and I got the same error message you received.

Can you call the funtion in the query itself and then filter the report on the parameter?

For the issue with the FROM clause, I think the differences between your function and mine are causing confusion. With mine, you have to include the brackets for the field name when you call the function, we have the field names and recordsets in reverse order from each other, and with concatfield you have to include the break character between the field name and the recordset name.

No wonder you're bleary eyed.

Try putting

MsgBox strSql

in the function right after declaring the strSql value to see what it is reading.


Let me know.


John

Use what you have,
Learn what you can,
Create what you need.

 

[THWatson]

John
PHV may well have been on top of the problem. I wish he had indicated something about how to fix it.

From your code, here is what shows as the strSQL...
SELECT [CreditTo] FROM ;
with a run-time error 3131

From the other code, here is what shows as the strSQL
SELECT [CreditTo] FROM [qryMemTest]
with a run-time error 3061, too few parameters

I'm not clear what the third parameter should be.

I am wondering about changing the qryMemTest into a Make Table query, running the query from code in the Module, and then referencing the tblMemTest in the report. Actually, I tried this, and it works except for the fact that I get 3 rows of concatenated records rather than just one.

OY !!

Tom

 

[BoxHead]

Tom,

The error is coming from the fact that the query asks for a parameter when it runs. I believe it has something to do with the order of locking records and opening recordsets. I don't think we can solve it as long as the query asks for the parameter.

I think we can solve it by putting the parameter into the function call.

First, from the msgbox results, it looks like the separator may be missing in the statement.

Concatfield("[CreditTo]", ", " ,"qryMemTest")

Make sure you have that part of the function and see if the query name shows up in the msgbox.

Next (if that worked) you can include the parameter in the concatfield criteria instead of in the query:

Concatfield("[CreditTo]",", ","qryMemtest","[YourYearFld] = " & [Enter a Year Please])

Be sure to take the parameter statement out of qryMemTest.





John

Use what you have,
Learn what you can,
Create what you need.

 

[THWatson]

John
I will try what you suggested. I might not get too much more done today as I have to leave shortly for a wedding rehearsal.

Thanks for all your help. I wish I could stick in another star, but it won't let me.

I'll keep you posted.

Tom

 

[BoxHead]

Tom,

I'm marking the thread to let me know when you get back to it.

You might want to close out the thread in the modules forum - or not. Your call.

I'm psyched about this. You're going to end up with a report that asks the user to enter the criteria and where to print it on the page. That's cool.

I wrote Concatfield a few years ago and have tweaked it a bit here and there, but I never even thought about putting the criteria in as a parameter request. When I saw that work in the test report I bult today, that was worth a lot more than a star. Thanks for that.

Talk to you when you're back at it.

John

John

Use what you have,
Learn what you can,
Create what you need.

 

[THWatson]

John
I came home and started tinkering with this again.

My first step was this...
1. Use a form that selects the year and drives the report
2. Reference the control in the form in which the user inputs the year. This is actually the way I normally do things, and this should bypass the parameter being called for in the query.

What happens when I press the command button to run the report is (a)I am asked for the number of lines to move down the page...that is what is expected, and then (b) the code breaks down, giving me a run-time error 3061, too few parameters.

The strSQL value is
SELECT [CreditTo] FROM qryMemTest;
which appears to be correct, so I am not clear what parameter I am missing.

So then I tried this as the expression...
=ConcatField("[CreditTo]",", ","qryMemTest","[YearToCheck] = " & [Forms]![frmYearChecker]![txtYearPicker])
with the same result as two paragraphs above (a) request for number of lines, and (b) run-time error 3061.

The strSQL for this formulation is
SELECT [CreditTo]FROM qryJanuaryToDecemberMemorial WHERE [YearToCheck] = 2005;

That's where I'm at so far. I can get the strSQL correct, but don't get the desired result.

My next step will be to remove the parameter altogether and request it when the report runs.

Tom

 

[THWatson]

John
If I remove the parameter altogether and request it when the report runs, I get the reques for each record that is being pulled.

Since it's a string that I want, then somehow the records aren't being put into a string.

Think I will give up for tonight.

Tom

 

[THWatson]

John
You may gather that I have a bit of a persistent bent, so I kept working on this puzzler. I finally got something to work. There is some tidying up to do yet, but here's the method I got to work.

1. I changed the query into a Make Table query.
2. In an unbound report, I put a text box in the Detail section, and that text box has the expression...
=ConcatField("[CreditTo]"," , ","tblMemTest")
3. The report is driven from a form.

There seems to be no way (well, maybe there is but I can't find it) to use the query in the report expression, without getting the run-time error that keeps looking for an additional parameter.
I have tried removing columns from the query, to try and find the offender but without success.

I think the problem may be this. The query is made up from 3 different tables. And all 3 of those tables need to be in the query in order to pull the correct results. And there is the parameter issue (calls for the year) but entering the year via a form should avoid a problem there.

So it would seem as if making a table to provide the data for concatenation is the way to go.

I have a couple of additional steps to take to get the final result...
1. Bring in data from another table into the final result, as memorial donations from non-members also have to be included. I will probably do this with a Union query.
2. Build a form by which the user can select the person in whose name the memorial donations were made, so that entries aren't improperly printed twice.
3. Maybe something else I haven't yet thought of.

But, and you may be interested in this...you mentioned, away back in a very early post, about making up a template sheet. There is a fancy border in the Page Header and Page Footer. I figured out a way so that if the printing starts on an already partially printed page, those borders will not print. They will print, however, on Page 2 and any successive pages in the printing moment at hand.
Putting code on the Format event of the Page Header and Footer does that...
If Me.Page > 1 Then
Me.OLEUnbound0.Visible = True
etc.

And EUREKA...did you know this...if the printing goes to a second page your Input Box asks for "How Many Extra Lines to push down?"

I'll lick this beastie yet. If you have any other thoughts, ideas, I would welcome them. I still don't quite understand why we can't get things to run from the query.

Tom

 

[BoxHead]

Tom,

Good work! (and a lot of it).

I think I'd have to agree with you on the make table issue. That is how I used to do it until yesterday when we found the parameter could be in the concatfield statement. This morning (some of us still sleep) I learned that the parameter could only be in the concatfield statement if the report was bound. To keep the report from returning a result for each record in the table, I created a table with one field and one record and bound the report to that, but that seems pretty pedestrian to me.

My only concern with the make-table is that the aggregate functions like concat2/field take a long time to run and if that's included in the make-table query, it may slow things down quite a bit. Let me know if that's an issue. Can the form be used to get the year and the name at one time?

If you want to get rid of the "How many extra..." question on pages after page one, I think you're already onto the answer with the way you handled the border. I changed my code as below and eliminated the question.

If Me.Page > 1 Then
myInt = 0 [COLOR=green]' or whatever default value you want[/color]
Else
For myInt = 1 To InputBox("Hold the existing sheet up to the template and enter the last number at which text is printed.", "SELECT TEXT LOCATION")
[COLOR=green]'To create a template report showing line numbers add in "& myInt" in the For Next code[/color]
mySpc = mySpc & vbNewLine [COLOR= green]'& myInt [/color]
Next myInt
End If

I think the only way to avoid the parameter issue would be to use CreateQueryDef in VBA after the parameter is provided by the user. I'm not sure of this, but I know that Access allows you to do some things with a saved query that you cannot do with a dynamic query. When a saved query asks for parameters, I think it falls somewhere in between saved and dynamic. The CreateQueryDef should dynamically save the query.

Keep me posted. I'm very interested to see how you get this resolved.






John

Use what you have,
Learn what you can,
Create what you need.

 

[THWatson]

John
Interesting that the parameter can only be in the concatfield statement if the report is bound. Something new to learn every day! I think I might take a run at the CreateQueryDef solution and see how that works. If not, I can create code to run the MakeTable query and reference that table in the concatfield statement...as we know that works.

As for this taking a long time to run, I don't think that will be an issue. At least, it isn't on my computer. We'll see.

I haven't developed the form yet where the secretary will input the name, but I'm sure that the form can be used to get both the name and the year.

A couple of interesting things...
1. The "How many extra lines..." question after page 1 doesn't always show up. I haven't added your solution to that yet, but noticed it doesn't always appear. Wonder why.

2. My calculations indicate that there are precisely 7.25 inches between the Page Head and Page Footer, that being the height of the Detail section when expanded. That means that there are about 7.25 lines per inch. I have tried changing the height of mySpc but that doesn't make any difference...the line value in the Detail section is 53.
So I am wondering if there is a way, through code, to indicate the height of mySpc. It would be nice if the user knew that a value of 6, for example, was equivalent to one inch. And the user will always want to start printing a new entry at least 1/2 inch, and maybe 3/4 inch, below the last printed line.

By the way, this little piece of work is a small add-on to a database upon which I have already spent countless hours. But isn't it interesting that some of these small additional pieces can consume so much time and thought!

John, I really appreciate your help. If you have any additional thoughts or comments, shoot.

I'm off to conduct a wedding this afternoon. I'll keep you posted as to my progress as it goes along.

Tom