ACCESS FEED: Re: [MS_AccessPros] Re: Form Load or On Open question

Kamis, 04 Februari 2016

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