Kamis, 04 Februari 2016

RE: [MS_AccessPros] Re: Form Load or On Open question

 

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



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



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









__._,_.___

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:

Poskan Komentar