Adding columns from another table to a report

[ljungers]

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_na me, locations.loc_a ddr on this report.

Someone know how to do this. Thanks in advance.

[ADezii]

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_na me, locations.loc_a ddr 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.

[ljungers]

__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.OpenRepo rt 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.

[ljungers]

Anyone know how to work with expression builder in Report? I need some help.

[nico5038]

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)

[NeoPa]

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_na me, locations.loc_a ddr 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.