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

 

[THWatson]

Ian
Thanks for your thoughts.

But setting the text box to Can Grow, and having it exactly the width of the page, sure enough doesn't do what you suggest. It's probably because the text box is being fed a string...and it's an unbound report.

As for getting a new line, I am using vbNewLine which, as nearly as I can determine, is the same as vbCrLf, which is also the same as combining Chr$(13) and Chr$(10).

Tom

 

[THWatson]

John
With one exception, I have this all worked out.

I am using the form approach. ListBox1 has the names of people in whose memory donations have been made during the past two years. ListBox2 is created by an SQL on the AfterUpdate event for ListBox1.

ListBox2 shows the donors' names and also the Fund, so if there are two or more funds involved, the secretary can select only those pertaining to one fund and print those, and then select the second bunch.

ListBox2 also has an <ALL> possibility at the top of the list. This is created in the SQL using a UNION SELECT statement.

As nearly as I can see, all of this works pretty well.

The only "exception" is the business about putting part of a name on one page and the remainder placed at the bottom of the next page. I'm not sure there's a way to beat this without ending up with some pretty ugly line formatting, so I may have to live with that.
Well, there is one way to beat it. The secretary could push down enough lines to make it work out right. This is a little extra work on her part...but, hey, I have saved her tons of time already!

John, a great big THANKS AGAIN for helping me to work my way through this. Even though I ended up not using your ConcatField function, I have it for future reference, and it would have been great had it not been for the parameters it had to negotiate.

Tom

 

[BoxHead]

Tom,

I've really enjoyed this and I've learned a lot so no need for thanks.

As far as the issue with keeping the names intact, I have to go back to me earlier post. I don't know of any event that could notify us of a detail section extending onto the next page so the only way to be sure would be to break the string with a linebreak as the string is built.

If you don't want the function to add up the physical length of the string, you could use 46 characters as a break point. To get 46, I used the widest capital characters and widest small case characters and figured there would be 12 caps at most with a spaace before each except for the first (11) and that would leave room for 23 of the widest small-case letters.


I'm not clear on the SQL to build the string from the list box. I know I've seen it before but it's been awhile. You would include an if...then state to check the length of he current line and add line breaks as necessary.


Okay, okay... as I type this, it occurs to me that if you can figure out which line you are on and you know at what point you are starting the printing, you would be able to calculate this behind the scenes of the form.

As the secretary selects the names, a function could run to see if it's spilled onto the next page. If it has, a message could come up telling her that. She can decide if she should change the start point, deselect the name and have it continue as part of the group on the next page, or move the whole entry to the next page.

Think on that and let me know if it's worth pursuing.

HTH




John

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

 

[THWatson]

John
Appreciate your thoughts!

Here's the order in which things happen...
1. The names are selected and the string built.
2. The Preview, or Print, command says, "Okay, let's run the report."
3. Code on the OnOpen event for the report runs the InputBox form in which the secretary instructs the # of lines to push the printing down the page.

So, yes, I suppose the secretary would be able to calculate the # of inches from where the printing starts to the bottom of the page. But that, in itself, isn't going to determine whether or not a name is going to split at the bottom of the page.
She will be able to see, in Preview mode, whether this happens. Would a function be better? Not sure.

In any event, I wouldn't have the foggiest idea how to write a function for that.

I'm also not at all clear on how to write a function to check the line length, if that were to to be tried.

Some interesting facts about the printing...
1. vbNewLine is 1/8" inch, so there are 8 of those in one inch.
2. Using Eighteen point Old English MT font, a full Detail section is 25 lines.
3. In inches, a full Detail section is 7.25 inches. That means that a line of 18 point Old English type is .3 inch.
4. In terms of vbNewLine values, a full Detail section is 58 lines.
5. Including spaces, and & characters that go between names (such as Jim & Mary Brown), the widest line would appear to be maybe 57 characters...but that depends on how many of the widest characters, and how many of the narrowest characters, there are in the line.
6. A page has 1 inch margins, left and right.

I don't think that de-selecting a name and having it print as part of a group on the next page will work. Here's why...
The printing begins with a Header line that actually in every case I have tested becomes two lines...
In memory Of Mary Jones, donations were made to the Heritage Fund by...
Sam Smith, Fred Arbuckle, etc. etc.
To run a second batch which showed the exact same Header lines would look odd. In cases where donations are made to 2 separate funds, those are printed as separate batches. So the page ends up looking like this...
In memory Of Mary Jones, donations were made to the Heritage Fund by...
Sam Smith, Fred Y Sue Arbuckle, etc. etc.
----leave a 3/4 inch or so blank space-----
In memory Of Mary Jones, donations were made to the Memorial Fund by...
Mark Huggersmith, Jake & Judy Trembley, etc. et.

So, knowing that information, if a function could check that out, it could be worthwhile. I just don't know.

As for your comment I'm not clear on the SQL to build the string from the list box. I know I've seen it before but it's been awhile. You would include an if...then state to check the length of he current line and add line breaks as necessary. Here is the code for that. The code goes on the AfterUpdate event for the list box (donors and the fund they donated to) that is built after a name (in memory of) is selected in the first list box.

Private Sub List2_AfterUpdate()
Dim frm As Form, ctl As Control
    Dim varItm As Variant
    Dim strList As String
    Dim i As Integer
    
    strList = ""
    Set frm = Forms!frmMemorialBook
    Set ctl = frm!List2
    
