Results 1 to 15 of 15
  1. #1
    Join Date
    Dec 2005
    Location
    Somewhere on the 3rd Rock
    Posts
    93

    Unanswered: Memo field to report

    I have a input field that is a memo type field, and I need it to put the data in a report, simple enough right.

    I know how to get this to put the data in one field on the report. the problem I am having is I need to be able have some of the data to go to page one of the report, and then continue to page 2 of the report, if there is to much data in the field, for it to fit only on the front page.

    the field name for input is IncidentNarrative (memo format). The report it will print to will have enough room on page 1 for about 10 lines of data, and page 2 will have room for about 30 lines of data.

    The data field on the report will not be at the bottom of page one, and will not be able to grow to the next page. however, page 2 can grow to page 3.

    The drawback is I can not change the format of the form, as it is set by state law.

    any suggestions.

    Thanks
    Will Dove
    working hard is better then hardly working ...

  2. #2
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    Have you tried setting the Can Grow property of your memo field to Yes?

  3. #3
    Join Date
    Dec 2005
    Location
    Somewhere on the 3rd Rock
    Posts
    93
    I can not allow the field to grow on the first page, I need it to go to the second page after it reaches it limit.

    Quote Originally Posted by DJN
    Have you tried setting the Can Grow property of your memo field to Yes?
    Will Dove
    working hard is better then hardly working ...

  4. #4
    Join Date
    Dec 2005
    Location
    Somewhere on the 3rd Rock
    Posts
    93
    still trying to make this work, and have not gotten anywhere with it yet, I am thinking of having 2 flieds in the table, and limiting the first on to the max size of the first page area.
    Will Dove
    working hard is better then hardly working ...

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    maybe an outline method:

    api DrawTextEx and its DT_CALCRECT format ??? (google is your friend!)
    Determines the width and height of the rectangle. If there are multiple lines of text, DrawTextEx uses the width of the rectangle pointed to by the lprc parameter and extends the base of the rectangle to bound the last line of text. If there is only one line of text, DrawTextEx modifies the right side of the rectangle so that it bounds the last character in the line. In either case, DrawTextEx returns the height of the formatted text, but does not draw the text.

    armed with that...

    in Detail_Format()
    ...width in lprc is obviously the width of your (unbound) textbox ready to house the first part of memo$
    ...a loop to Left$() your memo$ at various lengths starting Len(memo$) and working downwards (?? 'binary-search' style, dividing the jump by 2 each time ??) until you get the part1$ to fit in a DT_CALCRECT height within some max/min you defined to suit your layout.
    from here you can split your memo$ into two partN$
    ...part1$ on page 1 control
    ...part2$ on page 2 control

    this is from some random jottings in my codebank - never tried it myself.
    best of luck.
    if you can make it fly, let us know.

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i tried some experiments with DrawTextEx - attached is some EXPERIMENTAL CODE

    take extreme care when playing with maxHeight/minHeight and/or font-size and/or textbox size
    . close everything else on the machine and work only on a copy of your db. its quite easy to set these params to generate an interesting OUT OF MEMORY error that corrupts your Windows session and needs a reboot to fix.

    it is almost right on screen (i didn't try hardcopy) - the memo is successfully split into two strings and the first string length is related to the first textbox size...
    ...but there is something wrong. for example in the first record DrawTextEx thinks it can draw three more words in the textbox than reality. needs some tweaking. the divide by 14 is an empirical magic tweak - and it's wrong.
    i also cut all the linefeeds out of the memos ...another tweak needed to handle linefeeds

    if anyone can produce something with less magic and more accuracy, i would like to receive a copy.

    izy

    oh yes - and the code is still full of debug stuff
    Dont use this attach unless you are brave. see attach in #8
    3 Sept'06: Decided to remove this attachment - sloppy & dangerous code.
    Last edited by izyrider; 09-03-06 at 06:36.
    currently using SS 2008R2

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    found a magic tweak!

    Private Function GetHeight(strThis As String) As Long
    Const InternalPadding As Long = 600 'allow for textbox internal padding
    Const PixelMode = 3
    Dim vBox As RECT
    Dim myhDC As Long
    Me.ScaleMode = PixelMode
    myhDC = GetDC(Me.hwnd)
    SetRect vBox, 0, 0,
    (Me.memoBox1.Width - InternalPadding) / 14, 10

    seems to work out for the test data!
    why 600
    (and why /14 for that matter!!)


    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i suspect the memory error was because i didn't release the hDC... get the params wrong and end up in an infnite loop with each cycle eating more memory for another hDC until craaaaaaaack.

    here's another go - this one should release the hDC and fix the out of memory stuff but it doesn't address the infinite loop.
    also added a few more apis and debug stuff to try to fix the problem.

    ...but no, it still doesn't work accurately on screen. why does DrawTextEx draw text in a different way from a textbox? any ideas?

    meanwhile, back at the original question, it should be possible to set up something like the code here plus some magic tweak factors to make a printed report come out right (for a fixed font/size & fixed textbox size). not elegant, but do-able for a given report.

    ??? i wonder if it does any better on hardcopy, but i don't have a printer on this box. i put the additional apis in to play scale-games between printer and screen resolution - no joy so far, but it SHALL yield.

    izy

    BTW - the attached uses Courier for the 1st part - ugly, but part of my experiments, and i forgot to reset the font before posting

    LATER: no, "hardcopy" doesn't seem to be any better. Tried a PDF "printer" at various resolutions and got a different result each time - the memo splits OK, but not at a full line-width for part1$
    Attached Files Attached Files
    Last edited by izyrider; 08-30-06 at 12:57.
    currently using SS 2008R2

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Memo Field

    Maybe I'm way off base here but couldn't the OnFormat of the section be used and the [Page] command. It may be kind of sloppy but couldn't it be something like:

    Select case [Page]
    Case 1
    Report!XXX!MyField = left(MyMemoField,XX)
    Case 2
    Report!XXX!MyField = mid(MyMemoField,XX+1,YY)
    Case 3
    Report!XXX!MyField = mid(MyMemoField,YY+1,ZZ)
    etc...
    End Select

    Where XX, YY, and ZZ, etc. are you're designated lengths of text to fit in the memo field on the report for each page.

    (Syntax is not 100% correct but you can kind of see what I'm getting at - you could probably even get fancier with some kind of equation.)
    Last edited by pkstormy; 08-31-06 at 13:13.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    woooooooooo!
    there is life out there, after all.

    hi Paul,

    the issue is to determine XX

    the original question is YY lines (not XX chars) to go in myrpt.txtbox1 with
    residual chars/lines going on page2 myrpt.txtbox2

    in principle, DrawTextEx() knows all about drawing text (fonts, size, wordends, tab-expansion, &handling, widths, heights, blah blah), but in practice - i can't make the #@~°ç thing work precisely.

    i'm missing something!

    izy
    currently using SS 2008R2

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Memo

    Hey izy,

    I was thinking that the report had a set size of the memo field for page 1 and then that page 2,3,4, etc. each had set sizes to fit the text in. If the memo space available on page 2 was a known 20 lines, then my thought was that approximately XX-YY characters could fit within that space (taking into account that it wouldn't be exact (and probably wouldn't look pretty)). Your code is definately more precise, accurate, more advanced and the correct solution. Mine was just a "down and dirty" type of solution (which really wasn't that thought out - I just typed it in so I could respond to something today so I threw it out there).

    I did encounter some code once which handled reporting of dividing up memo fields into separate pages in a certain manner but I only chanced upon it and didn't really pay much attention as I never thought of needing it myself. Your response and method made me think of it though.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  12. #12
    Join Date
    Dec 2005
    Location
    Somewhere on the 3rd Rock
    Posts
    93
    Your right,

    page 2, 3, 4, etc..... are set to a size, and the sizes are mostly the whole page.


    Quote Originally Posted by pkstormy
    Hey izy,

    I was thinking that the report had a set size of the memo field for page 1 and then that page 2,3,4, etc. each had set sizes to fit the text in. If the memo space available on page 2 was a known 20 lines, then my thought was that approximately XX-YY characters could fit within that space (taking into account that it wouldn't be exact (and probably wouldn't look pretty)). Your code is definately more precise, accurate, more advanced and the correct solution. Mine was just a "down and dirty" type of solution (which really wasn't that thought out - I just typed it in so I could respond to something today so I threw it out there).

    I did encounter some code once which handled reporting of dividing up memo fields into separate pages in a certain manner but I only chanced upon it and didn't really pay much attention as I never thought of needing it myself. Your response and method made me think of it though.
    Will Dove
    working hard is better then hardly working ...

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    finally i bumped into Lebans' take on all this. it didn't show in all my searches for DrawTextEx() because he is using DrawText().
    Lebans seems to have covered all the bases, so no need to rediscover the wheel!

    feed his routine with successive substrings from the memo until you get the height you desire.

    i also tried an alternative to the binary+word adjusting approach to define the substring that fits. instead of DT_CALCRECT, use DT_END_ELLIPSIS (inserts '...' in the string just before it runs out of space in the textbox.
    simple matter then to instr() for the '...'
    you could probably modify Lebans' method to work with this.
    need to handle ellipsis in the original memo. perhaps replace() any ocurrance with something insanely unlikely to be in your real memo that occupies the same space as an ellipsis before starting and 'un-replace' when you have found the first part of the memo you need.

    izy
    Last edited by izyrider; 09-03-06 at 06:38.
    currently using SS 2008R2

  14. #14
    Join Date
    Dec 2005
    Location
    Somewhere on the 3rd Rock
    Posts
    93
    looks like something I need to work on, thanks for the replies and if anyone has any others please let me know.

    I am going to look at all the suggestions and see which I can get to work.

    Thanks
    Will Dove
    working hard is better then hardly working ...

  15. #15
    Join Date
    Nov 2003
    Posts
    1,487
    Interesting...

    Without actually knowing what the report looked like (at this point) I think I would attack this by placing the all queried data into a VBA generated temporary table which would contain one additional memo field for the specific purpose of this report.

    1) Generate a Temporary Table based on the required queried data and include one additional Memo field.

    2) Enumerate through the Queried data and place this data into the temp table.

    3a) While processing each data record, look closer at the specific Memo field in question. Is it greater than 10 lines? If so then process this Memo field and break it into two data chunks....

    3b) Pull out the first 9 lines of the Memo field (API's will need to be used since Line Wrap or word wrap can play a role here).

    3c) For the 10th line add "Continued on next page..." to the retreived data. Use Space(xx) to right justify or again us API for this.

    3d) Place this data into the the first (initial) Memo field.

    3f) Place the remaing portion of the Memo data into the second (additional) memo field.

    4) Continue processing the queried data and place the retrieved data into their respective temp table fields.

    5) Bind the report to the Temp Table (RecordSource = "TempTableName").

    6) Add the second Memo field to the report perhaps in its own group header or something - you'll need to format your report to accomdate. Set the Can Grow and Can Shrink properties to Yes. Bind it to the additional Memo field within the Temp Table.

    7) Delete the Temp Table when the Report is Closed.

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •