Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    New York, New York
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    format a single record to look like a subreport (2000)

    I am working on a report that is comparing an individual's employment barriers with referrals they received. The barrier information has a one-to-one relationship with the the main record, while the referral information has a many-to-one relationship with the main record. In otherwords, each of the referrals someone recieves is a stored in its own individual record, while all of the barrier information is a stored in a series of checkboxes in a single record.

    Each detail item on the report shows the client's name, and I wanted to list the identified barriers and referrals in side-by-side lists, one item per row. The referrals are easy: a subreport. The problem is in creating something similar for the barrier information.

    My thought was that I could assemble a list through VB in a function, and simply call this as the source of a textbox with CanGrow = True. The Function works like this: I open a recordset containing the single barrier record and step through the fields, and if the checkbox is checked then I add the barrier name to a string. Each time I add the name of one of the barriers I add a carriage return - Chr(13). This creates a list of barriers separated by return characters is a string.

    The string is created just fine, the problem is that the report is not reading the carriage returns as returns. In textbox the string of barriers is separated by that little box indicating an unknown character rather than each barrierbeing listed on a new line. If I use a line-feed character - chr(10) - the same thing happens.

    Am I going about this in the right way, and is there a way to get the returns into the string, or is there a better solution?

    -karl

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: format a single record to look like a subreport (2000)

    Try the combination carriage return/linefeed. In VBA, you can use the symbolic constant vbCrLf for this.

    BTW, if there is a one-to-one relationship between individuals and barriers, why don't you merge them into one table?

    Or else, create a table with Person ID and Barrier ID. Each barrier that exists for each person is a separate record. The relationship between the main table and this table would be one-to-many. You could display it in a subreport, just like the referrals.

  3. #3
    Star Lounger
    Join Date
    Sep 2002
    Location
    New York, New York
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: format a single record to look like a subreport (2000)

    Thanks Hans, that worked like a charm.

    As to why the structure, the original idea was this:
    1. Since this barrier information would exist for only a small percentage of several thousand people each year, it should be separate for space reasons. (The small % has turned out to be untrue anyway, and plus the space saved would be small in comparison to the overall size anyway, but I didn't know that then...)
    2. As to why not a structure similar to the referrals, that was a decision based on data entry ease balanced with my programming ability. The series of checkboxes was the best choice for encouraging staff to use the barrier tracking rather than inserting items, but what it would take to tie checkboxes to a structure that had a single record for each barrier was beyond my ability (particularly at the point when I started designing the database).

    Aside from being occasionally cumbersome, is there a hazard with the one-to-one relationship I have? I've thought about changing it, but there hasn't been a really compelling reason.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: format a single record to look like a subreport (2000)

    If it's an existing database, leave it as it is now. If you were in the development stage, it might be worth it to change the structure, but (probably) not now that it is already in use.

Posting Permissions

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