Results 1 to 7 of 7
  1. #1
    melhado
    Guest

    Print Current Record - With twist

    Access 2000 SR-1; NT 4.0 SP6

    I have looked at all the posts here regarding printing current record and it is pretty straightforward -- if one is dealing with a single form and a single unique identifier. I am sure many of the ideas presented will work here as well .. but I am unsure of whether the approach I am taking with mine will work.

    The "current record" I am dealing with is a form/subform, each part based on its own table, recording the test data relevant to that process, yet the report resulting must present data from both tables (and a third, for static data, that I believe would be best handled with DLookups to the appropriate lookup table). This is presenting some unique issues that I am unable to find references to or answer questions I have. I am listing what I think are the issues first, and then present the approach I am setting up.

    I am in search of some ideas and approaches to accommodate the requirements.

    <UL><LI>There are two stages of processing, in a one-to-many relationship, the one side is the main form, the many is the subform. However, a quirky numbering system completely ignores uniqueness, and 2 of these fields act together as Primary Key. But Wait ...there's more
    <LI>The report needs to identify only one piece of the many side in the subform .. but this piece needs to be identified by the 2 fields from the one side, PLUS one field from the many side (The 2 fields are the foreign key in the subform's table, and provides the link, however, the value of the one field is a solo act)-- IOW, it takes 3 fields to uniquely identify the piece upon which the report is based. But the three fields are in different tables. The pertinent SQL code of my planned report query illustrates this:

    <pre>SELECT tblBillet_Test_Data.SerNum, tblPiece_Data.PcNum, tblBillet_Test_Data.PTnum
    FROM tblBillet_Test_Data
    RIGHT JOIN tblPiece_Data
    ON (tblBillet_Test_Data.PTnum = tblPiece_Data.PTnum)
    AND (tblBillet_Test_Data.SerNum = tblPiece_Data.SerNum);
    </pre>


    <LI>The DLookup code's criteria part on the report will be based on one of the fields from the one side (main form table)[/list]What I have done is create a query joining the two tables, upon which to base the report using the joins illustrated above, which returns data properly.

    My confusion is how to pass the field information from the form to the report -- can I use a "strSearch" SQL string? Or am I approaching this all wrong?

    Before I get too lost, thought I would look for a little direction. Appreciate your thoughts.

    'dave

  2. #2
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print Current Record - With twist

    Did you try using the where argument of the openreport method. You should be able to create a where clause without the where using concatenation to reference the control values on your main form and subform.

    DoCmd.OpenReport "rptMyReport", acViewPreview,,"SERNUM = '" & me.txtSerNum & "' AND PTNUM = '" & Me.txtPtNum & "' AND PCNUM = '" & Me.SubFormControlName.Form.txtPcNum & "'"

  3. #3
    melhado
    Guest

    Re: Print Current Record - With twist

    Paul,

    Thank you... it was the last line -- how to reference the subform in that WHERE clause that had me wondering about my approach. You answered that nicely, thank you. I assume that my command button is on the main form in your example?

    I seem to recall seeing similar coding using Me.Parent... and Me.Child... is that the same thing or do those have to be declared?

    Once again, thank you

    'dave

  4. #4
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print Current Record - With twist

    Yes, the button would have to be on the main form to use the syntax in my example. I'm not sure of the child part but the parent property can be used from the subform to refer to the main form. This is particularly useful when you are using the subform in more than one object.

  5. #5
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print Current Record - With twist

    I'm not a big dlookup user but I think I'd use the form value for the where clause. As for the rest of it I'm not certain I follow what you're doing but play around with it - you should be able to find a good solution. If not post back with more details.

  6. #6
    melhado
    Guest

    Re: Print Current Record - With twist

    I meant to edit my post.. (I was rambling about the DLookup -- thinking out loud). I was able to sell the QA manager on some design changes in the report, which enavbed me to make use of a subreport for the spec data, linked on the part number. Before, the spec table data was all over the place, so a subreport was not suitable.

    I think writing out all that DLookup stuff made me realize that it was getting more complicated than it needed to be.

    Thanks for responding,

    'dave

  7. #7
    melhado
    Guest

    Re: Print Current Record - With twist

    Thank you for confirming the button placement, Paul, and for clarifying the parent property. This will work, as the subform value is used only here.......

    <font color=red>(I edited out the DLookup ramblings of my original post after making some changes that made a subreport work fine. Unfortunately, not before getting responses to the DLookup question. I apologize for the non-continuity.)</font color=red>

    I am also wondering if, since the actual Piece ID as printed is the SerNum concatenated with "-" and PcNum, I will probably have to use hidden text boxes on the form to capture the equivalency data? I wouldn't think that the field names in the unbound box be adequate to make a match?

    Thank you again for your feedback,

    'dave

Posting Permissions

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