Pass date value from subtable to query

scottb
4StarLounger
Posts: 402
Joined: 14 Apr 2010, 15:59

Pass date value from subtable to query

Post by scottb »

Hi again,

I am trying to help with an inherited mess.

Form is “Table1”
Record source for the form is a table also named “Table1”
There is a table included at the bottom of form Table1: Object source is “Table.Donation”
Table.Donation is linked to the record source Table1 by field “SER_NUM”
Table.Donation includes several fields, the one I need is “DepDate”

There is a button on the form that opens a report “RptIndividualDonarRcpt”
The record source for the report is: “Qry_PRINT_INDIVIDUAL_Donation_REPORT”
The query has a parameter for DepDate, prompting the user to enter the date (##/##/####) of the record to include in the report.

Is it possible to select the desired record in Table.Donation and have DepDate value for that record pass directly to the query so a date doesn’t have to be manually entered?

Hopefully this makes some sense. Rebuilding the DB isn’t an option at this time.
Thank you for any assistance.
-Scott

scottb
4StarLounger
Posts: 402
Joined: 14 Apr 2010, 15:59

Re: Pass date value from subtable to query

Post by scottb »

Think this might work. Changed the DepDate parameter to [Forms]![Table1]![Child42].[Form]![DepDate].
I'll update if it does to close.
Thanks.

scottb
4StarLounger
Posts: 402
Joined: 14 Apr 2010, 15:59

Re: Pass date value from subtable to query

Post by scottb »

It works but when I try to run another record it retains the previously selected records DepDate. If I close the form and reopen it all is well. Is there a way to "clear" the previous DepDate value?
Thank you!

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Pass date value from subtable to query

Post by HansV »

Do you close the report in between?
Best wishes,
Hans

scottb
4StarLounger
Posts: 402
Joined: 14 Apr 2010, 15:59

Re: Pass date value from subtable to query

Post by scottb »

Yes. I have DoCmd.Close acReport, sExistingReportName

scottb
4StarLounger
Posts: 402
Joined: 14 Apr 2010, 15:59

Re: Pass date value from subtable to query

Post by scottb »

The report isn't showing as open but when I select another record and run again it appears as though it never closed.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Pass date value from subtable to query

Post by HansV »

Without seeing the database it's impossible for me to know what causes the problem.
Best wishes,
Hans

scottb
4StarLounger
Posts: 402
Joined: 14 Apr 2010, 15:59

Re: Pass date value from subtable to query

Post by scottb »

I will strip down a version and attach. Thanks Hans.

scottb
4StarLounger
Posts: 402
Joined: 14 Apr 2010, 15:59

Re: Pass date value from subtable to query

Post by scottb »

I figured it out. I was seeing this when I was exiting out of the sendobject procedure. I moved the close report code to after the error handling and now everything is working. Thanks again for all the help.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Pass date value from subtable to query

Post by HansV »

Best wishes,
Hans

scottb
4StarLounger
Posts: 402
Joined: 14 Apr 2010, 15:59

Re: Pass date value from subtable to query

Post by scottb »

Good article and thank you for sharing but not related. Working on a db used on a stand alone pc. Thank you Hans.