Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating a dictionary-type header

    I would like to create a report, a phone directory, for example, and have in the page header the first entry and the last entry on that page. It will look something like this:

    Adams - Barette
    or
    Peters - Smith

    I have no problem getting the first name in the sequence in the header and the last name in the sequence in the footer, but the trick to me is how to get the last name in the sequence in the header.

  2. #2
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a dictionary-type header

    I think what you want to do is set the controls value in the reports On Page Event.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a dictionary-type header

    Ok, sounds good, but what do I do?

  4. #4
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a dictionary-type header

    John:
    Sorry I didn't respond more quickly. I actually spent a fair amount of time trying to get this to work and in the end, I still can't get the Last field in the report into the Page Header. I have been able to insert the first field in the footer however ...

    Insert a text box in the Page Header bound to the Contact Name field. In this example I call the text box TextFirst and the field is [fldContact]. In the Page Footer insert another text box bound to the [fldContact] this will be the To: text. Insert a final text box in the Page Footer e.g. TextFooterFrom this is the From: text.
    Finally, in the OnFormat Property of the Page Header insert the following code:
    Reports!YourReportName!TextFooterFrom = Reports!YourReportName!TextFirst

    Afraid that's the best I can do. Hopefully someone else can suggest how to get the last record into the Header.

    [img]/w3timages/icons/crazy.gif[/img]

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a dictionary-type header

    I have had success by creating a textbox in my page header and putting as the control source Left$([LastName],3), which returns the first three letter of the first entry on the page. If I copy this text box and put it in the footer, it gives me the first 3 letters of the last entry of the page. The trick is how to tell a text box in the header to behave like it is in the footer.

    Any ideas?

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Creating a dictionary-type header

    There is a sample report in the Solutions database that creates a "phonebook" type of header on each page. Is that what you're looking for? You can download Solutions.mdb or Solutions9.mdb (Office 2000) from the MS site, but if you can't find them, let me know and I'll email you a copy if you specify the version. Unfortunately, they don't zip down far enough to attach here.
    Charlotte

  7. #7
    Star Lounger
    Join Date
    Jan 2001
    Location
    Yorkshire, England
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a dictionary-type header

    access 95 came with a sample file called solutions.mdb which had an example of just this - a phone book with first / last page entry on page header.
    Trick is to do 2 passes through file storing fields on first pass and putting it in header on second

    code below does something similar - puts page total in header you should be able to amend it for your names.

    insert control HdTotal in header section to contain total

    to make access do 2 passes insert control with =[pages] as value (as used in page 1 of n) this will force 2 passes thru report

    in this example I've totalled field "amount" you'll need to insert your own field name. Also I've defined it as integer you may need to change this.

    Option Compare Database
    Option Explicit
    Dim Totals() As Long
    Dim Total As Long
    Dim PageNo As Integer
    Dim Pass As Integer


    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    Total = Total + Me![Amount].Value


    End Sub

    Private Sub PageFooter_Format(Cancel As Integer, FormatCount As Integer)

    If Pass = 1 Then
    PageNo = PageNo + 1
    ReDim Preserve Totals(PageNo)
    Totals(PageNo) = Total
    Else
    PageNo = PageNo + 1
    End If
    Total = 0

    End Sub

    Private Sub PageHeader_Format(Cancel As Integer, FormatCount As Integer)

    If Pass = 2 Then
    Me![HdTotal].Value = Totals(PageNo)
    End If
    Me![CondPgBreak].Visible = "No"

    End Sub

    Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)

    If Pass = 1 Then
    Pass = 2
    PageNo = 1
    Else
    Pass = 1
    PageNo = 0
    End If

    Total = 0

    End Sub

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a dictionary-type header

    Exactly! Would it be simpler to e-mail me what appears in the control source of the text box?

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a dictionary-type header

    Must confess that I am totally out to lunch regarding your answer. I don't understand how working with page numbers are going to get my alphabetical letters. As far as double passing, I'm not quite sure what this means, either.

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Creating a dictionary-type header

    No, because there's more to it than that. I'm attaching a zipped Access 97 database that contains the report as pulled from the Access 97 version of Solutions.mdb The main difference between that and the 2000 version is specific references to the DAO object library in the code.
    Attached Files Attached Files
    Charlotte

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a dictionary-type header

    Got your attachment, rushed to the report. Saw two text boxes that had no control source, nothing. The were named firstentry and lastentry. I don't understand why it is working. I had to convert your file into Access 2000. Could that be it? I tried to recreate the properties. Still don't know the secret.
    If you can figure it out, hats off to you. I will not give up, but it is frustrating me.
    By the way, I'm not too sure what DAO means. I know DOA means dead on arrival.
    Thanks for all your help.

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Creating a dictionary-type header

    You didn't indicate which version of Access you were using, so I sent the lowest common denominator.

    In 2000, it's customary to use constructions like "Dim dbs As DAO.Database" to keep from confusing the application when the ADO references are checked. DAO stands for Data Access Objects, and it's the object model that Access is built on. Every time you declare a database object variable, you're using DAO. To make it work, you must have a DAO object library reference set from the Tools-->References menu in the VB IDE.

    The textboxes are not supposed to have a control source. That's the whole idea--the report works because you're using code to populate two unbound controls. The code behind the report sets the value of those textboxes dynamically, based on the first name on the current page and the last name on the same page. The tricky part is that the page has to be formatted before you actually know what the last name on the page is, so populating the unbound textboxes requires both format passes.

    If you struggle through the code behind the report, you'll learn a lot about what you can do with Access reports and a minimal amount of code.
    Charlotte

  13. #13
    Star Lounger
    Join Date
    Jan 2001
    Location
    Yorkshire, England
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a dictionary-type header

    sorry if the reply was a bit cryptic but no need to blush this double passing routine is anything but intuitive. If you think of putting page 1 of n at the base of your page how do you know what n is till you get to the last page? Basically you don't - so access formats what it can first pass then makes a second pass through the report to fill in the missing bits and then prints it. You can make use of thie second pass to fill in other missing bits like your first / last names.
    Hopefully the solutions.mdb that Charlotte posted can explain this better than I can. I think there is also a knowledge base article but I can never find them when I need them. Also I think the Access Developers Handbook has a pretty good explanation (can't check cos somebody "borrowed" my copy)

    good luck

  14. #14
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a dictionary-type header

    I thank you for all of your help, but I'm just not getting it. Unless it is mapped out in sequence (Do A, then B..), I'm having a hard time visualizing what is happening. In fact, I don't really care what is happening as long as I know how to do it. I got your file, looked at it, and it still all seems like magic to me.

    I admire your patience with me.

  15. #15
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a dictionary-type header

    Here's what I sent to Charlotte. The same applies to you.

    I thank you for all of your help, but I'm just not getting it. Unless it is mapped out in sequence (Do A, then B..), I'm having a hard time visualizing what is happening. In fact, I don't really care what is happening as long as I know how to do it. I got your (Charlotte's) file, looked at it, and it still all seems like magic to me.

    I admire your patience with me.

Page 1 of 2 12 LastLast

Posting Permissions

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