DateAdd to a field in access

I have a button in Access that copies one record and then saves it in the same table.


DoCmd.RunCommand acCmdSelectRecord
   DoCmd.RunCommand acCmdCopy
   DoCmd.RunCommand acCmdRecordsGoToNew
   DoCmd.RunCommand acCmdSelectRecord
   DoCmd.RunCommand acCmdPaste
   
   MsgBox "Re-Evaluation Created", vbInformation, ""

The table is called Initial_Eval, and the field is called Eval_date.

I want to change the date by 1, thus increasing the day. I'd like to add a command in the above script to change the date by 1 using the DateAdd.

What's the best way to do this?

So far I have:
Dim DateX As Date
DateX = Dlookup("Eval_date", "Initial_Eval","")
DateAdd("d",1, DateX)

Open in new window


But I'm not even sure that's right, and I know I need to re-store the value after modifying it.

Any ideas?
darantaresAsked:
Who is Participating?
 
IrogSintaConnect With a Mentor Commented:
I believe you gave the textbox control that holds Apt_Date a different name.  You need to use the name of the textbox control when using the Me keyword which is used to refer to controls on a form.  Or you can remove that keyword from your line of code.

Apt_Date = DateAdd("d", 0, Apt_Date)
0
 
IrogSintaCommented:
Since you are already on the new duplicated record, you can just add this:
   Me.Eval_date = DateAdd("d", 1, Date)
   Dirty = False

Open in new window

0
 
darantaresAuthor Commented:
Ok, so I tried the above and I have this:

 DoCmd.RunCommand acCmdSelectRecord
   DoCmd.RunCommand acCmdCopy
   DoCmd.RunCommand acCmdRecordsGoToNew
   DoCmd.RunCommand acCmdSelectRecord
   DoCmd.RunCommand acCmdPaste
   Me.Eval_date = DateAdd("d", 1, Date)
   Dirty = False

Open in new window


But instead of taking the last date (like if the Record was from 12 Dec), it takes the current date and adds 1 to it. So it copied the record and then the script put 3 Jan in there (and today is the 2nd of Jan) instead of it being 13 Dec.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
IrogSintaCommented:
Use this:
   Me.Eval_date = DateAdd("d", 1, Me.Eval_Date)
0
 
darantaresAuthor Commented:
Brilliant!

So, if I have a number stored in a field, and the field type is set to number, if I want to add 1 to it I thought it would look like this

Me.Current_visit = [Me.Current_visit] + 1

Open in new window


But I'm getting an error with that too. Why wouldn't that work?
0
 
IrogSintaCommented:
What error message are you getting?
0
 
darantaresAuthor Commented:
The MS Visual Basic for Applications opens up and then says

Compile error:

Method or data member not found.
0
 
IrogSintaCommented:
Do you have a textbox named Current_visit in your form?
0
 
darantaresAuthor Commented:
I don't, it's just called Visit
0
 
IrogSintaCommented:
If Visit is bound to a field in your table and you want to increment it by 1, you would use:
Me.Visit = [Me.Visit] + 1
0
 
darantaresAuthor Commented:
Yea, it's still not going.

I've attached it here for you to look at if you want.

If you bring up a record (I was testing with Dora TheExplorer), if you click on Initial Evaluation, and then Create re-Evaluation, it copies the record and adds 1 to the date like it's suppose to.

I took that code (to add the date) and put it on the Daily Note Form/Tab, and that same code to copy the date and add 1 gets that Method error, as well as when I try to increment the visit +1

So, what should happen on the Daily Notes page, when you click "New Note/Copy Current" it should copy the note, add 1 to the date and increase the Visit by 1.

Any ideas what I'm doing wrong?
V3.accdb
0
 
IrogSintaCommented:
Can you save your database as 2007 version.  I don't have 2010.
0
 
darantaresAuthor Commented:
I just tried and it won't let me because it says it uses features that are only available in access 2010
0
 
darantaresAuthor Commented:
Do I need to declare the variables? I've been reading some stuff about that.

For the current form, the code is.
DoCmd.RunCommand acCmdSelectRecord
  DoCmd.RunCommand acCmdCopy
  DoCmd.RunCommand acCmdRecordsGoToNew
  DoCmd.RunCommand acCmdSelectRecord
  DoCmd.RunCommand acCmdPaste
  Me.Apt_Date = DateAdd("d", 0, Me.Apt_Date)
  Me.Current_visit = [Me.Current_visit] + 1
  Dirty = False
MsgBox "Re-Evaluation Created", vbInformation, ""

Open in new window

0
 
IrogSintaCommented:
Go ahead and save a copy of your database in 2007.  Don't worry about the warning.  I just need to see what you have.
0
 
darantaresAuthor Commented:
It won't even give me the option, unless there is a way I don't know how.

When I do a Save As, I can't change the file type or hit the drop down to select "All Files" and then change the extension.

If I do a Save & Publish, and Save Database As, and then Acess 2002-2003 .mbd, an error pop up comes and the only option is OK.

I've attached a screen shot of the error, let me know if it's any assistance at all.

ErrorError 2Error 3
0
 
darantaresAuthor Commented:
Brilliant! It's working perfectly now, for the changes to both variables. Thanks a ton, you've been a lifesaver.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.