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

 

[ProgramError]

Hi folks

As a matter of interest , are the positions predetermined? That is, are they spaced evenly down the page and have the same number, plus or minus one line, of lines for each donation entry?



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

 

[ProgramError]

Hi again folks,

As another matter of interest, Is the donation page the last page, say... of a book or are there several diffent pages to which the donation strings need to be added. If its just the last page of a book, then why not save the position of the last last enter within the database itself. This will elliminate the need to input the print starting position each time.

Once the page has been filled reset the pointer to the first position for the next page.

I hope you understand what I'm getting at.


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

 

[THWatson]

Ian
The pages are single pages that once printed go in a Memorial Book. The Detail section of a page would look like this...

In memory of Violet Smith, donations were made by Sam Jones, Fred and Wanda McGruber, Tilly and Mac Franklin.

In memory of Timothy Vector, donations were made by Brooke and Marcus Weldon, Jack and Gerta Hopper.

and so on, down the page...

The names of the donors are collected in a string to be printed when necessary. So there aren't individual records to be printed.

The printing would always be done on the last page, so saving the print position of the last printing would work. I'm not sure how to set that up. Could you amplify?

Thanks.

Tom

 

[THWatson]

John
I have encountered problems...

Preview mode is fine...Print is not
In Preview, the pages look properly formatted, but when you Print everything changes.
For example, let's say I select a value of 12 lines in the Input Box, what I want to have happen is the Detail section gets pushed down 12 lines. What is happening when you Print is that the Report Header gets pushed down 12 lines.
There is a label and a fancy border in the Page Header, so this section just gets pushed further down the page. That would be acceptable if the things in that section didn't print on Page 1 (if the myInt value is > 0) and I have code in the Format event to deal with that. Again, this works perfectly in Preview but not in Print.
I tried adding the code to the Print Event for the sections, but that doesn't make any difference to the printed results.

Problems with mySpc
Your original instructions said to put the mySpc text box in the Report Header, which I did. I also had to put a small text box in the Detail section, with its control source set to =mySpc. If I didn't do this, things didn't work properly. So I tried moving the coding into this section as well. That didn't work.

InputBox run-time error
When I press the Cancel button on the InputBox, I get a run-time error 13, mismatched data. What is supposed to happen when the Cancel button is pressed is an empty string results. I tired adding Cancel=True to the code but this didn't solve things.
I am therefore wondering if there is a problem here somewhere.

The long and the short of it...I'm treading water.

Tom

 

[THWatson]

John
Well, I have persisted...and am part-way home.

I had to remove the mySpc text box from the Report Header completely, in order to get the Print mode to match the Preview mode.

I also declared myInt in the general declarations at the top of the module. And then put a text box, txtShowInt, to reference the number of lines that are to be pushed down. Then I can reference txtShowInt in both Page Header and Page Footer to determine what needs to happen.

I also had to move the code that you showed for the Print Event in the Report Header to the Format Event for the Report Header (below will show code still in that Print Event, as I have only commented it out so far).

One annoying thing is that when I display the report in Preview mode, and then ask it to Print, the InputBox comes up again, and you must enter the same value that was entered in Preview mode in order for things to work. No matter what I do, I can't seem to get around this.

An interesting thing is that the integer value derived from the InputBox is always 1 more than what was entered. Enter 1, you get 2. Enter 24, you get 25.
Seems to me I read somewhere about a bug in InputBox. Maybe that's it.

And there's still the issue of the Cancel button breaking the code. I did add Error Handling to the Report Header Format event, so that if Cancel was pressed the Integer value would revert to 0, and that solves it in a way.

Option Compare Database
Option Explicit
Public mySpc As String
Public myInt As Integer

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
If Me.Page = 1 And Me.txtShowInt <= 1 Then
Me.OLEUnbound1.Visible = True
Else
Me.OLEUnbound1.Visible = False
End If

