Need report to crossreference... 1

[Neowynds]

I am creating a Security Incident Report database in Access2000.

I have been working on this database for approximately two weeks now and I finally have all the bugs worked out of the forms and tables and relationships, they all work great now. My problem now is that incident reports can have multiple parties involved with them.

I have linked my PartyInfo table to SIR (main table stands for Security Incident Report)and have all the relationships correct. In the party info the Primary key is PartyID which uniquely identifies each party. Also there is a foreign key called ReportID.

When a party member is filled out in the PartyInfo subform (which is a subform of form SIR) the particular record that the parties were involved in is recorded in the ReportID field on the PartyInfo table as well as the SIR table.

I need to have a report so that when I call it up It has all the report details as well as all party members with that ReportID.

I am having trouble with this, as this is the first time I have had to cross reference two keys from two tables that did not have an equal amount of records. Am I going to have to base this off a query? Or am I just going about everything all wrong? and if its the query thing how? I am terrible with queries. (Thats the main reason it took me two weeks and not one)


Thank you for your time,

Neowynds

 

[ddean68]

Neo,

Have you tried making a report to display the Report details and then a subreport that would be linked by this report_ID , displaying all Party information linked to this report_ID?


David

 

[Neowynds]

ddean68

Thank you for the quick response,

Yes I tried that however the way the report looks is not the way my manager wants it. He would like it all layed out nicely with no Scroll bars and stuff on the sides of the subreport, which looks suspiciously enough exactly like a subform in the report. I know that I can do it using the report wizard. After that though it is a cosmetic thing and the fact that the subreport wont self expand with extra records, instead it has a scroll bar. My manager was wanting to be able to run the report and print it out if need be, and have it be ready for filing or passing on to corporate security big-shots. regardless of how many Partys are involved. If I could clean up the way it looks and get it to self expand and contract the way a text/memo field does I would be more than willing to go the subreport route.


Thanks again for wading through this I have just spent the last 15 hours straight (except for smoke and caffeine breaks) working on nothing but this database. Thank god Im on hourly.

Driven nearly to the point of exhaustion,
Neowynds
Neowynds

 

[GTLoco99]

I have built many reports with subreports and never have experienced the problem you are having. Go to the properties on the subreport and look for a Can Grow property and make sure it is set to yes. That may fix it

Thanks
GTLoco

 

[ddean68]

OK, I was guessing it would not be that simple. I researched this a bit and came accross some information that may help you. This was retrieved from other sources, however I hope it helps you out. I have never done a mail merge, I did briefly look into it and it may do the trick though.


Question: "Is there a way to autosize an ole control object frame to
fit itself with the ole size in ?(I do not mean the
clip,stretch,zoom properties)
The critical point is my subreport is continuous.
Actually I am trying to make a test book (for educational
purpose).Every question is prepared in MS Word. And all
the questions are in MS Access table and the questions
(prepared by MS Word) are in OLE fields. So because of the
varying size of the questions, at the report; the OLE size
must fit the question size.
In order To get a well designed, fix font sized and
continuous report, OLE size must vary with the question
size like a textbox's can grow property."


Answer: "No. OLE objects and frames can not be resized at run time.. Only in design
mode. If you have that much word data, that it would be better to create a
Word Document as a formatted mail merge template and simply pull the data in
from Access (e.g. the verbiage)."

 

[ddean68]

As the poster above my last post states though, I have never had this issue. I never rule out the fact that it is an issue I have never encountered though.

 

[Neowynds]

Thanks for the responses,

But this all seems way to complicated. Is there no simple way to run a report that will display all parties involved in a particular incident, without the use of a subreport?

If not I will just go with the subreport and let my manager know that there is no other way.

Thanks again for your time,

Neowynds

 

[dcurtis]

If I understand your question correctly, you can most certainly do what you want. To make sure I understand correctly, when a new Security Report is generated, the ReportID is the PK in the SIR Table. You then copy that number to the party table to a field called ReportID.

The problem is with the layout (format) of the report. I was not able to duplicate the scroll bars on my subreport no matter how small I made it. A thing to note, as stated above, is that the CanGrow property was set to yes.

If you are still having a problem with the report and are able to send me a copy (zipped, stripped of all 'personal or sensitive' data) I will look at and post an answer here. My email is r6mast "at" yahoo.com

 

[Neowynds]

Thank you for the quick response,

I have fixed the report problem, however now that I have the database completed I have a small problem. I designed this in access 2000. My manager wants it in access 97. The problem is a small one. In the form that the security officer will use a combo box to enter the post. when they select a post the address is automatically filled in the address section. This part works in access 2000, but not in 97. in the data tab of properties on the address section I have

=[LocationID].Column(2)

I am confused. Am I going to run into this problem with more critical items as well now that I have the database in access 97?

Thanks again for your time,
Jeramy

Neowynds

 

[mbuell]

Two thoughts:

Scrolling subreports: if this is not a "can grow" property issue, I seem to remember many years ago having had a similar prob in A2. If I recall it happened because I didn't create the subform properly. Used std procedure for recreating the sub and bingo.

A97 vs. A2000: lotsa differences. Mostly in coding. If no coding, no major problems going either way. Maybe some formatting issues, but none I know of. All coding will need recompiling and verification. Object/control reference syntax may be included in my meaning of "coding". I moved the other direction, and remember spot problems all over the place.

Mark
<O>
_|_