Need help with a report

**Tari** · 02-04-2011, 02:40 PM

Hey,

my boss wants to change our purchasing paperwork from paper to access. As I'm the geek on sight he asked me if I could sit down and figure it out - unfortunately I'm a total Access noob

So I decided to work backwards.. I used the order mangement template from the office website and changed it according to my needs.
I'm at the final step now and don't know how to do it

My order form looks like the attached screenshot. The Order ID is the "1" where it says "RTP Number" and it's an autonumber.

Now what I need is when you hit the button "View RTP" that it asks you for the RTP Number.. so 1.. and it shows you the report related to Order ID 1. I have to configure the report so that it looks the way I want it, but I think I can figure that out. My problem is the qualification by Order ID.

And I have another question.. When you open the database the order form automatically opens and displays the first record. How do I make it to default to a new record, rather than the first record? So when you open it, it gives you a blank form.

Hope that made any sene

Thanks for the help!

**jzwp11** · 02-04-2011, 03:31 PM

If you are using code behind the View RTP button, then you just need to specify the WHERE portion of the docmd. How did you set up the button, the command button wizard? Can you post the code behind the button?

The docmd code might look something like this:

Code:

 
Dim stDocName As String
    stDocName = "YourReportNameHere"
    DoCmd.OpenReport stDocName, acPreview, , "[Order ID]= " & me.ControlNameOnTheFormThatHoldsTheOrderID

When you open the database the order form automatically opens and displays the first record. How do I make it to default to a new record, rather than the first record? So when you open it, it gives you a blank form.

In design view of the form, go to Properties-->Data-->Data Entry and change it from No to Yes

**Tari** · 02-04-2011, 03:51 PM

Originally Posted by jzwp11

If you are using code behind the View RTP button, then you just need to specify the WHERE portion of the docmd. How did you set up the button, the command button wizard? Can you post the code behind the button?

Unfortunately I have no clue. As I said, I'm working backwards from a Microsoft Template. I opened the form in design mode and clicked on code. Screenshot 1 shows you what I got there. Screenshot 2 is the properties of the button. All the button does is open the report, doesn't it? This is the link to the original template http://office.microsoft.com/en-us/te...in=HA001234159 there the same button is called "Orders Summary". It would probably save you time to look at it there and your magic can give you the answers ^.^ Sorry, I'm really new to Access.

Originally Posted by jzwp11

In design view of the form, go to Properties-->Data-->Data Entry and change it from No to Yes

Sorry I don't quite understand this. Properties of which section?

I appreciate your help!

**jzwp11** · 02-04-2011, 06:36 PM

Actually the template uses an embedded macro; I generally do not work with macros, so I'm not sure how to specify to open the report just for the current record shown on the form. If I were doing it with code it would look like this.

Code:

Dim stDocName As String

    stDocName = "Orders Summary"
    DoCmd.OpenReport stDocName, acViewReport, , "CustomerID=" & Me.CustomerID

If you can post your DB (with any sensitive data removed) I can add the code.

**Tari** · 02-07-2011, 11:07 AM

Here you go

Thanks so much for your help

**jzwp11** · 02-07-2011, 11:21 AM

I was able to figure out what to do within the macro. I added the reference to the orderID control of the form in the conditions section of the OpenOrderSummary macro: [Forms]![Add an Order and Details]![OrderID]

I've attached the modified DB.

**Tari** · 02-07-2011, 02:03 PM

if you click "view RTP" it does not ask you for the order number though..

**jzwp11** · 02-07-2011, 02:09 PM

It's not doing what I thought it would. Let me take another look at it.

**Tari** · 02-07-2011, 02:11 PM

But I'm actually looking for a prompt asking which OrderID to show. So if I want to reprint OrderID 5.. I click "view RTP", prompt asks me for Order ID.. I enter 5 and it opens the report related to order ID 5

**jzwp11** · 02-07-2011, 02:31 PM

But I'm actually looking for a prompt asking which OrderID to show. So if I want to reprint OrderID 5.. I click "view RTP", prompt asks me for Order ID.. I enter 5 and it opens the report related to order ID 5

From a user's point of view, I would rather have the report print for the record I am viewing rather than some other random record.

So, I have set it up so that the code will show the report for whatever record is shown in the form when the user clicks the button. This relieves them of having to type in the number. Additionally, the way you have your query set up that supplies data to the report, it will error out if there are no detail records tied to the order. Instead of having the user see an erroneous report, I added some additional code that throws back a message saying that there are no details so a summary report is not possible. The revised DB is attached.

We could add another button to give the user the option of viewing the report for another record rather than the currently shown record.

**Tari** · 02-07-2011, 02:36 PM

Originally Posted by jzwp11

This relieves them of having to type in the number

I appreciate all your help with this DB, but I don't think I made myself clear enough as to what I'm looking for. I don't want the user to be relieved of typing a number, I actually do want them to type in the number. If I have to reprint an RTP from a previous order, I don't want to flip through records until I find the one I'm looking for. The DB has an order detail summary per supplier, so I find the order ID real quick. I then just want to click "view rtp", type in the order ID and get the one i was looking for.

Does that make sense?

**Tari** · 02-07-2011, 02:46 PM

Originally Posted by jzwp11

From a user's point of view, I would rather have the report print for the record I am viewing rather than some other random record.

I get that and I agree

Originally Posted by jzwp11

We could add another button to give the user the option of viewing the report for another record rather than the currently shown record.

That would be great

**jzwp11** · 02-07-2011, 02:48 PM

Here is the DB with both options. I'll leave it to you whether you want to keep both or just one. If you want just one, delete the button you don't want.

**Tari** · 02-07-2011, 02:52 PM

That is perfect. Thank you!

So all I have left to do is customize the layout of the report and then I'm done.

Consider yourself my personal hero for today

**jzwp11** · 02-07-2011, 02:55 PM

You're welcome. Good luck as you finish up the project.

Need help with a report

Thread Tools

Need help with a report

Similar Threads

Duplicate a business form in an Access Report (add blank records to a report)

SUMMARY Formula for All Months in Access report Grouped by Month at the End of Report

Access Report using UNION queries - Can I seperate the query results in the report?

query based report question - run report on one record

Sub-report backcolor matching alternating row color in main report

Posting Permissions