If Me.Page > 1 Then
Me.OLEUnbound1.Visible = True
End If
End Sub

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
If Me.Page = 1 And Me.txtShowInt <= 1 Then
Me.OLEUnbound0.Visible = True
Me.Label2.Visible = True
Else
Me.OLEUnbound0.Visible = False
Me.Label2.Visible = False
End If

If Me.Page > 1 Then
Me.OLEUnbound0.Visible = True
Me.Label2.Visible = True
End If
End Sub

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
Dim myInt As Integer
   On Error GoTo ReportHeader_Format_Error

mySpc = ""
myInt = 0

If Me.Page < 2 Then
For myInt = 1 To InputBox("Enter # lines to push down")
mySpc = mySpc & vbNewLine '& myInt

Next myInt
MsgBox myInt
Me.txtShowInt = myInt
End If

   On Error GoTo 0
   Exit Sub

ReportHeader_Format_Error:
    If Err.Number = 13 Then
    Me.txtShowInt = 0
    Exit Sub
    Else
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ReportHeader_Format of VBA Document Report_rptMemTest"
    End If
End Sub

Private Sub ReportHeader_Print(Cancel As Integer, PrintCount As Integer)

'Dim myInt As Integer
'mySpc = ""
'For myInt = 1 To InputBox("Enter # lines to push down")
'mySpc = mySpc & vbNewLine '& myInt

'Next myInt
End Sub

Anyway, I'm gaining on it. And a lot less frustrated than I was a couple of hours ago. Just not there yet.

Tom

 

[BoxHead]

I think the error on the input box is from the fact that we've declared myInt as an integer and cancel is returning a zero-length string.

Tied to this issue is the fact that the code I wrote adds a second line when myInt = 1. Not the desired result.

I think declaring myResponse or myRsp as a public variable might give you better control on the error-handling and resolve the extra line issue.

Dim myInt As Integer, myRsp As String
myRsp = InputBox("Enter # lines to push down")
If Not IsNumeric(myRsp) Then
myInt = 0
Elseif myRsp < 2 Then
myInt = 0
Else
For myInt = 2 To myRsp
mySpc = mySpc & vbNewLine
Next myInt
End If

I think there is a gap in my understanding of how you will be using this. I had imagined that you would have a printed document say 20 and a half pages long and you would want to add text to the bottom half of page 21. In my mind, you would run the query for the name of the person you wanted to add to the document and put page 21 in the printer. As far as Access is concerned, that is page one of the report. If the text carries on to the next page (page 22 in your world, page 2 in Access), it would format normally.

Reviewing the posts from the last couple of days, though, I realize that [YearToCheck] is a part of the query and I'm not sure why.

Tom, I know it may seem redundant, but could you explain what I'm missing here? Are there two different situations in which this report would be generated: one batch update and one with newly-added information?

So you know, I have a report in which I would want to use this function occasionally and I'm planning on adding a button to the form to allow the user to select the print location on the page. They can print normally or they can click the new button and a copy of the same report with this new code will open and prompt them for the location.

I'm sorry this is taking so long, but I think you will still end up with something that works, something that you can be proud of and something that lets you bring a lot more muscle to the next Access challenge you face.

Fight on!







John

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

 

[THWatson]

John
Thanks for the suggested code, to resolve the error and the extra line issue. I'll put that in.

As for your understanding of the way this will be used, you are bang on target.
The pages go in a Memorial Book. One at a time. So there may be 20 pages in there, or 100, doesn't matter. The page that is currently being printed will either be a fresh page, or it will be partially filled.

So let's take your example of there currently being 20 full pages, and page 21 is half filled. At the current sitting, the printing is to start part way down page 21. But, as you say, Access will see this as Page 1. And if the printing goes on to the next page that will format normally.

There's never a batch update. It's always new information being added.

