Kamis, 04 Februari 2016

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

 

Jim,
 
I just think it's good to be explicit in my coding. I could be more spartan by not creating a variable to store the change date (datChange).  I named it datChange since it seemed to correspond with the name of the field in your table.
 
Again, if you are using DLookup() to pull the date from a table, you will need to add a filter/where to the DLookup() that identifies the correct record.
 
Duane Hookom, MVP
MS Access
 

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Thu, 4 Feb 2016 18:02:37 +0000
Subject: Re: [MS_AccessPros] Re: Form Load or On Open question



Duane,

So I put this code in the production database with the correct names and it works but I am trying to figure out your datChange part of the code in the test database.



If DateValue(Me.txtTodaysDate) = #2/4/2016# Then

 Dim NTable As String
 Dim OTable As String
 
 NTable = "R&D-CURRENTEMPLOYEES - " & [Forms]![frmMainTransferForm].[txtTodaysDate]
 OTable = "R&D-CURRENTEMPLOYEES"

    DoCmd.CopyObject , "R&D-CURRENTEMPLOYEES - " & [Forms]![frmMainTransferForm].[txtTodaysDate], acTable, "R&D-CURRENTEMPLOYEES"
  
   'Email message to casey and jim to let them know the table is copied
  
Dim objol As New Outlook.Application
Dim objmail As MailItem
Set objol = New Outlook.Application
Set objmail = objol.CreateItem(olMailItem)
With objmail
.To = "luvmymelody@yahoo.com"
.Subject = OTable & " " & "Copied as " & NTable
.Body = "The " & " " & OTable & " " & " table has been copied with the name of " & " " & NTable
.NoAging = True
.Display
End With
Set objmail = Nothing
Set objol = Nothing
SendKeys "%{s}", True
MsgBox "The R&D-CURRENTEMPLOYEES table has been copied with the name of R&D-CURRENTEMPLOYEES-1-2-2017" & vbCrLf & _
" and a message has been sent to notify the users of the copied table."
  
End If





 
Jim Wagner


On Thursday, February 4, 2016 10:57 AM, "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:


 
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 (9)

.

__,_._,___

Tidak ada komentar:

Poskan Komentar