473,326 Members | 2,126 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Adding columns from another table to a report

114 100+
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
5 2594
ADezii
8,834 Expert 8TB
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
ljungers
114 100+
__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
ljungers
114 100+
Anyone know how to work with expression builder in Report? I need some help.
Mar 11 '07 #4
nico5038
3,080 Expert 2GB
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
32,556 Expert Mod 16PB
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

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

Similar topics

2
by: Daniel | last post by:
I use an Access database to basically take data exports, import them, manipulate the data, and then turn them into exportable reports. I do this using numerous macros, and queries to get the data...
1
by: Marius Kaizerman | last post by:
Hi, I'm working with access and sql server. I have two tables. Fields in table #1: barcode (unique),transfer_to,quantity_of_items,supplier,price,date_of_shipment Fields in table #2: barcode...
13
by: Shannan Casteel via AccessMonster.com | last post by:
I set up two tables (one with the regular claim info and another with ClaimNumber, PartNumber, and QuantityReplaced). The ClaimNumber is an autonumber and the primary key in both tables. I made a...
4
by: Sam | last post by:
Hi, I'm adding columns to a datatable as followed. The values are from textboxes or comboboxes. The first column is properly field but then all the subsequent columns just contain "", whereas the...
1
by: travismorien | last post by:
I have four tables of different "entities". One table contains information for "people", one for "trusts", one for "companies" and one for "self managed super funds". Each type of entity has an...
5
by: John Bokma | last post by:
I have a table A, with two ID columns. In a report both ID colums should be shown with the actual value stored in a second table, B The problem is, both IDs need to be looked up in B, but are not...
0
by: Abhishek Rodrigues | last post by:
Hi All, I am trying to create a report which has dynamic columns. i.e. the columns will be known only at the run time. How do I add the columns at the runtime in Crystal reports. Apart from...
3
by: wvmbark | last post by:
First time poster... I just found this forum and it appears there's plenty of people here that could make short work of problem that's been driving me absolutely bonkers for months. Every day we...
5
by: =?Utf-8?B?Y2RiaWdncw==?= | last post by:
Hi, I've been tasked to write a windows app that allows people to enter transactions. For each transaction, there can be an unknown number of items, and as a person enters an item, the program...
1
by: ikuyasu | last post by:
Hi, I am trying to create a report that takes a value from the field on a table as a group category. But The value on the report takes an id number (auto increment, and the first column)...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.