Here's the reason for the [YearToCheck] parameter...
The database is for a church. One table, tblNewGivings, keeps track of Members' donations. These are in several different categories, one category of which is Memorial. The current and previous years donations are kept in the current stuff and then archived. But this means that there will always be 2 years of data sitting there (and there's a reason for that...it's tied in with year end receipts for income tax purposes).
When quarterly statements, or recipts, are issued it is necessary to be certain that the data is pulled for the correct year. Hence the [YearToCheck] parameter.
But your question is pushing me to re-think whether I need that for this particular situation. How often, if ever, might there be a case where there are donations for a particular individual in 2 separate years? The greatest likelihood would be some donations in December and some in January. But could I get rid of that parameter and still have this function properly? I'll run some tests on that. Might be it could even run better.

The situation is slightly complicated by the fact that there could be donations by non-Members in memory of the same person. These go in a separate table, tblMiscellaneousDonations, and receipts are handled separately because they are issued at the time rather than waiting until year-end.
So I do have to draw in the data from the two tables, tblNewGivings and tblMiscellaneousDonations, and I can easily pull them together with a Union query.

Thanks for raising this [YearToCheck] issue. Sometimes a person gets in the habit of doing it this way just because that's the way we have been doing it, and one needs a jolt to re-think. I'll let you know where that thinking leads me.

Sorry to be long-winded, but I am thinking as I type the reply.

One fresh issue...
Is there a way to manipulate the string we are pulling together so that where it breaks at the bottom of a page can be controlled?
For example, let's say that the bottom line was
Sam & Marge Jones, Bruce & Velma Hackett, Catherine E.
and the first line at the top of the next page was
Ralston, Jack and Myrtle Busby, etc.

Obviously, one complete name here is Catherine E. Ralston. It's a bit undesirable, in a Memorial book, to have half of that name on one page and half on another. But I don't know if there's a way to beat it.

So you are going to be using this same code in your own situation? Neat.

By the way, I did some reading today. You indicated, in an earlier reply, that the parameter can only be in the concatfield statement if the report is bound. What I read said that the problem lies with the fact that Jet cannot find the data when the parameter exists, and thence the run-time error indicating that a parameter is missing.

Tom

 

[BoxHead]

Hi, Tom.

It's late where I'm at so I'm about to turn in, but there are two things to share before that.

1.) The way I wrote concatField, it will not repeat the same name. It checks to see if the name and the separator character are already in the string. If they are, it skips it. That may be good for you or bad. I'm not sure if you want a name in there twice if someone made a second donation for someone in a second year.

2.) If you could determine a maximum number of characters for each line, a second function could parse out the result of concatield to throw in a line break every n characters. That should keep the names together.



HTH


John

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

 

[THWatson]

John
First, replies to your post immediately above...

1) No, I don't want a donor's name to appear a second time, so the way you wrote concatField works perfectly.

2) It appears that the maximum number of characters in a line would be 54. But this is a TrueType font (Old English, 18 point) on an 8 1/2 inch wide page, with left and right margins of 1 inch...so might the max characters depend upon how many "wide" letters such as "w" are in the line, and how many "narrow" letters such as "i" are there?

Secondly, regarding your new code to deal with the error handling and extra line issue, from your second previous reply...
a) This does resolve the error handling
b) This does reduce the number of lines pushed down by 1
c) The myInt value is still 1 greater than the value entered in the InputBox, but that probably doesn't matter

The second appearance of the InputBox when I go from Preview to Print is annoying. Here's the sequence...
1. Run the report in Preview mode
2. Enter the value in the InputBox
3. Press Print
4. The InputBox appears a second time, and the value that is entered that time is the value that will hold.
I always like the secretary to see what is going to be printed before she prints the page. But she is going to have to remember what she put in the InputBox when she Previewed, and I worry a little about that.

Further thoughts regarding the [YearToCheck] parameter
Yes, I can get rid of that. However, I am still going to have a parameter. Understandably, donations are made in memory of several people...but we will only select, for printing, one at a time. Even if the secretary selects the name from a ListBox or ComboBox on a form, this filter is still going to have to be sent to the query, so still a parameter. I think I will have to run this from a table (made by a MakeTable query) regardless.

