Some Fields in Record Source Not transferring to Report

[Meredith9053]

I have used a Database template to create a database to use for a simple job.
The template is exactly what I wanted, so I plagerised! The database is
finished now, with the exception of this small problem - well its not that
small because it has rendered my database useless at this stage.......ahhhhh

Ok, my problem is that some of the items on my orders table are not being
transferred to my invoice report. Ie, freight and Order Number. Everything
else that I have set up or amended is totally fine. But these two fields
will not transfer over. (fields are available for invoice view, and have
been added to record source.) ie, properties on report freight - control
source is freight selected from the pull down list. (have tried amending
control source to read =[Orders].[Freight] and that sort of thing, but no go.


I did a bit more research, and pulled some templates to check where I might
be wrong, and found the same error on another template – northwind 2007. If
you run the order and make an invoice, you will notice the freight is not
transferring on that database either. Can someone please have a quick look
at that template, and as mine is similar, it may correct my problem also.
Incidently, at the end of my invoice I have the following: These fields
will not calculate either.
Total of Sales: Control source =Nz(Sum([ExtendedPrice]),0) This works fine.
Freight: Control Source=Freight (taken from Orders table) (Will not pull
record from the input on Order details input page.)
SubTotal: Control Source =([TotalofSales]+[Freight]) Get nothing not even
error on these last 3.
GST (+10%) Control Souce =([SubTotal]*0.1)
Total Due: Control Source =([SubTotal]+[GST])

One interesting point, both these items, are the only two (apart from a
discount field in the actual product ordering section, that are fields that
are individual to each order. Make sense? ie, all items like product code
etc, come from a pull down list etc. Except my discount field which i just
enter the discount off the order, which is also individual to each order.....

As I said the Northwind 2007 has the same error….. so if that is fixed I
would assume the same problem would be what I am experiencing……

Thanks so much for your time.

 

[Wayne-I-M]

Create a query with all the information you need for the report and base the
report on that.

 

[Fred]

Hello Meredith,

Wayne's answer is, of course, correct.

You are having trouble trying to troubshoot your current work. If I may be
direct, your post indicates that you are deeply immersed in the terminology
and syntax of Access work but shows a lack of fundamental understanding of
how "flows" through access objects. (from tables to forms and reports,
sometimes with queries in the middle. If that's true, it would be a good
area to learn a little extra on. You post lacks any clear statements about
the core facts.

Not that many will want to spend a lot of time looking at a database which
isn't yours, but in the Northwind example, freight DOES show up on the
invoice. It's data source is the orders table, with a query in the middle.

Take an example of your freight amount not showing up in the report.

- Is that freight amount present in the freight field of the record for that
order in your orders table?

- Look at the query (data view) which is the record source of your report.
Is that example freight amount present

- If the answer to both is "yes", then just try (temporarily) adding a
"freight" box to your report (or a copy of your report) and see if it show up
on the printout.

I'm guessing that the answers to the above 3 questions will help get you
pointed towards the source of the problem.

Hope that helps a little.

Fred

I have used a Database template to create a database to use for a simple job.
The template is exactly what I wanted, so I plagerised! The database is
finished now, with the exception of this small problem - well its not that
small because it has rendered my database useless at this stage.......ahhhhh

Ok, my problem is that some of the items on my orders table are not being
transferred to my invoice report. Ie, freight and Order Number. Everything
else that I have set up or amended is totally fine. But these two fields
will not transfer over. (fields are available for invoice view, and have
been added to record source.) ie, properties on report freight - control
source is freight selected from the pull down list. (have tried amending
control source to read =[Orders].[Freight] and that sort of thing, but no go.


I did a bit more research, and pulled some templates to check where I might
be wrong, and found the same error on another template – northwind 2007. If
you run the order and make an invoice, you will notice the freight is not
transferring on that database either. Can someone please have a quick look
at that template, and as mine is similar, it may correct my problem also.
Incidently, at the end of my invoice I have the following: These fields
will not calculate either.
Total of Sales: Control source =Nz(Sum([ExtendedPrice]),0) This works fine.
Freight: Control Source=Freight (taken from Orders table) (Will not pull
record from the input on Order details input page.)
SubTotal: Control Source =([TotalofSales]+[Freight]) Get nothing not even
error on these last 3.
GST (+10%) Control Souce =([SubTotal]*0.1)
Total Due: Control Source =([SubTotal]+[GST])

One interesting point, both these items, are the only two (apart from a
discount field in the actual product ordering section, that are fields that
are individual to each order. Make sense? ie, all items like product code
etc, come from a pull down list etc. Except my discount field which i just
enter the discount off the order, which is also individual to each order.....

As I said the Northwind 2007 has the same error….. so if that is fixed I
would assume the same problem would be what I am experiencing……

Thanks so much for your time.

 

[Meredith9053]

Wayne, thanks so much for your response.
You are totally right in what you are saying. I dont know what i am doing.
But as i said, I have a little business, and needed a database to create and
record orders and invoices. I dont have the money to spend to have someone
design a database for you, so i thought i would give it a bash! considering
I did not know what access was, let alone how to use it a few months ago, i
am happy enough with my efforts.

anyway, in answer to your questions i have indeed created a query to run the
report. maybe i did something wrong in the setup? Well obviously! LOL

The Freight and Order number DOES show up in the Orders table.

The Query for the Invoice data is below. and as you can see the order
number and freight is in there.

SELECT Orders.[Order ID], Orders.[Ship Name], Orders.[Ship Address],
Orders.[Ship City], Orders.[Ship State/Province], Orders.[Ship ZIP/Postal
Code], Orders.[Your Order Number], Orders.[Freight], Orders.[Customer ID],
[Employees Extended].[Employee Name] AS Salesperson, Orders.[Order Date],
Shippers.Company AS [Shipper Name], [Order Details].[Product ID], Products.ID
AS [Product ID], [Order Details].[Unit Price], [Order Details].Quantity,
[Order Details].Discount, CCur(Nz([Unit
Price]*[Quantity]*(1-[Discount]),0)/100)*100 AS ExtendedPrice, Products.[ATG
Code], Products.[Product Code], Products.[Product Name]
FROM (Shippers RIGHT JOIN (Customers RIGHT JOIN (Orders LEFT JOIN [Employees
Extended] ON Orders.[Employee ID] = [Employees Extended].ID) ON Customers.ID
= Orders.[Customer ID]) ON Shippers.ID = Orders.[Shipper ID]) LEFT JOIN
([Order Details] LEFT JOIN Products ON [Order Details].[Product ID] =
Products.ID) ON Orders.[Order ID] = [Order Details].[Order ID];

I can image that it is completely frustrating to help people like me, and i
debated posting this reply, but i dont want to throw away what i have done
already.

 

[Tom Wickerath]

Hi Meredith,

The Freight and Order number DOES show up in the Orders table.

The Query for the Invoice data is below.

Try running the query by itself. Do you see the expected number of records,
with the freight and order numbers shown in each record?

Although the chances are low of a problem here, you might verify that the
properties of the textboxes in the reports show that the forecolor and
backcolor are different. If you happened to have a white forecolor with a
white backcolor, the text box would appear to have nothing in it.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Wayne, thanks so much for your response.
You are totally right in what you are saying. I dont know what i am doing.
But as i said, I have a little business, and needed a database to create and
record orders and invoices. I dont have the money to spend to have someone
design a database for you, so i thought i would give it a bash! considering
I did not know what access was, let alone how to use it a few months ago, i
am happy enough with my efforts.

anyway, in answer to your questions i have indeed created a query to run the
report. maybe i did something wrong in the setup? Well obviously! LOL

The Freight and Order number DOES show up in the Orders table.

The Query for the Invoice data is below. and as you can see the order
number and freight is in there.

SELECT Orders.[Order ID], Orders.[Ship Name], Orders.[Ship Address],
Orders.[Ship City], Orders.[Ship State/Province], Orders.[Ship ZIP/Postal
Code], Orders.[Your Order Number], Orders.[Freight], Orders.[Customer ID],
[Employees Extended].[Employee Name] AS Salesperson, Orders.[Order Date],
Shippers.Company AS [Shipper Name], [Order Details].[Product ID], Products.ID
AS [Product ID], [Order Details].[Unit Price], [Order Details].Quantity,
[Order Details].Discount, CCur(Nz([Unit
Price]*[Quantity]*(1-[Discount]),0)/100)*100 AS ExtendedPrice, Products.[ATG
Code], Products.[Product Code], Products.[Product Name]
FROM (Shippers RIGHT JOIN (Customers RIGHT JOIN (Orders LEFT JOIN [Employees
Extended] ON Orders.[Employee ID] = [Employees Extended].ID) ON Customers.ID
= Orders.[Customer ID]) ON Shippers.ID = Orders.[Shipper ID]) LEFT JOIN
([Order Details] LEFT JOIN Products ON [Order Details].[Product ID] =
Products.ID) ON Orders.[Order ID] = [Order Details].[Order ID];

I can image that it is completely frustrating to help people like me, and i
debated posting this reply, but i dont want to throw away what i have done
already.

 

[Meredith9053]

Tom is the new god!

Thanks tom, I hadnt considered the White on White option..... dont know how
i managed it, and wasnt going to check because i thought it would be a waste
of time, how could i miss something that simple...... and well... there you
have it. thanks so very much.

Meredith

Hi Meredith,

The Freight and Order number DOES show up in the Orders table.

The Query for the Invoice data is below.

Try running the query by itself. Do you see the expected number of records,
with the freight and order numbers shown in each record?

Although the chances are low of a problem here, you might verify that the
properties of the textboxes in the reports show that the forecolor and
backcolor are different. If you happened to have a white forecolor with a
white backcolor, the text box would appear to have nothing in it.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Wayne, thanks so much for your response.
You are totally right in what you are saying. I dont know what i am doing.
But as i said, I have a little business, and needed a database to create and
record orders and invoices. I dont have the money to spend to have someone
design a database for you, so i thought i would give it a bash! considering
I did not know what access was, let alone how to use it a few months ago, i
am happy enough with my efforts.

anyway, in answer to your questions i have indeed created a query to run the
report. maybe i did something wrong in the setup? Well obviously! LOL

The Freight and Order number DOES show up in the Orders table.

The Query for the Invoice data is below. and as you can see the order
number and freight is in there.

SELECT Orders.[Order ID], Orders.[Ship Name], Orders.[Ship Address],
Orders.[Ship City], Orders.[Ship State/Province], Orders.[Ship ZIP/Postal
Code], Orders.[Your Order Number], Orders.[Freight], Orders.[Customer ID],
[Employees Extended].[Employee Name] AS Salesperson, Orders.[Order Date],
Shippers.Company AS [Shipper Name], [Order Details].[Product ID], Products.ID
AS [Product ID], [Order Details].[Unit Price], [Order Details].Quantity,
[Order Details].Discount, CCur(Nz([Unit
Price]*[Quantity]*(1-[Discount]),0)/100)*100 AS ExtendedPrice, Products.[ATG
Code], Products.[Product Code], Products.[Product Name]
FROM (Shippers RIGHT JOIN (Customers RIGHT JOIN (Orders LEFT JOIN [Employees
Extended] ON Orders.[Employee ID] = [Employees Extended].ID) ON Customers.ID
= Orders.[Customer ID]) ON Shippers.ID = Orders.[Shipper ID]) LEFT JOIN
([Order Details] LEFT JOIN Products ON [Order Details].[Product ID] =
Products.ID) ON Orders.[Order ID] = [Order Details].[Order ID];

I can image that it is completely frustrating to help people like me, and i
debated posting this reply, but i dont want to throw away what i have done
already.

 

[Tom Wickerath]

Hi Meredith,

Glad to hear that you found the problem!

Several years ago, when I used to teach an Access course, I would sometimes
through in an extra credit question to diagnose a combo box on a form that
appeared blank. I used the font trick to make the combo box appear as if it
wasn't working. A few students would solve the problem. <smile>


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom Wickerath]

I meant "I would sometimes throw in..."


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________