Want to display a list of items for a record 1

[buee04]

Hi,

Okay, let's see if I can explain this. But before I go any further, I gotta let you know that I've only been using Access for 2 months now, so alot of stuff I'm not familiar with, especially vb scripting.

For the sake of explaining, let's say I have 3 tables. The main table is tblJobs, and it's related one to many to tblFirmsInvolved by ContractNumber. And that is related to tblFirms by ID.

tblFirmsInvolved holds the names of the 8 firms involved for each job in tblJobs and each firm is categorized by ID. For example, 1 is Architect, 2 is Consultant, and so on. And, my tblFirms simply says what types of firm the numbers mean.

Okay, now the report. My report, rptResult, shows the details for each record. I want to display all of the firms involved for each job on my report. I tried using a list box, but I wasn't able to get it.

Please help.

THANKS in advance!

 

[DougP]

If you use a subreport on the main report instead of a list box you will be better off.

Not sure which tables go where for your example.
But if you think of an Invoice the main report has the header informaiton like Invoice Number Date and so on
The subreport for an Invoice is the "items" sold.
It could be one or many
So

Kinda like so
----------------- Main report -----------------------
100001 8/7/02 CustID = 120002
-----------------------------------------------------
------------------- subreport -----------------------
Part# Description qty price [Invoice# hidden]
9299 Screw 2 .45 100001
9291 Nut 1 .23 100001

-----------------------------------------------------
Then each part in the List above also has the Invoice number field hidden from view.

Now the subreport is Linked to the main report by the Invoice number. This is the Child/Master link in the properties of the subreport. So you would put "Inovice#" in both the child and master properties boxes.
DougP, MCP

 

[buee04]

Hey Doug,

Thanks for your help, I got it now. But I just wish that I had more control with the subreports.

 

[buee04]

PLEASE HELP! Deadline is this Friday!!

Thanks.

This is more of a design problem, but I will need help with the technicalities too. I have some 6 fields that are automatically on the report as default. Then I have 10 textboxes reserved for the fields that the user chooses to display. The user selects the different fields from frmReportSelect, which is pretty much a listbox that you can have multiple selections and a preview report button. The record source for the listbox is qryAll, which is all of the fields that I want to display from different tables.

I have one main table, tblJobs, which has a good number of other tables related to it via ContractNumber. There are 3 tables that have a one to many relationship with tblJobs: tblFlexComp, tblFirmsInvolved, and tblEquipSold.

So if the user chooses to display the field FLEX COMPONENT (which is from tblFlexComp), I want to be able to display all of the different components under that ContractNumber.

But because the textboxes cannot resize and reposition automatically and there is no specific textbox for each field, I don't know how to display the 3 fields if they are selected. I need to keep the report to one page too.

Please! Any help or suggestion is greatly appreciated!!