By the way, here's the quote, from "Access Cookbook" by Getz, Litwin and Baron: When you run a parameter query from the user interface, Access can find the parameters if they have already been satisfied using a form and run the query. When you create a recordset from VBA, however, the Jet engine isn't able to locate the parameter references.
This has to do with a CreateQueryDef approach. The authors go on to say that this can be solved by adding the parameters into the subroutine that creates the QueryDef.

John, I feel as if I am taking far too much of your time on this. I am most grateful for your help.

I would be interested to know where you are located. I am in Guelph, Ontario, which is about 30 miles west of Toronto.

Tom

 

[THWatson]

John
Hey, I solved something!

The book "Access Cookbook" has a form, and accompanying module, to replace Access' InputBox. So I imported that form and module. The form gets called from the OpenArgs when the report is opened. The value is entered, the report goes into Preview...but now when I press Print there is no second appearance of a box asking for the # of lines to be pushed down.

I had to make only one small change in your code - replace the InputBox with the name of the form and the text box on that form in which the # of lines is entered.

Works slick.

If you would be at all interested in the form and module, I could send it to you.

Tom

 

[BoxHead]

Tom, that sounds slick! I would very much like to see that code.

Thanks for that!

What else is there, now?

The length of the line and how to get it to only break between names...

Is it possible that there are any commas in the names? I'm not sure of the correct format for names like Sam Stone, PHD. Minnie Mouse, Jr. That could make it a bit trickier, but not too bad. In concatfield, you can use chr(13) & chr(10) (I think those are the two) as part of the separator to add a comma and a linebreak between each name. The next step would be to remove the unneccessary line breaks.

Let me mull this one a bit. A couple years ago, I had started baking something to account for the width of individual letters. I do a lot of fontsize changing onFormat events based on the length of strings. It's always been a 'close enough' solution, but it may be time to preheat the oven.

This has been some great work, Tom. It's time for your star.

Take care,




John

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

 

[THWatson]

John
First of all, thanks for the star! You deserve extra stars, but the site won't let me give them.

Re the names of donors...
The names are straightforward. No degrees following names. No Jr.'s. There are only 4 possibilities of which I can think. Here are the examples.
1) Joe & June Smith (husband and wife donors). Most fall into this category.
2) Sam Brown (a single individual donor)
3) Guelph Male Chorus (a firm or group donor)
4) Dr. Jack Spratt (this would be unusual, but possible)

Re the form and code from Access Cookbook
The form is a pretty normal form, called frmInputBox. Pop-up = No. Modal = No. Border Style = Dialog. It has 3 buttons (OK, Cancel, Help), and an input response text box called txtResponse. The Help file property is MSACC20.HLP.

Here is code behind the form.

Option Compare Database
Option Explicit

Dim mvarHelpFile As Variant
Dim mvarContext As Variant

Const acbcHELP_CONTEXT = &H1&

Private Declare Function WinHelp Lib "user32" Alias "WinHelpA" (ByVal Hwnd As Long, ByVal lpHelpFile As String, ByVal wCommand As Long, ByVal dwData As Any) As Long

Private Sub cmdCancel_Click()
    On Error GoTo cmdCancel_ClickErr
    DoCmd.Close

cmdCancel_ClickExit:
    Exit Sub

cmdCancel_ClickErr:
    MsgBox "Unable to close. [" & Error & " (" & Err & ")]"
    Resume cmdCancel_ClickExit
End Sub

Private Sub cmdHelp_Click()
    'Really, you don't care if this call fails!
    WinHelp Me.Hwnd, mvarHelpFile, acbcHELP_CONTEXT, CLng(mvarContext)
End Sub

Private Sub cmdOK_Click()
    On Error GoTo HandleErr
    Me.Visible = False

ExitHere:
    Exit Sub

HandleErr:
    MsgBox "Unable to close. [" & Error & " (" & Err & ")]"
    Resume ExitHere
End Sub

Private Sub Form_Open(Cancel As Integer)
    On Error GoTo HandleErr
