problem getting a value from Date()

[Paul]

I'm trying to write a line of code that will update a date field (txtDate)
in a form to the current date whenever a value in another field is changed.
To that end, I've tried using the following:

Private Sub Activity_AfterUpdate()
Me!txtDate = Date()
End Sub

but it doesn't do anything. That is, it doesn't enter today's date in
txtDate.

I've also noticed that in the Immediate pane, ?Date() produces the current
Date, but MsgBox Date() produces an error becuase Date() is null.

Why does VBA recognize Date() in the immediate pane as having a value of
today's date, while the MsgBox function thinks it has no value? And what
can I do to reset txtDate to today's date whenever the value in the Activity
field is updated?

Thanks in advance,

Paul

 

[Arvin Meyer [MVP]]

Your code should work. Your MsgBox example should be:

? MsgBox(Date())
or
? MsgBox(Date)

You can't use a msgbox as a statement in the Immediate Window, you need to
use the function.

 

[Paul]

Thanks for replying to my message, Arvin, but in fact the code doesn't work.

I didn't mean to suggest that I tried using MsgBox Date() in the Immediate
Window. I only used ?Date() in the Immediate Window, and it does work.

I tried using

MsgBox Date

in the VBA procedure in an effort to debug the problem, but it produces the
error "Invalid Outside Procedure."

I get the same error "Invalid Outside Procedure"when I try to use

Me!txtDate = Date()

in the code as well.

Can anyone tell me why I'm getting this error message when I try to use the
Date function in this procedure?

Thanks
Paul

 

[Paul]

The point I was trying to make about getting ?Date to work in the Immediate
Window while Me!txtDate = Date() produces an "Invalid Outside Procedure"
error is that the Immediate Window recognizes Date() as a valid function
that returns the current date, while VBA does not recognize it as a valid
function in a line of code.

 

[John W. Vinson]

Why does VBA recognize Date() in the immediate pane as having a value of
today's date, while the MsgBox function thinks it has no value? And what
can I do to reset txtDate to today's date whenever the value in the Activity
field is updated?

My guess is that you have a table field or a form control named Date, and
Access is getting confused about whether you mean the control/field or the
builtin function.

 

[Paul]

Ah! - that's it.

Thanks, John.

My guess is that you have a table field or a form control named Date, and
Access is getting confused about whether you mean the control/field or the
builtin function.

 

[Stuart McCall]

Thanks for replying to my message, Arvin, but in fact the code doesn't
work.

I didn't mean to suggest that I tried using MsgBox Date() in the Immediate
Window. I only used ?Date() in the Immediate Window, and it does work.

I tried using

MsgBox Date

in the VBA procedure in an effort to debug the problem, but it produces
the error "Invalid Outside Procedure."

I get the same error "Invalid Outside Procedure"when I try to use

Me!txtDate = Date()

in the code as well.

Can anyone tell me why I'm getting this error message when I try to use
the Date function in this procedure?

Thanks
Paul

To you it may seem that your debugging code is inside a procedure, bit it
isn't. Check that all your Sub and Function procedures are correctly
declared and terminated, ie:

Sub Something()
'Code
End Sub

Function SomethingElse()
'Code
End Function

Also look for 'orphaned' terminators, like an End Sub line with no
corresponding Sub Something() line above it.

 

[Paul]

True, but VBA picks that up as soon as you try to run a procedure or compile
the code.

In my case, the problem was that I had a table field named "Date," as John
Vinson pointed out.

 

[Stuart McCall]

True, but VBA picks that up as soon as you try to run a procedure or
compile the code.

In my case, the problem was that I had a table field named "Date," as John
Vinson pointed out.

<SNIP>

Ah. Yes John's good at spotting those. One to be remembered.
Glad you're up & running.

 

[Arvin Meyer [MVP]]

Glad you found it. Be aware that in the Immediate window, you need a
question mark (?) to return a result, i.e. to run any function, while
leaving the ? out will execute a sub. So:

MsgBox Date

will not work. But:

?MsgBox(Date())

will work, as will:

?MsgBox(Date)

The MsgBox statement will not work in the Immediate Window. The MsgBox()
function will.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Jeanette Cunningham]

Paul,
I am guessing that you have a field called Date in your table. If that is
the case, access can get confused because Date is also the name access uses
for the Date function.
Use ActivityDate or ADate as a field name for this field instead.

You need to make sure that the control called Activity is being updated for
the txtDate to have its value set to date.
If txtDate is an unbound control, make sure to set its format property to
one of the date/time formats on the control's property dialog | format tab.


Jeanette Cunningham -- Melbourne Victoria Australia

 

[Paul]

Thanks for the further explanation and clarification, Arvin.