Report with subreport - one-to-many relationship

[lorirobn]

Hello,

I have a report which uses a subreport. When I run the report, I get
"Enter Parameter Value" error message for "tblGuestRoom". 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 "rsubReport1", 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.RoomNumber,
and Link Master Fields = tblGuestRoom.RoomNumber. 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

[Larry Linson]

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.googlegr oups.com...

Hello,

I have a report which uses a subreport. When I run the report, I get
"Enter Parameter Value" error message for "tblGuestRoom". 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 "rsubReport1", 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.RoomNumber,
and Link Master Fields = tblGuestRoom.RoomNumber. 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

[Lorirobn]

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 ***

[Lorirobn]

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 ***