Me.txtResponse = Null
    Me!txtResponse = basInputBox.varDefault
    Me.Caption = basInputBox.varTitle
    Me!lblPrompt.Caption = basInputBox.varPrompt
    If Not IsNull(basInputBox.varHelpFile) And _
     Not IsNull(basInputBox.varContext) Then
        Me!cmdHelp.Visible = True
         'Set things up for the Help button.
        mvarContext = basInputBox.varContext
        mvarHelpFile = basInputBox.varHelpFile
    Else
        Me!cmdHelp.Visible = False
    End If
    If Not IsNull(basInputBox.varXPos) Then
        DoCmd.MoveSize basInputBox.varXPos
    End If
    If Not IsNull(basInputBox.varYPos) Then
        DoCmd.MoveSize , basInputBox.varYPos
    End If

ExitHere:
    Exit Sub
    
HandleErr:
    ' No error can occur here, I don't think, that
    ' would make the form open invalid.
    Resume Next
End Sub

Property Get Response()
    ' Create a user-defined property: Reponse
    ' This property returns the value from
    ' the text box on the form.
    Response = Me!txtResponse
End Property

There is also an accompanying module, called basInputBox. Here is the code for that module.

Option Compare Database
Option Explicit

Const acbcInputForm = "frmInputBox"

' The inputbox form reads its parameters
' from these public variables. Their names
' are important: if you change them, the form
' won't know where to look for the values.
' You can, of course, add your own,
' and add more optional parameters to
' the declaration below. For example, perhaps
' FontName, FontSize, etc. parameters would be nice.
Public varPrompt As Variant
Public varTitle As Variant
Public varDefault As Variant
Public varXPos As Variant
Public varYPos As Variant
Public varHelpFile As Variant
Public varContext As Variant

Public Function acbInputBox(Prompt As Variant, Optional Title As Variant, _
 Optional Default As Variant, Optional XPos As Variant, _
 Optional YPos As Variant, Optional HelpFile As Variant, _
 Optional Context As Variant)

    ' Create an input box replacement that works just
    ' like the one in Access, except that you have complete
    ' control over this one.
    
    ' The differences:
    '   You can enter as much text as you like into this
    '   one, though you could, of course, limit the text on
    '   your form, if you like.
    '
    '   If you don't specify a position, Access centers its
    '   InputBox on the WHOLE screen.  This one centers within
    '   Access.  Just made it all simpler.
    '
    '   At the point of this writing, though the HelpFile and
    '   Context options appear in Help, they don't work for
    '   the Access InputBox.  They do work here.

    ' This parameter is not optional.
    varPrompt = Prompt
    
    ' Use a blank title if the caller didn't supply one.
    varTitle = IIf(IsMissing(Title), " ", Title)
    
    ' Put text into the text box to start with.
    varDefault = IIf(IsMissing(Default), Null, Default)

    ' Specify the screen coordinates, in twips.
    varXPos = IIf(IsMissing(XPos), Null, XPos)
    varYPos = IIf(IsMissing(YPos), Null, YPos)
    
    ' Specify the help file and context ID.
    varHelpFile = IIf(IsMissing(HelpFile), Null, HelpFile)
    varContext = IIf(IsMissing(Context), Null, Context)
    
    ' Open the form in dialog mode.  The code will
    ' stop processing, and wait for you to either close
    ' the form, or hide it.
    DoCmd.OpenForm acbcInputForm, WindowMode:=acDialog
    
    ' If you get here and the form is open, you pressed
    ' the OK button. That means you want to handle the
    ' text in the textbox, which you can get as the
    ' Response property of the form.
    If IsFormOpen(acbcInputForm) Then
        acbInputBox = Forms(acbcInputForm).Response
        DoCmd.Close acForm, acbcInputForm
    Else
        acbInputBox = Null
    End If
End Function

Private Function IsFormOpen(strName As String) As Boolean
    ' Is the requested form open?
    IsFormOpen = (SysCmd(acSysCmdGetObjectState, acForm, strName) <> 0)
End Function

