Access Database Reports

[Steve]

I want to edit an access report. Whenever I try to add more database
information to a report, the report is appended. I do not want multiple
pages, I just want to insert new database entries into the report, print it
to .pdf (an easy one I can already do) then edit the report with new data and
keep repeating the process. Please help, I'm really new to all of this.

 

[Klatuu]

You cannot edit an Access report. It is not an interative object (other than
in design view). It is only a view of a set of data based on some criteria
at the point in time the report was created.

What is it you are trying to accomplish?

 

[Steve]

Hello Klatuu,

First, thank you for this very prompt response. Please forgive me but I am
very new to Access. I have created a database with a number of tables. I
created a report with various fields from each of two of the tables. I want
to create a report each time I enter information into one of the tables,
using data from both tables. Only the information from one of the tables
changes. So, I have a table of 'customers' which remains the same
throughout. Then I have a table of 'orders', where I enter new orders as
they arrive. I combine the information from both tables to create a 'report'
which is actually an order confirmation to be sent back to the customer.
Does this make any sense?

 

[Klatuu]

So let me see if I understand.
When you create an order for a customer, you want to create a confirmation
report that includes only the newly created order, right?

 

[Steve]

Yes. Only the information from that order, along with the his/her
information from the 'customer' table.

 

[Klatuu]

Okay, the Record Source of the report should be a query that joins the
customer information table with the order information table.

I don't know how you are opening your report, but I will assume you are
using a command button on your form that runs either some VBA code or a
macro to open the report.

To keep it as simple as possible for you, I would suggest you open the
report's record source query in design view and find the column with the
Order Number

The will be a row or rows labeled Criteria. In this row and in the Order
Number column, put a reference to the form and control name on the form where
the order number is. The syntax would be:

Forms!NameOfFormHere!NameOfOrderNumberControlHere

That will cause only that one order to be printed.

 

[Steve]

I will try this. Is it possible that I can continue this thread a little
later? I will need some time, even though your instructions are excellent,
to work my way though this. I appreciate your help and patience.

 

[Klatuu]

Sure take all the time you need.
You have 10 minutes, starting now

No, really, at your convenience. Post back if you have additional questions.
Good luck.

 

[Steve]

Hello Klatuu;

We will be working on this tomorrow morning. I have created a report with a
logo and some address and phone information. This is the format that I would
like to use and hopefully we can. I am going to email this information to
the people I am helping do this, and I expect we will chat tomorrow. Thanks
again!!!

 

[Steve]

Good morning Klatuu,

I am on my way to dinish up my database and have one question. The syntax
for the reference to the form and control name. The control name would be
the name for the field where the control number (the purchase order number
here) is?

 

[Klatuu]

Well, to clarify, a field is a property of a table or query that contains a
data item. Although even Microsoft erroneously referrs to controls as
fields, they are not. Controls are property of forms and reports. Many
control types can be bound to a field in the report's record source, so to be
accurate, when you refer to a field, you are talking about a a table or
query. When you refer to a control, you are talking about the property on
the form used to display either the value of the field it is bound to or a
calculated value.

So to answer your question, My previous post with

Forms!NameOfFormHere!NameOfOrderNumberControlHere

breaks down like this:

Forms - A reference to the application's Forms collection
NameOfFormHere - The actual name of the form. Specifies the member of the
collection.
NameOfOrderNumberControlHere - The name of the control on the form that has
the field in the form's record source indentified in its control source
property.

So that's how it all ties together

 

[Steve]

Hello again,

I have entered the information in the Criteria row, ProductNo col in this
format:

[Form]![Client Confirmation ONTARIO]![OrderNo]

When I click on the appropriate query, I am instructed to "Enter Parameter
Value". I do this, and then go to my reports, double click on the
appropriate report, and a page is created - the proper page. I then print
the report and close it. I enter more data in the proper form, and go
through the process again, but the report now has two pages, not just the one
I want. Where am I going wrong?

 

[Steve]

I don't know if this went out the first time, so I thought I'd try again.
Sorry to be such a pest.

Hello again,

I have entered the information in the Criteria row, ProductNo col in this
format:

[Form]![Client Confirmation ONTARIO]![OrderNo]

When I click on the appropriate query, I am instructed to "Enter Parameter
Value". I do this, and then go to my reports, double click on the
appropriate report, and a page is created - the proper page. I then print
the report and close it. I enter more data in the proper form, and go
through the process again, but the report now has two pages, not just the one
I want. Where am I going wrong?

 

[Steve]

Hi there,

We have another thought. If we could get the Report Header to repeat on
evey page, we could just keep going and when I need to take off one page
(with the logo, etc.), I could print that page to as a .pdf (using Win2PDF)
and save just that one page .pdf as the confirmation.

 

[Steve]

Hi once again,

We just noticed that the Order Number is not being printed on the
confirmation report. Shouldn't it be? Or maybe because it's the control,
it's not included in the datat transfer from the form to the report. Maybe I
should be using the ID, not the order number. Any thoughts?

 

[Steve]

Is there any way we can contact you by phone. I'm a bit under the gun here,
as you can tell from my past few replies. Thanks, Steve

 

[Klatuu]

Sorry I could not get back to you yesterday, Steve.

If it is asking for a parmeter, then it can't find the control on the form.
As to your other ideas, there are better ways to do it.

My actual preferred way, rather than using a reference in the query, is to
have no reference in the query, but use the Where argument of the OpenReport
method. I did not mention that earlier, because I did not know whether you
were using VBA or a Macro to open the report. In this method, you still use
the field in the report's recordset you want to filter on and you reference
the control like this:

Dim strWhere As String

strWhere = "[PO] = " & Me.txtPONumber

Docmd.OpenReport "MyReport", , , strWhwere

 

[Steve]

Hi Klatuu,

Sorry, I had a bit of an accident and haven't been feeling like
comminicating. My sincere apologies for not getting back to you before this.
I think we have the problem solved, but I appreciate all your time and
patience.

Sorry I could not get back to you yesterday, Steve.

If it is asking for a parmeter, then it can't find the control on the form.
As to your other ideas, there are better ways to do it.

My actual preferred way, rather than using a reference in the query, is to
have no reference in the query, but use the Where argument of the OpenReport
method. I did not mention that earlier, because I did not know whether you
were using VBA or a Macro to open the report. In this method, you still use
the field in the report's recordset you want to filter on and you reference
the control like this:

Dim strWhere As String

strWhere = "[PO] = " & Me.txtPONumber

Docmd.OpenReport "MyReport", , , strWhwere
--
Dave Hargis, Microsoft Access MVP

Is there any way we can contact you by phone. I'm a bit under the gun here,
as you can tell from my past few replies. Thanks, Steve