Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Line numbering (on report/form?) (2000 - 2)

    I am trying to do something that is a waste of time? <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    Say I have two tables with a one-to-many relationship. I'd like to automatically increment the value in a number field in the 'many' table. However, I would like to start the numbering at 1 each time. i.e for each record in the 'one' table there would typically be six related records in the 'many' table and I would like to automatically assign them numbers 1 to 6 and for these number to be visible on a data entry form. I'm pretty certain that meddling with a proper autonumber field is not the way to go.

    Maybe I don't even need to store these numbers as what I really need is to print them next to the item on a report. I've seen the sample reports database which includes an example report that does this.

    Would trying to do the same thing on a form be a pointless exercise??

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Line numbering (on report/form?) (2000 - 2)

    Group your report on the one table.
    In the detail section of the report, add a textbox control.
    Set the control source of this textbox to =1 (include the equal sign)
    Set the Running Sum property to Over Group
    Francois

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Line numbering (on report/form?) (2000 - 2)

    Thanks Francois, I think that's how the sample report I've seen does it, I just haven't had chance to look at it properly yet.

    My real question is would there be any point/would it be possible to do something similar on a form?

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

    Re: Line numbering (on report/form?) (2000 - 2)

    There isn't any point on a form because the sequence of the records can be changed by the user. In a report it at least makes marginal sense in case someone wants to refer to a particular line of a printed report. Forms are interactive and Access records don't really have numbers. What would you be accomplishing by showing a phony number on the form?
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Line numbering (on report/form?) (2000 - 2)

    Thanks, I didn't really think there was any point but I'd got a bit confused.

    However, on a similar note, I've mostly been using Autonumbers as key fields and a visible identifier to a record on forms on reports. Currently, I need to assign a sequential numeric ID value (Report_ID) to records associated with a particular client (ClientName) i.e the Report_ID is not unique in the table but would be for records with the same client name..

    Any suggestions on the best approach. I was thinking of making ClientName and Report_ID both key IDs but would the user have to type the ID manually, with me writing code to check for duplication/prompting for the next number.

    At the end of the day, the database doesn't justify having a much time put into it, so if I don't want to get into something that'll be more complicated than I first thought.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Line numbering (on report/form?) (2000 - 2)

    You could use the DMax function. Something like:
    DMax("[ReportID]", "YourTableName", "[ClientName] = 'Bill Gates'") + 1
    Francois

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Line numbering (on report/form?) (2000 - 2)

    Cheers. I think this will also come in useful for another database I'm working on.

Posting Permissions

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