Additionally, there is code on the Open event for the report. The code for that is...

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "frmInputBox", WindowMode:=acDialog

End Sub

Hope this is of interest and help to you.

Tom

 

[BoxHead]

That's great, Tom. Thanks. You may have guessed that I like new things.

It's occured to me that the best way I can think to do this is to run it concurrently with concatField. Declare a variable CLL (Current Line Length) and as each name is added, check that it does not push CLL past the maximum line length. If it would, add a line break to the string and reset CLL to the length of the name being pushed to the new line.

Understand that this goes beyond what you had asked for. This will prevent a name from being printed on more than one line rather than more than one page. It could give you a lot of variation on the number of characters on each line.

I tinkered around with the width of characters. I had assumed that within a single font, a character would maintain its proportionate size.

I was wrong. Two characters could be the same width at one size and different widths at a different font size. Go figure.

I'm pasting the approximate widths of the Old English 18 point characters if you want to pursue this. Give some thought as to the best way to structure this. To create a PhysicalLength funtion for a string, it will have to look at each character, reference its length and add it up.

Let me know what you think.


Width Count Characters
0.062868627 7 space, comma, f, i, j, l,t
0.073286857 6 pipe, apostrophe, period, colon, semi-colon, question mark
0.073708046 2 [, ]
0.084376316 5 (, ), `, c, z
0.094302941 2 e, r
0.10512459 13 a, b, d, g, h, n, o, p, q, s, u, v, y
0.105993388 1 -
0.125737255 5 $, *, _, k, x
0.126982178 10 0, 1, 2, 3, 4, 5, 6, 7, 8, 9
0.136438298 3 ", /, \
0.145740909 1 ^
0.158335802 4 l, Z, m, w
0.168752632 6 #, <, =, >, J, X
0.178127778 2 +, V
0.188605882 4 &, A, C, E
0.20039375 3 G, L, Y
0.21024918 6 B, F, P, R, S, T
0.221124138 6 D, H, K, O, Q, U
0.233185455 1 @
0.25147451 2 %, N
0.256504 1 W
0.261738776 1 M




John

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

 

[THWatson]

John
Interesting stuff about the fonts. I will give some thought as to how the line breaks should occur. Initially, I was just thinking of keeping a name on one page, rather than it being split into a line at the bottom of one page and the rest at the top line of the next page. But would it be easier to program if the testing was done on every line, not just the last line on the page?

In connection with the InputBox from Access Cookbook, there is an additional form, called frmTestInputBox. In case you are interested, it is a form with 7 labels and text boxes and a "Test it" command button.

The form looks like this...
LABEL TEXT BOX
Prompt txtPrompt
Title txtTitle
Default txtDefault
X Position txtXPos
Y Position txtYPos
HelpFile txtHelpFile
ContextID txtContextID

The code behind the form is

Option Compare Database
Option Explicit

Private Sub cmdTestIt_Click()
    Dim strTitle As String

    ' Handle the title specially, so you can
    ' "fake" the way the function ought
    ' to work in real life.
    strTitle = Nz(Me!txtTitle, " ")
    MsgBox "Return value: " & acbInputBox(Me!txtPrompt, _
     strTitle, Me!txtDefault, Me!txtXPos, Me!txtYPos, _
     Me!txtHelpFile, Me!txtContextID)
End Sub

Private Sub txtPrompt_Change()
    Dim fEmpty As Boolean
    fEmpty = (Len(Me!txtPrompt.Text & "") = 0)
    With Me!cmdTestIt
        If fEmpty And .Enabled Then .Enabled = False
        If Not fEmpty And Not .Enabled Then .Enabled = True
    End With
End Sub

This is supposed to call the acbInputBox routine from the basSpecialEffects module. But there is a problem. When you press "Test it" you get a compile error: Ambiguous name detected: acbInputBox.
I'm not sure yet why that happens. I have looked over the code. But I'm going to give it up for now and get some shut-eye.

Take care.

Tom

 

[BoxHead]

Thanks again, Tom.

With the line breaks, I don't know of any way to identify where the text is being pushed to the next page. Inserting the line breaks throughout the string is the only way I can think to ensure a name stays on one page.



John

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

 

[THWatson]

John
That's what I figured.

So, if the function would be fairly simple to write, then it would be interesting to see the effect...whether or not the formatting still looks pretty good, or there is so much difference in line length that the page looks sort of ugly.

An alternative approach would be to have the user take a look, in Preview mode, at the bottom line on the page. If names are broken, re-run the report and enter a "push down" value of 1 more or one less lines than previously entered and see what it looks like then.
Not as clear-cut, since it requires more user intervention, but nonetheless doable.

What I always worry about is that I am the only person in the church who is even remotely familiar with the behind-the-scenes working of this database (there's lots of VBA code), and what happens if I'm not there. So I try to make the program as user-friendly as possible, with as few interventions as possible.
I developed it from scratch, starting about 6 years ago. Since then I have re-built it twice, and now it's a pretty comprehensive program. I keep adding little bits, such as the current functionality we are discussing, to make the secretary's life easier, and I would hate to try and calculate how much time I have spent on it.
I am totally self-taught as far as Access is concerned, and have become fairly decent in many of the programming aspects. My huge weakness is modules - the creation of subroutines and functions. One of these days!

By the way, the value of the line space taken up by vbNewLine is exactly 8 lines per inch.

Tom

 

[THWatson]

John
I want to test an approach with you...

1. tblNewGivings includes a field called "InMemoryOf", so from there I can pull the names of members who made donations in memory of someone, and also the names of people in whose name the donation was made.
So, run a query to pull that data.

2. tblMiscellaneousDonations is set up similarly.
So, run a query to pull that data.

3. Create a Union query to join the data from those two queries.

4. Create a form on which there are 2 list boxes, a text box, and a command button.
a) The first list box pulls the list of "InMemoryOf" people from the Union query. The secretary would select one name from the list.
b) The AfterUpdate event of the first list box runs an SQL that populates the second list box with the names of people who made donations in memory of the name selected in the first list box.
c) The user would then select any number, or all, of the entries in the second list box, a multi-select box. Almost always she would select All, so actually, I can put an All at the top of the list. The items selected would show in the text box, properly formatted for the report.
d) Press the command button to run the report.

5. The report would still open the form from which the user would select the number of lines to be pushed down the page.

The advantages of this approach that I see are...
1. No parameters are needed. The list boxes do the selecting. So that bottleneck is beaten.
2. No need to run a MakeTable query, or create a QueryDef
3. No need to be concerned about the possibility of the ConcatField function taking a while to run.
4. The user has a bit more control over what is printed. Say that, for whatever reason, half of those who donated in memory of a specific person, specified that the donation was to go to the Heritage Fund, and the other half specified the Organ Fund. She could select half of the names from List Box 2, print those, showing the donations designated to the Heritage Fund, and then print the other half of the names showing the donations designated to the Organ Fund.

In actual fact, our Memorial Book pages show things in this format...
In memory of Violet Smith, donations to the Herigage Fund were made by Sam Jones, Fred and Wanda McGruber, Tilly and Mac Franklin.

I would be interested in your thoughts, especially if you see some pitfall of which I haven't thought.

Best regards.

Tom

 

[BoxHead]

1.) Is the 2nd listbox tied to the 1st to eliminate the 'Selecting'?
2.) Could a listbox or combo box be placed on the form to designate the fund? Or, could a listbox be included for each possible fund? Not sure how many options there would be. If you've already captured the data as to which fund was designated, I'd prefer to not require someone to know which is which. I'm a big proponent of avoiding situations where one person has to figure out what someone else already knows.
3.) Would the new format repeat the beginning of each sentence?
In memory of Violet Smith, donations to the Herigage Fund were made by Sam Jones, Fred and Wanda McGruber, Tilly and Mac Franklin.
In memory of Violet Smith, donations to the Organ Fund were made by Bill Smith, Tim and Wilma Millen, and Michael McCaskey.

If you could build the entire string on one pass by differentiating funds on the form or in the query I think it would reduce the amount of 'knowledge' required by the person doing the printing.

FYI, I came up with a way to sidestep the parameters with a simpler make table query. I created a table, tbl1 with one field [myParam] and joined it to the query on which the report is based. On the Report's OnOpen event, I use:

Dim sql1 As String
DoCmd.SetWarnings False
sql1 = "SELECT " & """" & InputBox("Enter param") & """" & " AS myParam INTO tbl1;"
DoCmd.RunSQL sql1
DoCmd.SetWarnings True

I know you're going in a different direction with this, but it's been such an issue, I thought I should mention it.






John

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

 

[THWatson]

John
Takes a lot of boxcars to make a train, doesn't it! And a lot of thought to the order they get hitched!

Re your questions and concerns...

Is the 2nd listbox tied to the 1st to eliminate the 'Selecting'?
ListBox 1 shows the names of people in whose name donations have been made. When the user selects a name, ListBox 2 shows only the people who have donated in memory of the name selected in ListBox1.
So, under most circumstances it will eliminate all the 'Selecting'.
I have modified ListBox 2 so that it shows both the donors' names and the specific fund to which the gift was designated. This makes it readily evident whether or not donations were designated to more than one Fund, and which are which.

Could a listbox or combo box be placed on the form to designate the fund? Or, could a listbox be included for each possible fund?
The modification to ListBox 2 does this. But it would be possible to construct further ListBoxes.
I agree totally with your point about avoiding situations where somebody has to figure out what is already known. That's exactly why I am trying to think of all possible ways to get this down pat, and taking so much time to try and get it that way.

Would the new format repeat the beginning of each sentence?
Yes, the way you have described it is the way things go in the Memorial Book...there being 2 differences. (1) there is about a 3/4 inch space left between the entries, (2) the month and year is printed at the right below the last line for the particular entry.

Like this...
In memory of Violet Smith, donations to the Heritage Fund were made by Sam Jones, Fred and Wanda McGruber, Tilly and Mac Franklin.
March 2005


In memory of Violet Smith, donations to the Organ Fund were made by Bill Smith, Tim and Wilma Millen, and Michael McCaskey.
September 2005

Maybe I could accomplish this with Grouping in the report. Not sure. Haven't got that far yet.

The differentiating of funds is necessary. That's partly what sent me in this new direction. And the principal thing I like about it is that the secretary can see everything on the screen...whereas with a MakeTable or QueryDef approach she has to know beforehand whether or not there was more than one Fund to which donations were made in memory of Violet Smith, and which funds those were.

Thanks for the method to sidestep the parameters issue in a MakeTable query. That's great to know, should I conclude that the Form and ListBox approach isn't the way to go. At the moment, I'm psyched up about its promise, particularly because of the visual interface and no need for parameters.

Well, I'll jump back in the Engineer's seat and head for the next siding.

I really appreciate your thoughtfulness and feedback!!! Pretty soon I won't have to bother you further.

Have a great day!

Tom

 

[ProgramError]

Hi Tom and John

Regarding the work wrapping of the memorial declaration, if you set the textbox for the declaraton to 'can grow' and the width of the textbox the full width of the page at one line in hieght . Doesn't this give you what you are looking? the words don't break in the centre and the hieght of the textbox is determined be the string length. It also prevents the string splitting over two pages. Or do you want to keep the name as a whole so it isn't split at the end of the line like...

In memory of Violet Smith, donations to the Organ Fund were made by Bill Smith, Tim and Wilma Millen, and Michael
splits to the next line with
McCaskey.

is shown as

In memory of Violet Smith, donations to the Organ Fund were made by Bill Smith, Tim and Wilma Millen,
and Michael McCaskey.

As another matter of interest, you can use vblf for line feed instead of chr(10) and vbcr for carrage return instead of chr(13) or even VbCrLf for a combination of the two instead of chr(10) & chr(13) - much easier to read and type.

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