Help - Connect current date from subform to report

[jsonne]

Thanks for all the much needed help in the past - but yet again - I need more

I have a Form Called Contacts that has a sub form called Work Orders that has a field called "Date Sent" I need this field "Date Sent" to show up on my Report that has the same field "Date Sent"
but...........
I only want the current date of "Date Sent" not past or future. In other words if the field in the sub form says 8/4/04 I need only that date to show up on my report not 8/3/04 or 8/5/04. I tried =Now() but still get all other dates. I still have not made it past 1st grade on this programming!

 

[SydneyGeek]

Jan, try this:

Open the form in Design view.
Drag a new Textbox control onto the main form. Name it something useful and descriptive -- eg: CurrentSentDate.
Go to its properties, select the Data tab, and in the Control Source, click the Builder (...) button. You'll see the Expression builder come up. Open the folder for your current form, then the sub form. Scroll down the list of controls (second column) and double-click the one you want (not the label!)
Click OK. Now look at the form. As you select different records in the subform you should see the data for that record appear in the control.
Now, you can use that control's data in the report to give you what you need.

Denis

 

[jsonne]

I need to go back to a twinkle in the eye - I am not even pre-school these days

If I understand correctly you wanted me to go to my
main form "Contacts" design, form view
put in a trext box for CurrentSentDate,
then properties, Data, Control Source, find builder
Open "Contact" folder
Open subform "Workorders"
then choose one from the column on right
then link that to the "Date" control on my report

Okay - now here is where I am stuck - there does not seem to be an expression or I don't know, or don't see it - that will give me date - i.e. what would go after

[Workorders Subform].Form.

Thank you.....

 

[SydneyGeek]

So far, so good...

If I understand correctly you wanted me to go to my
main form "Contacts" design, form view
put in a trext box for CurrentSentDate,
then properties, Data, Control Source, find builder
Open "Contact" folder
Open subform "Workorders"

Here's where you went wrong...

then choose one from the column on right

The column on the right is the inbuilt functions. What you want is the MIDDLE column (sorry - I called it "second" column which probably misled you). In there, you'll find the names of every control on the Workorders subform.
Scroll down that list, find the name of the control with the date that you want to see (possibly [Sent Date]). Double-click THIS name, and you'll see an expression like
=[Workorders].Form![Sent Date]
Then click OK, save the form and go take a look.

Denis

 

[jsonne]

grrrrrrrrrrrrrrrrr.....

Okay got that and it worked fine! But when I tried to link it to my Order Sheet Report – (which is made up of certain details from my main form “Contacts” and certain details from the sub form “Workorders”) - it didn’t work. This is what I did:

Went to Order Sheet Report design view and in the Date Sent box I went to properties clicked on Forms for “Contacts” double clicked “DateSent” and got

«Expr» Forms![Contacts]![DateSent] and in the Date Sent box #Name?

also tried

Forms![Contacts]![DateSent] and in the Date Sent box #Error?

=[Contacts]![DateSent] and got “Enter Parameter Value” box and in the Date Sent box #Error

=[Workorders Subform].Form!DateSent (this is the same code that is in the new text box that you just helped me with) and got “Enter Parameter Value” box and in the Date Sent box #Name?

I am reduced to a speck in the Milky Way. Thanks again for your patience and help.

 

[jmersing]

Hi Jan,

If you have the time and you would like to zip it and send it, I'll take a look. Just make the backcolor of the fields that are not working yellow or something that stands out.

Still have my address?

Jim

 

[SydneyGeek]

Jan, what is the name of the control on the MAIN form -- the one that pulls the data from the subform? This is the control that you should reference in the report. The syntax =Forms![Contacts]![DateSent] (with the correct control name) should do the trick as long as the form is still open when you run the report.

Denis

 

[jsonne]

on the Main form the control is
=[Workorders Subform].Form!DateSent

I have that listed on the properties control in the Order Report for Date Sent.

If I have both the form and report open it still gives me all dates not just current date.

 

[SydneyGeek]

It looks like you are referencing the subform control from the report. When you created the control on the main form, what did you call it? Check its Properties (the Other tab) and take a look. It may be called something unhelpful like Text184. Change the name to something descriptive, save the form, and refer to this name from the report.

Denis