By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 916 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

Adding columns from another table to a report

100+
P: 114
Hi to all. I hope someone can provide me with a solution to my problem. I have a Inventory master table that is used to produce a report.

Currently a form is used so that either part of the item number or item description is entered and a search button is clicked. VBA checks to make sure that something is in either of these fields and then a query is run that loads a list box on this form.

Clicking on one or more items shown in the list box and followed by clicking on a button "Print Report" causes VBA to build a string and pass it to the report.

What I need to do now is printing the location name and address that is in a table called locations. The inventory master has a loc_id column that matches the loc_id primary key in the location table. Relationship shows the connection of the two tables on this loc_id.

How do I retrieve this information? I need to be able to print something like location.loc_name, locations.loc_addr on this report.

Someone know how to do this. Thanks in advance.
Mar 11 '07 #1
Share this Question
Share on Google+
5 Replies


ADezii
Expert 5K+
P: 8,619
Hi to all. I hope someone can provide me with a solution to my problem. I have a Inventory master table that is used to produce a report.

Currently a form is used so that either part of the item number or item description is entered and a search button is clicked. VBA checks to make sure that something is in either of these fields and then a query is run that loads a list box on this form.

Clicking on one or more items shown in the list box and followed by clicking on a button "Print Report" causes VBA to build a string and pass it to the report.

What I need to do now is printing the location name and address that is in a table called locations. The inventory master has a loc_id column that matches the loc_id primary key in the location table. Relationship shows the connection of the two tables on this loc_id.

How do I retrieve this information? I need to be able to print something like location.loc_name, locations.loc_addr on this report.

Someone know how to do this. Thanks in advance.
__1. Add the Locations Table to the underlying Query (RecordSource). Make sure the Relationship, via [loc_id] , exists between the 2 Tables.
__2. Add [loc_name] and [loc_addr] to the Query Grid.
__3. Add 2 Text Boxes to the Report and set their Control Sources to the newly added Fields.
Mar 11 '07 #2

100+
P: 114
__1. Add the Locations Table to the underlying Query (RecordSource). Make sure the Relationship, via [loc_id] , exists between the 2 Tables.
__2. Add [loc_name] and [loc_addr] to the Query Grid.
__3. Add 2 Text Boxes to the Report and set their Control Sources to the newly added Fields.
Thanks for the reply. The relationship between the 2 tables exist on the [loc_id].

I don't think that would work in this case. Reason I say this is because the Query is used to load the list box prior to selection. Selected row(s) are passed from the list box to the Report. The string contains the item_id, description, and the primary key [rec_id] (it's in list box, but hidden ). The report uses that [rec_id] to fetch the row in the Inventory Table for printing. The report is called via VBA using the "DoCmd.OpenReport stDocName, , , strWhere" command.

I am wondering if the report should be performing the lookup of the [loc_id] in the Location Table using the [loc_id] found in the Inventory Table.

Thats what I'm not sure of or how it would be done, unless there is another way to do this. I'm new to Access and it's report features.

Thanks again.
Mar 11 '07 #3

100+
P: 114
Anyone know how to work with expression builder in Report? I need some help.
Mar 11 '07 #4

nico5038
Expert 2.5K+
P: 3,072
As the locationID is already available, I would propose to use a subreport.
Just place a new subreport and follow the wizard. The wizard will propose to link the subreport to the master report and accept that.
Now Access will synchronize the subreport and collect the needed data.

Getting the idea ?

Nic;o)
Mar 11 '07 #5

NeoPa
Expert Mod 15k+
P: 31,342
Hi to all. I hope someone can provide me with a solution to my problem. I have a Inventory master table that is used to produce a report.

Currently a form is used so that either part of the item number or item description is entered and a search button is clicked. VBA checks to make sure that something is in either of these fields and then a query is run that loads a list box on this form.

Clicking on one or more items shown in the list box and followed by clicking on a button "Print Report" causes VBA to build a string and pass it to the report.

What I need to do now is printing the location name and address that is in a table called locations. The inventory master has a loc_id column that matches the loc_id primary key in the location table. Relationship shows the connection of the two tables on this loc_id.

How do I retrieve this information? I need to be able to print something like location.loc_name, locations.loc_addr on this report.

Someone know how to do this. Thanks in advance.
Larry,
Please don't post the same question twice (Can't get second table data to show in report). This just makes it more difficult for everyone involved. If you need to clarify a question, please include that clarification in the original thread.
Mar 12 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.