473,705 Members | 3,580 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Report with subreport - one-to-many relationship

Hello,

I have a report which uses a subreport. When I run the report, I get
"Enter Parameter Value" error message for "tblGuestRo om". I click ok
and the report seems to work fine.

I narrowed down this error to the Link Master Fields property setting,
when I tried the same scenario with form/subform. It gave me error:
'The Link Master fields property setting has produced this error: The
object doesn't contain the Automation object 'tblGuestRoom'. Error
happened when I clicked 'next record' while viewing what should have
been the last row for subreport's RecordSource table where key matched
main report's current record (see below).

Here is the picture of how things are set up:
--Main report is "rptReport1 ". RecordSource is "qryQuery1" , which uses
2 tables: tblGuestRoom, the table coming up in the error, and
tblBathroom. They both have primary key of RoomNumber.
--Subreport is "rsubReport 1", with RecordSource tblWindow.
TblGuestRoom to tblWindow is a one-to-many relationship, on key
RoomNumber. I think the problem lies with not defining the
relationship from main report to subreport correctly (I am relatively
new to Access, but a programmer). Perhaps I didn't set my
relationships up correctly, or Link Master, or it doesn't know the
one-to-many relationship?? (although seems to be displaying fine).
--On the main report, properties for subreport are as follows:
SourceObject = the subreport. Link Child Fields =tblWindow.Room Number,
and Link Master Fields = tblGuestRoom.Ro omNumber. In Relationships,
there is a 1 to many relationship between tblGuestRoom and tblWindow.
Does the subreport know this, or do I need to tell it that somehow?
Where do I tell it this? I suspect that this is where my problem lies.
The strange thing is, report data looks correct.

Thank you in advance for any help,
Lori

Nov 13 '05 #1
3 7391
Did you include both RoomNumber fields in the Query on which is the main
Report's RecordSource? Because this is a Report and you cannot be updating
the data, one RoomNumber Field will be sufficient, and you will not need to
qualify with the Table name. If it were a Form, where you do need both Key
Fields, you could rename the RoomNumber fields (NewName: OldName in the
Query Builder Grid or use the AS clause in SQL) to make them easy to
distinguish without qualifying with the Table name.

As there can be only one RoomNumber field in the Report embedded in the
Subreport Control, you do not need to qualify it with the TableName, in any
case.

Form / Subform or Report / Subreport work just fine for one-to-many without
being informed. One-to-one is just a "subset" of one-to-many, in this sense.

Sometimes you can create problems for yourself by giving Access more
information than it needs.

Larry Linson
Microsoft Access MVP

"lorirobn" <lo******@yahoo .com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .
Hello,

I have a report which uses a subreport. When I run the report, I get
"Enter Parameter Value" error message for "tblGuestRo om". I click ok
and the report seems to work fine.

I narrowed down this error to the Link Master Fields property setting,
when I tried the same scenario with form/subform. It gave me error:
'The Link Master fields property setting has produced this error: The
object doesn't contain the Automation object 'tblGuestRoom'. Error
happened when I clicked 'next record' while viewing what should have
been the last row for subreport's RecordSource table where key matched
main report's current record (see below).

Here is the picture of how things are set up:
--Main report is "rptReport1 ". RecordSource is "qryQuery1" , which uses
2 tables: tblGuestRoom, the table coming up in the error, and
tblBathroom. They both have primary key of RoomNumber.
--Subreport is "rsubReport 1", with RecordSource tblWindow.
TblGuestRoom to tblWindow is a one-to-many relationship, on key
RoomNumber. I think the problem lies with not defining the
relationship from main report to subreport correctly (I am relatively
new to Access, but a programmer). Perhaps I didn't set my
relationships up correctly, or Link Master, or it doesn't know the
one-to-many relationship?? (although seems to be displaying fine).
--On the main report, properties for subreport are as follows:
SourceObject = the subreport. Link Child Fields =tblWindow.Room Number,
and Link Master Fields = tblGuestRoom.Ro omNumber. In Relationships,
there is a 1 to many relationship between tblGuestRoom and tblWindow.
Does the subreport know this, or do I need to tell it that somehow?
Where do I tell it this? I suspect that this is where my problem lies.
The strange thing is, report data looks correct.

Thank you in advance for any help,
Lori

Nov 13 '05 #2
Hi Larry,

