Report that is a snapshot of current form screen...doable? 1

[BoyWonderDesigns]

Hi,

I have a single form built from 2 tables (Order and Contact). Each Order has 3 contacts (deliver, billing, and pickup)...after the record is entered (contacts are selected from listboxes for each of the 3 types) I have a Print button that should bring up a report of what is on the current form screen. Is it possible to do this? I wasn't sure since the one form has the same table referenced 3 times (once for each of the 3 contact types?) What do I have to do to accomplish this reporting feat?

Thanks again, experts!

JB

 

[Remou]

You can build a report that mirrors the form, that is, a main report with three subforms. You can use the form data to limit the data displayed in the report.

 

[BoyWonderDesigns]

ok, so will I need to actually display the key fields to use for the subreports? I guess I am confused on how to use the form data to limit what is displayed on the report...previously (before I restructured the whole thing) I had the report filtering to the OrderID that matches the form OrderID, and it worked beautifully. But since we now have the 3 different references to the single table I was trying to think of how to link it all together for the single report page. My contact info entry on the form are list boxes, but on the report I need it to be the form (that is used to enter the contact info when a new one is added) instead of the list of available contacts)...am I making sense? So essentially the report will show the name, address, city, etc. for each of the 3 contacts along with the order date, etc.

I have attempted to setup subforms in my report, but it shows all the contact records as one big long form. I am guessing I need to filter somehow, but I wasn't sure where to apply a filter

Thanks!

 

[BoyWonderDesigns]

here's a link to what I have, might make it easier for you to see what I mean...pull up a record on FORM_Order_Entry then hit the Print Report button and it should pull up the data on the current form to the report format

http://www.boywonderdesigns.com/tek/jobs1d.mdb

Thanks!

 

[Remou]

For each subreport (when I said subforms in my previous post, I meant subreports, sorry) set the Link Child and Link Master fields appropriately:

[tt]Link Master Link Child
ORDER_BILLTO_Number Contact_ID
ORDER_PU_Number Contact_ID
ORDER_DEL_Number Contact_ID[/tt]

Either build the query that the report is based on with a reference to the Order Number of the form on the criteria line:

[tt]=Forms!FORM_Order_Entry!ORDER_Number[/tt]

Or open the report with a similar reference:

[tt]DoCmd.OpenReport "RptOrder2",,,"ORDER_Number=" & Me.ORDER_Number[/tt]

 

[BoyWonderDesigns]

http://www.boywonderdesigns.com/tek/jobs1d.mdb

OK check it out now and see if I am on the right track...something is still off...what did I screw up now? LOL

thanks for your assistance Remou

 

[Remou]

For future reference, it is not usual in Tek-Tips to post links to your work, it happens, but is rare. Everything is done with posts so that any reader can see both the problem and the solution.

Your report is too complicated and based off an incorrect query. Queries are a good idea for reports, but let us start with something simpler. Choose the Order table and start the Report Wizard, add all the fields (for now), select Columnar layout and choose Modify Design on the final screen. Make sure the Wizard Wand is selected, then add a Subreport to the Detail section of the report. The options to choose are:
1. Use existing tables and queries
2. Select Table: Contact and add all the fields
3. You will be asked to choose which fields link your main form to this subform. Let us say it is the Bill To subform, so choose ORDER_BILLTO_Number from the Form/Report Fields drop-down and Contact_ID from the Subform/Subreport Fields drop-down.

Repeat the above for the next two subreports, choosing the appropriate link fields.

Alter the code for your print button.

1. Change stDocName = "RptOrder3" to the name of the newly created report.
2. Change this:
[tt]DoCmd.OpenReport stDocName, acPreview, , "[ORDER_Number]=Forms!FORM_Order_Entry!ORDER_Number"[/tt]
To this:
[tt]DoCmd.OpenReport stDocName, acPreview, , "[ORDER_Number]=" & Me.ORDER_Number[/tt]

The Northwind sample database is a very useful resource and fully repays careful study.

Happy New Year.

 

[BoyWonderDesigns]

AWESOME! I knew I was making this more complicated than it actually was. Looks like I need to go through Northwind again, to get the rust off! Thanks for your assistance once again!

 

[JoeAtWork]

If you intend to send direct to printer, you could use

DoCmd.PrintOut

Otherwise, you might be able to send out as HTM, rich-text, or one of the other formats supported by:

DoCmd.OutputTo