[b]If ctl.Selected(0) = True Then[/b]
For i = 1 To ctl.ListCount - 1
  ctl.Selected(i) = True
  Next i
  
  For Each varItm In ctl.ItemsSelected
    'strList = Replace(strList, "<ALL>, ", "")
    'strList = strList & ctl.Column(0, varItm) & ", "
   strList = Replace(strList, "<ALL>, ", "") & ctl.Column(0, varItm) & ", "
   Next varItm
        If Len(strList) > 2 Then
    strList = Left(strList, Len(strList) - 2)
    Me.txtFund = ctl.Column(1, 1)
    Me.txtSelected = strList
    Me.Text19 = "The following " & ctl.ItemsSelected.Count - 1 & IIf(ctl.ItemsSelected.Count > 1, " names", " name") & vbCrLf & IIf(ctl.ItemsSelected.Count > 1, "have been selected.", "has been selected.")

    End If
    End If
    
[b]If ctl.Selected(0) = False Then[/b]
    For Each varItm In ctl.ItemsSelected
       strList = strList & ctl.Column(0, varItm) & ", "
   'Me.txtFund = ctl.Column(1)
   Next varItm
        If Len(strList) > 2 Then
    strList = Left(strList, Len(strList) - 2)
    Me.txtFund = ctl.Column(1)
    Me.txtSelected = strList
Me.Text19 = "The following " & ctl.ItemsSelected.Count & IIf(ctl.ItemsSelected.Count > 1, " names", " name") & vbCrLf & IIf(ctl.ItemsSelected.Count > 1, "have been selected.", "has been selected.")

   End If
   End If

End Sub

As I indicated two messages ago, I included in the SQL that builds this second list box a piece that makes <ALL> show at the top of the box. That is Column(0,0)
So you will note that I have bolded two lines in the code. One runs if <ALL> was selected, the other if it wasn't selected. This is, of course, a Multi-Select list box.

Anyway, that's where we are at. It has been a major piece of work, but I am about 98% pleased.

If you are interested in looking at the whole database, we could probably work that out.

Best regards.

Tom

 

[BoxHead]

Tom,

Where does the report get the data? Does it link to the form or is there a query that is built from the form input?

If the report has a query, it seems you might be able to use grouping format properties to keep the names together on a page. Even if it mean that the query returned a clumn with the donees name that was represented on the form with a text box whose back and border were invisible and whose forecolor was white. The text box wouldn't have to show/print on the report, but it would let you apply the appropriate grouping and keep together properties.

Just a new thought.

John


John

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

 

[THWatson]

John
In the Detail section of the report, there are 4 things...

1. a text box that picks up "MySpc" from the Report Header. (this is hidden)

2. a text box with the following Control Source...
="In Memory of " & [Forms]![frmMemorialBook]![List0].[Column](0) & ", donations were made to the " & [Forms]![frmMemorialBook]![txtFund] & " by..."
[List0] on the form, of course, is the list box that contains the names of people in whose name donations have been made.
[txtFund] on the form, is a text box that shows the Fund to which donations were made. This is generated from the AfterUpdate event on List2.

3. a text box with the following control source...
=[Forms]![frmMemorialBook]![txtSelected]
[txtSelected] is a text box in which the string of names, strList, is generated from the AfterUpdate event on List2.

4. a text box with the following control source...
=[Forms]![frmMemorialBook]![txtDateToShow]
[txtDateToShow] is created from the AfterUpdate event on List0.

The report is "unbound." So there is no query behind it. Even, in the early stages, when we were trying to build the string from the concatField function, the report was unbound.
I suppose it could work to build a query from the form input. However this in itself would not guarantee the names would be kept together on one page. The reason is that, in one current situation, there are 140 names to be printed. This takes up a little bit over 2 full printed pages. It is unusual to have this many donations in memory of one person, but it's there.

Does that clarify? Or point somewhere?

Tom

 

[BoxHead]

Tom,

Probably the best thing would be to create a small form that opens with the Report Activate event. This small form could have a button "Print Report" and a button "Change Text Location".

The event on the second button would simply close and reopen the report asking again for the location of the text.

This would make it a bit easier for the secretary to preview the report without having to remember about closing and reopening.

Does that make sense?



John

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

 

[THWatson]

John
It's worth a shot. I'll have a look.

Tom

 

[THWatson]

John
I tried this. I created a pop-up form with two buttons - <Print> and <Change Text Location>. Here's what I encountered.

If the <Change Text Location> button is pressed, what happens is this...
The report is closed. But this closes the InputBox form from which you choose the # of lines to push down, which then reopens...and when the # of lines to push down is entered you go back to the form from which the names are selected, and you're back at square one.

The problem is that the InputBox form opens on the Open event for the report. So we have two forms being opened by the report - the Input Box form on Open, the new form on Activate.

It becomes like a dog chasing its tail.

I went today and looked over all the pages from the last few years. In the great majority of cases, names all fit on one page, so there is only the occasional time when names will split onto two pages. Therefore, I think maybe it's not worth spending any more time on.

The new form approach came as close as we might have got to cracking this nut, and I appreciate the suggestion.

The secretary is thrilled with what we did for her.

Thanks again, John.

Tom

 

[ProgramError]

Tom

Don't use an input box! use a form with a textbox or even have an invisible box on the 'print'/'change text location' form which appears when the 'change text location' command is selected.
In the on click event of the 'change text location' button put something like

me!textbox.visible = true
docmd.close "reportname"


use the value in the textbox in the new report as appropriate.


Ian Mayor (UK)
Program Error
Programmers do it one finger at a time!

 

[THWatson]

Ian
Actually, I do use a form with a text box. That's why I called in an "Input Box form". I'm sorry for the confusion.

Just had a thought about this particular post. I wonder if there is a limit to the number of replies...or if it gets to a certain point it becomes a candidate for entry in the Guinness Book of World Records !!

Thanks, Ian, for your thoughts. Appreciated.

Tom