I'm not sure if this is what you meant, but I changed the RecordSource
of my subreport from a table (which had the RoomNumber field) to a
query, where I included all fields except the RoomNumber. This
worked!!!!!!! Thank you soooo much - many hours trying to figure this
one out.

But it did sound like you meant the main report's RecordSource Query. I
did delete one of the tables, leaving just one RoomNumber in the Main
Report, but still got the problem with the subreport (when recordsource
was still a table). Is that what you meant?

I am surprised I had to do this... so if I ever add fields to my table,
then I will always have to update the query to reflect the new fields?

Thank you once again...
Lori

*** Sent via Developersdex http://www.developersdex.com ***
Nov 13 '05 #3
Hi again Larry,

I just tried again, doing exactly what you said. I changed just the
RecordSource Query of the main report, and took out the 2nd Room Number.
This worked BEAUTIFULLY!!!! I didn't have to change the subreport at
all!!!

Thank you very much!

Lori

*** Sent via Developersdex http://www.developersdex.com ***
Nov 13 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1154
by: JRT | last post by:
Have a Report with sub report that shows up ok in print preview - looks like it should in preview When I print I get the error "this function is too complex", ok or help and on ok it prints out only the main report part. Sub report doesn't print, yet both reports print by themselves separately. Anyone have any ideas?
2
1495
by: cymichaelip | last post by:
HI I have a report with a subreport, output like below Report: Subreport: Label 1 FirstData1 SecondData1 ThirdData1 Label 2 FirstData2 SecondData2 ThirdData2 Label 3 FirstData3 SecondData3 ThirdData3
5
2332
by: PeterHardy | last post by:
Hi I'm having a problem removing the title from a subreport. The report is built into a main report but every time you run it there is a title that appears which is the name of the subreport. I cannot seem to find where to remove this and don't want it showing. Could anyone help by pointing me in the right direction. Thanks
3
1507
by: tod4 | last post by:
Hi I have a report with 3 subreports where from I take a few value in VBA On Print event. Some value causes error so i wrote in my procedure such code: On Error GoTo err_bl err_bl: If Err = 3071 Then MsgBox "Report contains wrong value!" Exit Sub
2
2011
by: Henrootje | last post by:
I have a report concerning a customer. In this report we find four subreports. They are in layout identical but data comes from different tabel. Now on the main report I count the totals of each report and sum them; =.!+.!+.! But if of of the subreports has no data for example because it has no data I get an error...
2
2367
by: Jimmy | last post by:
On the subreport, records are grouped by WorkDate. In the WorkDate header there is a textbox named DateCounter with the control source =1 and running sum set to yes. In either the report footer or the WorkDate footer (I tried both with no difference) is a textbox called DateCount, the control source of which is =DateCounter. If this subreport is run as a seperate report, DateCounter shows 1...2...3...etc for each seperate WorkDate and...
3
9153
by: Wayne | last post by:
I have a report containing 2 subreports. The subreports each contain a chart. The whole thing easily fits on one page but a second blank page is consistently being generated. This has nothing to do with the page width being too large because it isn't, and the second blank page displays the report header and footer. I can reduce the depth of the subreports so that they only take up one half of the first page's depth, but the second...
1
2235
by: princesteveis | last post by:
Actually I want a single report by the name "Sales and Purchases Summary Statement" which comprises of a main report name "Purchases" and a subreport name "Sales". I have also created a query for main report (i.e. Purchases) with the following two parameters; PurchasesType and Date Range (i.e. Between PurchasesStartDate and PurchasesEndDate)
4
2064
by: Bill | last post by:
I have a command button that opens the a report. On the report the bulk of the information is in the sub report. I have a check box on the form that if isn't check will cause a field to be invisible on the report. I want to be able to set the corresponding field on the sub report the same way. I can't seem to get the syntax right to change the visible value in the sub report. Right now I'm using "Reports!...
6
1640
by: Kevin | last post by:
On some records, the calculation is correct but on others it is wrong, why? the calculation is as follows (the source of each part of the calculation is below it): txtActualWage =\ txtLaborRevenue =rptLaborSubReport!TotalLaborCost TotalLaborCost =Sum((DateDiff("n",,)/60)*)
0
8767
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
8689
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9273
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9136
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
5933
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4439
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4703
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2486
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2081
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.