Selecting Memo Fields to Display in a Report 1

[dedren]

I am trying to generate a report, using some options that the end user selects from a combo box on a form or, if possible, right from the queries that generate the report.

The combo box has a list of memo fields that the user can choose to use when populating the report. My problem is that the memo fields are stored in separate tables, categorized slightly differently, and I have not been able to figure out how to get the information from the different tables into the report correctly. Below I will try to explain the objects I am working with:

Memo Fields:

[CaseNote] – field located in table [tblCasesMain]

[SARInfo] – field located in table [tblSARlst], additionally this table also has a field called [SARType] that describes [SARInfo]. Example: [SARType] value can be, “Original” or “Supplemental”

[SynopsisInfo] – field located in table [tblSynopsis] additionally this table also has a field called [SynpopsisType] that describes the [SynopsisInfo] field. Example: [SynopsisType] can be, “Original SAR Synopsis”, “Case Note Synopsis”, or “Incident Report”*

Linking Field:

[CaseNum] – Links all of the mentioned tables to [tblCasesMain]

Reports:

[rptIncidentReport] – main report opened containing various other info including the subreport [sbrIRSummary].

[sbrIRSummary]* - subreport that displays one of the chosen memo fields based on the users selection.

Queries:

[qryIRSummary]* - is the query I originally thought could collect all of the memo fields and be used to choose and populate which one displayed on the subreport [sbrIRSummary].

[qryIncident Report] – is used to populate most of the other information in the main report [rptIncidentReport].


*These asterisked fields/controls/objects are not required to be kept and can be changed as needed to accomplish the goal mentioned. Quick list: “Incident Report”, [sbrIRSummary], and [qryIRSummary].


I tried to explain as much as I thought relevant, but please ask if any clarification is needed.

 

[dhookom]

You could add all the memo fields as subreports and use a little code to make one visible based on the combo box selection.

Duane
Hook'D on Access
MS Access MVP

 

[dedren]

Thank you, that is not something I thought of, but wouldn't that mean I would have large spaces on the report when the other subreports are invisible?

 

[dhookom]

If only one would display at a time, you should be able to stack them or set them to very short and allow them to grow.

Duane
Hook'D on Access
MS Access MVP

 

[dedren]

Stacking the reports seems like it would cause other problems with formating, alignment, and future manipulation of the data, but I am very grateful for the advice. If you think there won't be a big issue with formating and future development of the DB, I will try this way. Please advise.

The only other option that is coming to my mind involves me putting all memo fields in a separate table and using them by type. This would involve a physical change to the main table, which I have heard is pretty risky.

 

[dhookom]

If the memo field subreports are similar, I would create a single subreport based on a saved query named "qryMemoFldsForSubrpt". Then prior to opening the report, change the SQL of this query to select from the appropriate table. The changed SQL would always need to have the appropriate field names to match the bound fields in the single subreport.

Duane
Hook'D on Access
MS Access MVP

 

[dedren]

I like this suggestion a lot. I am going to try it out. Thank you!