Setting ControlSource in VBA (1 Viewer)

MarcVS

New member
Local time
Today, 11:27
Joined
Aug 20, 2019
Messages
3
I'm trying to set the ControlSource of a TextBox in VBA.
Normally this is simple:
Me.TextBox.ControlSource = "=Date()"

But...

I need to use the DateAdd function:

Me.TextBox.ControlSource = "=DateAdd('w';1;Date())"

This gives an error: 'the syntax of the expression you gave is invalid'
the problem is the '=' sign. Without that I have no error, but of course, the ControlSource is not right then... (it gives #name?")
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:27
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF! Just a guess but try it this way:
Code:
Me.TextBox.ControlSource = "=DateAdd(""w"";1;Date())"
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:27
Joined
May 7, 2009
Messages
19,169
maybe change the semi colon to comma.
 

MarcVS

New member
Local time
Today, 11:27
Joined
Aug 20, 2019
Messages
3
>Me.TextBox.ControlSource = "=DateAdd(""w"";1;Date())"
This gave the same error

>maybe change the semi colon to comma.
That worked.

But...

The formula is more complex, in fact it is:

Me.TextBox.ControlSource = "=DateAdd('d',-1,DateAdd('m',9,[Formulieren]![frmFaseringCBMI]![datEffectieveStartdatumIn]))"

It gives no error but says #Name#

Now the problem is [Formulieren]![frmFaseringCBMI]![datEffectieveStartdatumIn]

It works when I put in in the controlsource property of the textbox, but not when I set it in VBA...
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:27
Joined
May 7, 2009
Messages
19,169
remove the variable from the quote:
Code:
Me.TextBox.ControlSource = "=DateAdd('d',-1,DateAdd('m',9," & [Formulieren]![frmFaseringCBMI]![datEffectieveStartdatumIn] & "))"
 

MarcVS

New member
Local time
Today, 11:27
Joined
Aug 20, 2019
Messages
3
I had to change it to

Code:
"=DateAdd('d',-1,DateAdd('m',9," & Me.datEffectieveStartdatumIn & "))"

but the result is not what was expected: (01/01/1900)
So I changed it to

Code:
"=DateAdd('d',-1,DateAdd('m',9,#" & Me.datEffectieveStartdatumIn & "#))"

And now it works.

Thank you for your help.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:27
Joined
Oct 29, 2018
Messages
21,358
I had to change it to

Code:
"=DateAdd('d',-1,DateAdd('m',9," & Me.datEffectieveStartdatumIn & "))"

but the result is not what was expected: (01/01/1900)
So I changed it to

Code:
"=DateAdd('d',-1,DateAdd('m',9,#" & Me.datEffectieveStartdatumIn & "#))"

And now it works.

Thank you for your help.
Hi. Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom