SetValue and Now() failing (1 Viewer)

GLese

Registered User.
Local time
Today, 09:53
Joined
Feb 13, 2018
Messages
52
In order to make a timestamp in several of my tables, I have built a macro based on a post on the Microsoft office support page "Store the Date and time when a record is modified"

My Macro is:

SetValue
Item = [DateTimeIn]
Expression = Now()

CloseWindow
Object Type
Object Name
Save = No


This was working fine, but all of a sudden I'm getting an error in the macro window when I try to build a similar expression for a different field (Need the same macro for [DateTimePickup]).

The error I get is "The 'SetValue' macro action has an invalid value for the 'Expression' argument."

There is a little error icon next to the Expression box in the Macro screen and when I hover over it the text box says "Microsoft Access can't parse the expression: 'Now()'."

I feel a bit stuck since I had written the macro earlier for [DateTimeOut] and had no issues until I tried making a third variant.

:confused:
 

Minty

AWF VIP
Local time
Today, 13:53
Joined
Jul 26, 2013
Messages
10,354
Is this a data macro ?

If not, not many of us use macro's so you may be better trying to do this in VBA, if it is based on a form action?
 

GLese

Registered User.
Local time
Today, 09:53
Joined
Feb 13, 2018
Messages
52
Is this a data macro ?

If not, not many of us use macro's so you may be better trying to do this in VBA, if it is based on a form action?

Pardon my being so new to the terminology, I'm not quite sure what you mean by data macro.

What this macro is supposed to do (and did up until whatever threw a wrench in the works) is timestamp the specified field for the record being created/modified by a form.

Ex: I have a form for entering a sample into my table f physical sample records. When the manufacturing operator has entered all necessary information into the form, they will click a "Log In" button which saves the record and closes the window. I had the DateTimeIn macro set on the form properties AfterUpdate, and this populated the current date & time in the DateIn field of my records table.

Does that help clarify?
 

BigHappyDaddy

Coding Monkey Wanna-Be
Local time
Today, 06:53
Joined
Aug 22, 2012
Messages
205
First, much better in describing the issue and desired results.
Second, to answer Minty's question: Yes, I believe this is a macro.

So while you may get some assistance in debugging your macro, the people in this forum are much better versed in using VBA. I understand that you may not understand VBA (or maybe even what it is), but it is much more powerful and flexible than the macro builder.

I highly suggest taking some time and looking into converting your macro to VBA.

Good Luck!
 

GLese

Registered User.
Local time
Today, 09:53
Joined
Feb 13, 2018
Messages
52
First, much better in describing the issue and desired results.
Second, to answer Minty's question: Yes, I believe this is a macro.

So while you may get some assistance in debugging your macro, the people in this forum are much better versed in using VBA. I understand that you may not understand VBA (or maybe even what it is), but it is much more powerful and flexible than the macro builder.

I highly suggest taking some time and looking into converting your macro to VBA.

Good Luck!


I'm not well versed in macros OR VBA, so I'm open to learning either one. I've heard a lot of good things on VBA, and have used a little bit in MSExcel, but I'm definitely a newbie when it comes to all this. Thanks for the advice and encouragement!
 

GLese

Registered User.
Local time
Today, 09:53
Joined
Feb 13, 2018
Messages
52
I'll throw this out there while I am doing some research on my end into a VBA option... (In case someone knows a quick fix)

If I use the built in "Convert Macros to Visual Basic" button in Access 1010, it gives me the following module:

Option Compare Database
'------------------------------------------------------------
' DateTimeIn
'
'------------------------------------------------------------
Function DateTimeIn()
On Error GoTo DateTimeIn_Err
With CodeContextObject
.DateTimeIn = Now()
Beep
MsgBox "Sample Logged In Successfully", vbOKOnly, ""
End With

DateTimeIn_Exit:
Exit Function
DateTimeIn_Err:
MsgBox Error$
Resume DateTimeIn_Exit
End Function
 

BigHappyDaddy

Coding Monkey Wanna-Be
Local time
Today, 06:53
Joined
Aug 22, 2012
Messages
205
Everyone starts as a newbie! The fact that you have been exposed to VBA in Excel is a good first step.
 

BigHappyDaddy

Coding Monkey Wanna-Be
Local time
Today, 06:53
Joined
Aug 22, 2012
Messages
205
So are you still getting an error message using the VBA function?
 

Minty

AWF VIP
Local time
Today, 13:53
Joined
Jul 26, 2013
Messages
10,354
It did look like a Macro. Thank you for confirming.

A data Macro works at table level, and is not commonly used - so ignore that thought for the time being. As suggested you will get a lot more functionality with VBA although the learning curve is steeper than with a Macro.

There is a option to convert Macro's to VBA in the macro menu.

If you do that it will at least do the initial hard work for you, although the code created is a little bit "wordy" it should work.

Post that code up (please use the # code tags in the reply editor) and let us see what it creates.
If you are not already, It may help you to get familiar with what the terms for various "things" in Access are referred to by us so called experts.

Forms generally contain lots of Controls. These can be text boxes, labels, combo boxes, lists, Sub forms, option groups and probably a load of others I've forgotten. Knowing how to refer to these controls will help you enormously.

Bookmark this page http://access.mvps.org/access/forms/frm0031.htm , you will refer to it lots!
 

GLese

Registered User.
Local time
Today, 09:53
Joined
Feb 13, 2018
Messages
52
Okay so thanks to everyone (Minty and BigHappyDaddy) for the push to explore VBA. I'm learning the basics of coding and this seems to be a much better route!

So before I close this thread out (since I'm taking a different route) I have two questions:

1) Do y'all have any recommendations on libraries of code elements I should keep bookmarked?

2) Would it be better to have a button linked to a module/class module where I would use essentially the same code for a button on different forms? Or should I code each button individually?
 

GLese

Registered User.
Local time
Today, 09:53
Joined
Feb 13, 2018
Messages
52
So are you still getting an error message using the VBA function?

After seeing how it converted, I decided to start from scratch and teach myself how to code it to do what I want, rather than relying on the auto conversion. I figure this will benefit me in the long run better if I can learn to make my own, clean code
 

BigHappyDaddy

Coding Monkey Wanna-Be
Local time
Today, 06:53
Joined
Aug 22, 2012
Messages
205
My 2 cents...

1) Do y'all have any recommendations on libraries of code elements I should keep bookmarked?

Personally, when I started learning VBA, I kept this site bookmarked. Search the forum first. 95% of the time someone has already asked your questions (or something similar). The members here are great and very helpful.

2) Would it be better to have a button linked to a module/class module where I would use essentially the same code for a button on different forms? Or should I code each button individually?

I use modules to store code that I will use in other modules / form modules / class modules. Form modules are to store code for that form only. If your button's action is unique to the form, then keep it on he form. If its action can be repeated in other forms / modules / other databases, then keep it in a module.

Class module is a different kind of animal all together. Separate research question! :D
 

GLese

Registered User.
Local time
Today, 09:53
Joined
Feb 13, 2018
Messages
52
My 2 cents...



Personally, when I started learning VBA, I kept this site bookmarked. Search the forum first. 95% of the time someone has already asked your questions (or something similar). The members here are great and very helpful.



I use modules to store code that I will use in other modules / form modules / class modules. Form modules are to store code for that form only. If your button's action is unique to the form, then keep it on he form. If its action can be repeated in other forms / modules / other databases, then keep it in a module.

Class module is a different kind of animal all together. Separate research question! :D

As for the members here, YESx1000000!!! Y'all are far better than some of the other forums I have dug around on *coughcoughStackOverflowcoughcough*


For the modules part, I have some code that will work on a few forms I'm building, so I'll go the modules route. If I make a module that is not unique to that form, I should see it in the modules section on the Access Object sidebar and can reference it by name in other places, correct?

EDIT: For the modules I want to use in multiple places, I should Declare them as Public Subs correct?
 
Last edited:

Minty

AWF VIP
Local time
Today, 13:53
Joined
Jul 26, 2013
Messages
10,354
As a tip - don't name a module the same as any of your functions or subs.
Access will get a headache.
 

GLese

Registered User.
Local time
Today, 09:53
Joined
Feb 13, 2018
Messages
52
As a tip - don't name a module the same as any of your functions or subs.
Access will get a headache.

Wait, don't you write subs and functions in modules to use in other places? IF that's the case then what do you call them?
 

Minty

AWF VIP
Local time
Today, 13:53
Joined
Jul 26, 2013
Messages
10,354
Yes - the Module is effectively a container so I have a module called modEmailFunct and another one called modExcelFunct , and one called modFormFunct

It's just to keep them organised, they are generally pretty generic.
If I need to build a specific excel or csv export routine that isn't generic I would plonk that if modCustomExports
 

GLese

Registered User.
Local time
Today, 09:53
Joined
Feb 13, 2018
Messages
52
Yes - the Module is effectively a container so I have a module called modEmailFunct and another one called modExcelFunct , and one called modFormFunct

It's just to keep them organised, they are generally pretty generic.
If I need to build a specific excel or csv export routine that isn't generic I would plonk that if modCustomExports

Ahh okay, so you use a similar naming convention where modCustomExports is the name of the module and the subroutine inside could be called Custom_Exports()

That makes more sense


Using that naming convention, I have this module: modDateTimeIn

Code:
Option Compare Database
Option Explicit
 
Public Function DateTimeIn() As String
DateTimeIn = Now()
End Function

And I plugged it into this sub:

Code:
Public Sub btnLoginPaperwork_Click()
    modDateTimeIn
    'Timestamps DateTimeIn Field
    Beep
End With
    MsgBox "Repack/Relabel Paperwork Sucessfully Logged In", vbOKOnly, "Thank You!"
    'Popup to confirm data entry to Operator
    DoCmd.Close
    
End Sub

And I am still getting a Compile Error - Expected Variable or Procedure, not module Which then highlights the line
Code:
Public Sub btnLoginPaperwork_Click()

I get the same error if the code in the module is this as well:
Code:
Public Function DateTimeIn()
With CodeContextObject
    .DateTimeOut = Now()
End Function

So am I not defining something right in the subroutine where I am calling up the module? Or is something in my module screwy?
 

jdraw

Super Moderator
Staff member
Local time
Today, 09:53
Joined
Jan 23, 2006
Messages
15,362
In this routine you are just providing the name of the module. You want to execute the function.
Code:
Public Sub btnLoginPaperwork_Click()
    modDateTimeIn
    'Timestamps DateTimeIn Field
    Beep
End With
    MsgBox "Repack/Relabel Paperwork Sucessfully Logged In", vbOKOnly, "Thank You!"
    'Popup to confirm data entry to Operator
    DoCmd.Close
    
End Sub

so you can replace modDateTimeIn with just the function name DateTimeIn or you can be very explicit and use

modDateTimeIn.DateTimeIn where
modDateTimeIn is the module name and DateTimeIn is the function within the module that you want to execute.
 

GLese

Registered User.
Local time
Today, 09:53
Joined
Feb 13, 2018
Messages
52
so you can replace modDateTimeIn with just the function name DateTimeIn or you can be very explicit and use

modDateTimeIn.DateTimeIn where
modDateTimeIn is the module name and DateTimeIn is the function within the module that you want to execute.

I tried it both ways, and using just DateTimeIn doesn't work at all. But if I use modDateTimeIn.DateTimeIn it finishes out the script, but the record is never added to the table?
 
Last edited:

Users who are viewing this thread

Top Bottom