Jim,
I should have also mentioned this code will run every time the form is loaded which might copy the table multiple times in the target date. I typically store a value in a table that determines if the action has been performed. The code checks the date, checks to see if it has already been performed, and either performs the action or not.
Duane Hookom, MVP
MS Access
To: ms_access_professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Thu, 4 Feb 2016 11:33:51 -0600
Subject: RE: [MS_AccessPros] Re: Form Load or On Open question
I should have also mentioned this code will run every time the form is loaded which might copy the table multiple times in the target date. I typically store a value in a table that determines if the action has been performed. The code checks the date, checks to see if it has already been performed, and either performs the action or not.
Duane Hookom, MVP
MS Access
To: ms_access_professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Thu, 4 Feb 2016 11:33:51 -0600
Subject: RE: [MS_AccessPros] Re: Form Load or On Open question
Jim,
You must use DLookup() or a recordset to pull the value of a field from a table or query. Consider using something like:
Dim datChange as Date
datChange = DLookup("DateOfChange","tblNewYearDateInformation")
If DateValue(Me.txtTodaysDate) = datChange Then
Or
If Date = datChange Then 'why use the control if it will be today's date
This assumes there is only one record in tblNewYearDateInformation or you don't care which record is referenced.
Duane Hookom, MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Thu, 4 Feb 2016 17:11:46 +0000
Subject: Re: [MS_AccessPros] Re: Form Load or On Open question
You must use DLookup() or a recordset to pull the value of a field from a table or query. Consider using something like:
Dim datChange as Date
datChange = DLookup("DateOfChange","tblNewYearDateInformation")
If DateValue(Me.txtTodaysDate) = datChange Then
Or
If Date = datChange Then 'why use the control if it will be today's date
This assumes there is only one record in tblNewYearDateInformation or you don't care which record is referenced.
Duane Hookom, MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Thu, 4 Feb 2016 17:11:46 +0000
Subject: Re: [MS_AccessPros] Re: Form Load or On Open question
Duane,
The hard code date is a testing until I get this part working. I have a table named tblNewYearDateInformation that has the dates I would like to have the code run. It will always be 1/2/???? of every year. How would I reference the dates in the table? I tried [Tables]![tblNewYearDateInformation].[DateOfChange] but it seems that table references do not work like Forms does.
Jim Wagner
On Thursday, February 4, 2016 9:54 AM, "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Now() contains a time element #2/4/2016 10:50:42 AM# so it will only briefly equal a date only expression. Plus you are comparing a date with a string which is never a good idea. Always have the same data type on both sides of the "=".
I would try:
If Format(Me.txtTodaysDate,",m/d/yyyy") = "2/4/2016" Then
Or better:
If DateValue(Me.txtTodaysDate) = #2/4/2016# Then
However I don't understand why you would hard-code "2/4/2016" into your VBA. I assume this is just for testing.
Duane Hookom, MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Thu, 4 Feb 2016 08:41:50 -0800
Subject: [MS_AccessPros] Re: Form Load or On Open question
So I tried to see if the code even sees the date and changed the code to below. I get the Ho there message box. So it does not see the date probably. The text box has a control source of =Now()
Private Sub Form_Load()
If Me.txtTodaysDate = "2/4/2016" Then
' DoCmd.CopyObject , "Jim", acTable, "tblMessages"
MsgBox "Hi there"
Else
MsgBox "Ho there"
End If
MsgBox "The tblMessages table has been copied with the name of tblMessages-1-2-2017" & vbCrLf & _
" and a message has been sent to notify the users of the copied table."
End Sub
I would try:
If Format(Me.txtTodaysDate,",m/d/yyyy") = "2/4/2016" Then
Or better:
If DateValue(Me.txtTodaysDate) = #2/4/2016# Then
However I don't understand why you would hard-code "2/4/2016" into your VBA. I assume this is just for testing.
Duane Hookom, MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Thu, 4 Feb 2016 08:41:50 -0800
Subject: [MS_AccessPros] Re: Form Load or On Open question
So I tried to see if the code even sees the date and changed the code to below. I get the Ho there message box. So it does not see the date probably. The text box has a control source of =Now()
Private Sub Form_Load()
If Me.txtTodaysDate = "2/4/2016" Then
' DoCmd.CopyObject , "Jim", acTable, "tblMessages"
MsgBox "Hi there"
Else
MsgBox "Ho there"
End If
MsgBox "The tblMessages table has been copied with the name of tblMessages-1-2-2017" & vbCrLf & _
" and a message has been sent to notify the users of the copied table."
End Sub
__._,_.___
Posted by: Duane Hookom <duanehookom@hotmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (7) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar