Test for new record

[chanman525]

Hey All,

Been looking for a post to try and help me on this but can't seem to get it located, so I thought that I would go ahead and post something.

I am looking for a way to send a notifier when a new record has been successfully added to form.

Currently what I have is when a record is updated it prompts the user if they are sure they want to save the record, not a biggy. If they close the record (project database that has status' of open, close or postponed)it sends an e-mail notifying the selected e-mail particpants that the project was closed.

So what I am looking for is when a new record is entered and successfully saved it will send a mailer out noting again the select receipients that a new project has been entered.

I tried testing " if me.newrecord then '"mail it" on the after and before update but it always sees it as an existing record.

This is what I currently have in place. The msgbox is just for testing to know where I am falling in the "if" statement.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If MsgBox("You have updated this record! Do you want to save changes before leaveing?", vbOKCancel, "Record Updated") = vbCancel Then
Me.Undo
Else
If Me.NewRecord Then
Call MailProject(Me.ID, "New")
Else
MsgBox "not a new record"
End If
End If
End Sub


Thanks for your time on this, and let me add that this place rocks - I have learned so much through this forum.

 

[PWise]

try
Private Sub Form_BeforeUpdate(Cancel As Integer)
If MsgBox("You have updated this record! Do you want to save changes before leaveing?", vbOKCancel, "Record Updated") = vbCancel Then
Me.Undo
exit sub
end if

If Me.NewRecord Then
Call MailProject(Me.ID, "New")
Else
MsgBox "not a new record"
End If

End Sub

 

[chanman525]

I tried that and it did not help, however I did realize that the error was in my SNMP mailer sub, I forgot to define the email object as a CDO.Message.

So for future reference this code works as long as you have the rest of the code set right!!

Thnx for the quick response though pwise

 

[TheAceMan1]

How are ya chanman525 . . .

For a new record [blue]Me.NewRecord[/blue] is [blue]true[/blue] in the BeforeUpdate event because the record has yet to be comitted. Its always false in the AfterUpdate event because it is comitted! What you need is a way to hold onto the value of [blue]Me.NewRecord[/blue] during BeforeUpdate. This can be accomplished with a [blue]Public Variable[/blue] in the form. So try this:

In the [blue]delaration section[/blue] of the form add the variable flgNew. Should look like:

[blue]Option Compare Database
Option Explicit

Private flgNew As Boolean[/blue]

Then overwrite the BeforeUpdate/AfterUpdate routines with the following:

[blue]Private Sub Form_AfterUpdate()
   
   If [b]flgNew[/b] Then
      Call MailProject(Me.ID, "New")
   Else
      MsgBox "not a new record"
   End If

   flgNew = False
   
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
   Dim Msg As String, Style As Integer, Title As String, DL As String
   
   DL = vbNewLine & vbNewLine
   Msg = "You have updated this record!" & DL & _
         "Do you want to save changes before leaveing?"
   Style = vbInformation + vbOKCancel
   Title = "User Action Required! . . ."
   
   If MsgBox(Msg, Style, Title) = vbOK Then
      [b]flgNew[/b] = Me.NewRecord
   Else
      [green]'Call MailProject for Cancel[/green]
      Cancel = True
      Me.Undo
   End If
   
End Sub[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884: