Using the same date in a field across sub forms (1 Viewer)

[hazeleyre23]

Hi,

• My main form : Maintenance
• Subform (within main form): UpdateAgentInfo
• Form I want to populate the date onto: UpdateAgent.

What I want to be able to do is use the FromDate field from my subform to populate on my UpdateAgent form txtUpdate field when it is opened:

I have tried:

Forms![UpdateAgent].txtUpdateFrom = Forms![Maintenance]![UpdateAgentInfo]![ToDate]

However it does not do anything....

Can someone advise on what i am doing wrong please.

thanks

 

[leanpilar]

try

Forms!UpdateAgent.Form.txtUpdateFrom = Forms!Maintenance.UpdateAgentInfo.Form.ToDate

 

[hazeleyre23]

thank you. that works perfectly

 

[hazeleyre23]

Using that code, how do I add a day onto this.

So if the ToDate = 2016/04/04, how do I + a to this to get 2016/04/05.

Thanks

 

[leanpilar]

if your field is a date you can simply use +1 if you get an error use:
DateAdd("d",1,[fieldname])

 

[hazeleyre23]

Thanks, do i put this in the code or in the default value (from the properties sheet)? As when i put it in my VBA Code it says 'Expected: = '

thanks

 

[bob fitz]

Try:

Forms!UpdateAgent.Form.txtUpdateFrom = DateAdd("d",1,Forms!Maintenance.UpdateAgentInfo.Form.ToDate

)

 

[hazeleyre23]

PERFECT!

Thank you

 

[hazeleyre23]

Sorry, but If I wanted to find the Max ToDate would I just add it into the same formula or would this have to be done on it own.

The reason I want to find the Max ToDate is so it always picks the latest date.
thanks

 

[bob fitz]

Sorry, but If I wanted to find the Max ToDate would I just add it into the same formula or would this have to be done on it own.

The reason I want to find the Max ToDate is so it always picks the latest date.
thanks

So if are you saying that you no longer want to add one day to the date on your main form but want to add one day to the most recent date used in the table/query that ToDate is in then you could use the DMax() function to return that value and the DateAdd() function add one day to that. For syntax using DMax() see: http://www.techonthenet.com/access/functions/domain/dmax.php

 

[hazeleyre23]

sorry to be a pain!

Yes, I want to find the max ToDate then add one day onto this.

thank you for your help

 

[bob fitz]

Perhaps something like:

Forms!UpdateAgent.Form.txtUpdateFrom = DateAdd("d",1,DMax("ToDate","[COLOR="Red"]YourTable/QueryName[/COLOR]"))

YourTable/QueryName would need to be changed to your actual Table/Query name.

 

[hazeleyre23]

Thank you so much, you have been very helpful and patient

 

[bob fitz]

No problem. Glad to help