Adding a field from a second table onto a report

[Meagan]

One of my co-workers has designed a report but now wants to add other fields
from other tables onto the report. I thought maybe if we added an unbound
textbox to the report, then went to properties for that text box and used
the Control Source option to select the new table and new field from this
table using the expression
NewTable![Newfield] this would work but it does not. When we go into layout
view a box pops up asking to "Enter the parameter value". I know if she
designs a query containing multiple tables she could then use this to build
a report, but she does not want to have to go back and redesign the form.
There must be a simple way to do this? Any help would be appreciated.
Thanks!

 

[Joan Wild]

I
know if she designs a query containing multiple tables she could then
use this to build a report, but she does not want to have to go back
and redesign the form. There must be a simple way to do this? Any
help would be appreciated. Thanks!

You wouldn't have to redesign the form. Your report can be based on a
query, while the form on a different table/query.

Alternatively, you don't need to base the report on a table or query. You
could use a SQL statement as the record source.

 

[Meagan]

I meant that she does not want to have to redesign the report but
accidentally typed "form". So, where do I put the SQL statement? I do not
see "Record Source" in any of the properties options.

 

[Joan Wild]

I meant that she does not want to have to redesign the report but
accidentally typed "form". So, where do I put the SQL statement? I do
not see "Record Source" in any of the properties options.

Open the report in design view; click on View, Properties. On the data tab
the first property is 'Record Source' (ensure that the report is selected
and not some control/section).

You should see the name of the table as the current record source. Click on
the build button to the right, and you can use the QBE to add the other
table/field. When you close the window, you'll be prompted to save - when
you do you'll see the SQL statement saved as the record source.

 

[Meagan]

Thanks. So now, I got this far and when when I go back to fieldlist on the
report, I can bring up that new table (though suddenly I do not know why the
main table attached to the report no longer comes up) and when I drag that
new field onto the report and then go to Layout View, I still get that
"Enter Parameter Value" and it is prompting for the value of a field from
the main table.

 

[Joan Wild]

Thanks. So now, I got this far and when when I go back to fieldlist
on the report, I can bring up that new table (though suddenly I do
not know why the main table attached to the report no longer comes
up)

Could you be clearer? The field list does not show tables. It shows the
fields that are in the record source (it should show you all the fields from
your original table, plus the field(s) you added from your other table).

and when I drag that new field onto the report and then go to
Layout View, I still get that "Enter Parameter Value" and it is
prompting for the value of a field from the main table.

I'm not sure, but it sounds as though you went into the QBE window and added
your new table, but deleted your original table. In the QBE, you should
have both tables (with a join line) and the grid would have all the fields
you want to show on your report. Once you save this SQL statement, the
field list will show all these fields.

 

[MSEagan]

In QBE I only added one new field from the new table, but both tables were
joined. So, I have to actually go back and add all the fields I want on the
report even though I already have all the fields I want from the first
table -this is what I assume I must do. OK, so now I think understand
this....so the next problem is how to get that "Enter Parameter Value"
message to leave so that I can actually go into Layout View and see a value
in this new field instead of ERROR.

By the way, I always wondered how to add new fields from different tables
and it was not until my friend (who thinks of me as an ACCESS expert!) asked
me that I decided it was time to learn. Once I have my test version working,
I can go back and teach her. SO, thanks for your help.

 

[Joan Wild]

In QBE I only added one new field from the new table, but both tables
were joined. So, I have to actually go back and add all the fields I
want on the report even though I already have all the fields I want
from the first table -this is what I assume I must do.

Yes, ensure it shows all the fields you want.

OK, so now I
think understand this....so the next problem is how to get that
"Enter Parameter Value" message to leave so that I can actually go
into Layout View and see a value in this new field instead of ERROR.

That could show up because you have a control on the report that refers to a
field that isn't in the record source - perhaps it's just a minor difference
(a space in the name); also check the sorting and grouping box. What is the
parameter it is looking for?

By the way, I always wondered how to add new fields from different
tables and it was not until my friend (who thinks of me as an ACCESS
expert!) asked me that I decided it was time to learn. Once I have my
test version working, I can go back and teach her. SO, thanks for
your help.

You could create a saved query and use that as the record source as well.

 

[Meagan]

Thanks so much! That worked. The problem was as you said...I did not include
all the fields from my report in the SQL statement. I added them and it now
works correctly.