Report shows text from main form based on date

[PavelowFE]

I have a subform that the user selects a date and makes a comment (text box/memo), stored in a table.

I want to be able to show the comment on a report, based on the comment (record) the user has selected on the subform. The user wants to be able to have reports that show the same data, but wants to apply different comments to each.

The date doesn't apply much for the report, but rather an easy/fast way to locate the comment.

As always, this place is great for help and it is very much appreciated.

Thanks,

Vince

 

[smedvid]

Just a couple of quick thoughts...

I recall you can pass an arguments parameter into a report. I've used this method before to display what filter was used against data to display that information in the report header...

In calling routine Use
DoCmd.OpenReport stDocName, acNormal, , lcWhere, , lcWhere
Note: I think the 2nd lcWhere represents OpenArgs...

Use Text box in Report form with value of "=[OpenArgs]"


Or, if the DE form is still open; perhaps reference the field directly? i.e. =Forms!frm_rpt_Property_Summary!txtWhere

htwh,

Steve Medvid
IT Consultant & Web Master

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[smedvid]

Sorry, I missed that you have this in a table...
Perhaps a Sub-Form can be used, or even simply link to that table in your main report form query... Good Luck...

Steve Medvid
IT Consultant & Web Master

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[PavelowFE]

The problem I have is that the user wants to select which comment goes in the report. I have the sub-form where the user can fill out a date and a comment and then use the navigation buttons to go back and forth in the records. I have the report calling up the table, but it wants to put all of the records in there.

I set up sql to try and select the comment based on which one is showing on the sub-form (it remains open). It don't have it with me at the moment, but it's something like this:
WHERE [forms]![switchboard]![comment]

The above statement pulls up all the records, where I just want the one that is showing. Hopefully I'm not too confusing on this.
Thanks for the help.
Vince

 

[dhookom]

If you want to print only a single record, you might want check Print only the current record to a report

If that doesn't work for you, please try to provide more information.

Duane
Hook'D on Access
MS Access MVP

 

[PavelowFE]

Still having problems with this. I swear it's something easy, but I just can't figure it out. I really need to get this done, it's the last thing I have left to fix.

I tried the suggestion above, with no luck. It still outputs all the records in the Comment table:

Dim strDocName As String
Dim strWhere As String
strDocName = "rptComments"
strWhere = "[RunID]=" & me!RunID
DoCmd.OpenReport strDocName, acPreview, , strWhere

Just to recap. I have a main form with two subforms. One subform has two date fields for selection and a submit button. When the user clicks on that, it produces a report of data in a table.
My user wants to be able to put comments on a report when it is generated. He may produce several reports, but wants different comments on each report.
I created a table with a date field and comment field. Made a form and put it as a subform on the main form. The user then selects a date and writes the comment.
My thinking to get what he wants is, when the user uses the first subform that produces the report, whichever comment is showing on the second subform will be the one to show in the report.
The report has a subreport section for the comments. I just have to find a way to tie it in. Any ideas?
Thanks,

Vince

 

[PavelowFE]

Looking at it, I believe an SQL WHERE statement is what is needed.

SELECT Comments.Comments
FROM Comments
WHERE ????

Something that will call only the text that is showing in the subform called "Comments".

WHERE [forms]![switchboard]![Comments]![comments] (but how do I get it to only show the selected comment?

Or, can it be done from a query?

Thanks,
Vince

 

[dhookom]

If you want to filter records on a subreport, you can't use the WHERE CONDITION of the DoCmd.OpenReport. You can add a criteria to the Record Source of the report. If your criteria is coming from a control on a subform you can find the syntax at Refer to Form and Subform properties and controls

Duane
Hook'D on Access
MS Access MVP