Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2006
    Posts
    2

    Unanswered: Report: Text box code to show more than one result

    It's been a while that I've designed a DB in Access, and I need some help. I will try to provide organized & sufficient information below.

    What I need help with, is in the report header, underneath the Cell Number and above the courses, I want to list (in a text box) which Brass Tags are in this particular Cell, and I don't want this list to be vertical. A Cell could have one Brass Tag or up to 100, so it needs to grow. I already know that DLookup won't work since it only shows the first result... can someone please help me?

    The DB is designed to match the internal structure of the business which is as follows, from the topmost level down, here are the tables:

    ->Cell
    One-to-Many with Courses (foreign key is CellID)
    One-to-Many with Brass Tags (foreign key is CellID)

    ->Course

    ->Brass Tag
    One-to-Many with Tasks (foreign key is BTID)

    ->Task



    I want to print a report where I use the parameter [Enter Cell Number], which is working fine. The layout of the report is:

    Report Header:
    Shows the Cell Number with all courses for that cell underneath (subreport).

    Grouped by BTNo (Brass Tag):
    Shows all Brass Tags for that Cell

    Grouped by TaskName:
    Shows all tasks under respective Brass Tag

    The report's record source is a query of the following:
    Table: tbl_cell
    Field: CellNmbr
    Criteria: [Enter Cell Number]

    Table: tbl_BT
    Field: BTNo

    Table: tbl_task
    Field: TaskName

    A subreport's record source is a query of the following:
    Table: tbl_cell
    Field: CellNmbr

    Table: tbl_course
    Field: CourseName

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    detail & pageheader bits of the report have _Format() events where you can do this neatly. probably there is some workaround for the reportheader but i don't bother...

    ...since i hate and loathe parameter queries like your [Enter Cell Number] (why? defensive programming: the user thrashes in some text, the report doesn't come out as expected, and the user can no longer see the typed text to notice that "London" <> "Gordon". user assumes the problem is programmer-incompetence rather than user-incompetence) i use a form myForm to gather user input textbox myBox...

    ...and once i have a form i can have a (hidden or not - as you like) textbox txtBlah...

    ...which i can fill (DAO code, but ADO equivalent exists) with:
    dim recs as dao.recordset
    set recs = currentdb.openrecordset("SELECT [Brass Tag] etc etc WHERE CellID =" & Me.myBox & " etc etc")
    with recs
    do while not .eof
    me.txtBlah = me.txtBlah & ![Brass Tag] & ", "
    loop
    end with
    set recs=nothing

    ...and from there, a textbox on the report can use
    =Forms!myForm.txtBlah
    as .controlsource


    [Brass Tag] is an intriguing name for a table field (ignore the fact that i hate spaces in names): what on earth is this db about?

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Nov 2006
    Posts
    2
    izyrider - Thank you for your reply. This db is going to be used to print consistent forms in a very large manufacturing company. The internal structure of the business is integrated into the db from top level down, where 'Cell' is the highest level and 'Brass Tag' is the lowest in the hierarchy.

    I'm going to attempt to use your 'myform' suggestion and remove the parameter query. However, in this case everyone is very familiar with the cell numbers that would need to be entered for the parameter; it would be very unlikely the user would make an error. Though I do understand your pointing out the more efficient method for easier use to use the 'myform'.

    Thank you again!

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    hope it works out for you, but whoooops

    major error in the code i posted above:

    do while not .eof
    me.txtBlah = me.txtBlah & ![Brass Tag] & ", "
    .movenext ' <<<<< missing in original post
    loop

    if you forget the .movenext you are in an infinite loop until string limits or memory limits or CPU temperature limits stop the machine.

    izy
    currently using SS 2008R2

Posting Permissions

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