AfterUpdate Issue

pdvsa used Ask the Experts™

I have code running to insert data to a table based on whether or not the [DateOfIssueSB] is Null or Not Null.

If Null then I want to run strSQL = "INSERT INTO tblLCAmendHistory code
If not Null then I dont want to run it.  

It seems to run all the time meaning that if it is both NULL and NOT NULL  [DateOfIssueSB].  I only want the INSERT to run if NULL.  

I had the code on both the AFTERUPDATE and BEFOREUPDATE thinking that I should not have th code on BEFOREUPDATE but rather AFTERUPDATE.
When on BEFOREUPDATE it would not give me the error of "VARIABLE NOT DEFINED" but after putting that code on AFTERUPDATE it gives me the error.  

I am thinking that the code should be on the AFTERUPDATE and it will INSERT only when ISNULL [DateOfIssueSB] but I do get the error msg below when doing this.

Any ideas on how to INSERT only if ISNULL  [DateOfIssueSB]?

the form is in continous forms mode
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
pdvsaProject finance


here is the complete code
Private Sub txtDateOfIssueSB_AfterUpdate()

      Dim strSQL As String
  'DoCmd.RunCommand acCmdSaveRecord
  CurrentDb.Execute "qryUpdateEUNameMS", dbFailOnError
    If IsNull(Me.txtLCNo) Or Me.txtLCNo = "" Then
        MsgBox "Enter in the LC No before Entering the Issue Date (need it for Insert Event) and it must be the real invoice number.  HIT ESCAPE and enter LCNo"
        Cancel = True
   'Exit Sub
    End If
    'GreyL 3/26/11: it is not working for if date is null (deleting the date of Issue)
   MsgBox "*" & Me.txtDateOfIssueSB & "*"
   If IsNull(Me.txtDateOfIssueSB) = False Then
   'If Nz(Me.txtDateOfIssueSB, "") = "" Then
        'strSQL = "INSERT INTO tblLCIssueDateHistory (fldDate, letterOfCreditID, EndUserID, IssueDate, LCNo, Amount) VALUES (#" & Format(Date, "m\/d\/yyyy") & "#," & Me!ID & "," & Me!EndUserID & "," & Null & ",'" & HyperlinkPart(Me!LCNo, acDisplayText) & "'," & Me!Amount & ")"
         strSQL = "INSERT INTO tblLCAmendHistory (AmendedDate, letterOfCreditID, EndUserID, LCNo, AmendType) VALUES (#" & Format(Date, "m\/d\/yyyy") & "#," & Me!ID & "," & Me!EndUserID & "," & "'" & HyperlinkPart(Me!LCNo, acDisplayText) & "'," & 14 & ")"
        MsgBox "this LC has been set to 'Issued' in the db. Once it is released then the date of release must be updated and who the LC was sent to", vbInformation
        strSQL = "INSERT INTO tblLCIssueDateHistory (fldDate, letterOfCreditID, EndUserID, IssueDate, LCNo, Amount) VALUES (#" & Format(Date, "m\/d\/yyyy") & "#," & Me!ID & "," & Me!EndUserID & ", #" & Me!DateOfIssueSB & "#,'" & HyperlinkPart(Me!LCNo, acDisplayText) & "'," & Me!Amount & ")"

    End If
  Debug.Print strSQL
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
End Sub

Open in new window

Cancel is an output parameter passed to the Before_update event.  It does not exist in the after_update event.   So, if you want to use after_update, remove line 10: cancel = true.  Or, if you DO want to cancel the update use the before_update event.

All of these look correct to me, so I don't see your other problem:

  If IsNull(Me.txtDateOfIssueSB) = False Then
  If Nz(Me.txtDateOfIssueSB, "") = "" Then

    If IsNull(Me.txtDateOfIssueSB) Then
    if len(me.txtdateofIssueSB) > 0 then

You can have Cancel only in Before update event. If you will build code for this event you will see Cancel as parameter of Sub

p.s.  your code does run an unconditional insert on line 37:  docmd.runsql strSQL.

But the insert is to a different table, depending on me.txtDateOfIssueSB
pdvsaProject finance


I remvoed the Cancel part
I also had to change the FALSE to TRUE and I think it worked after that or at least when a record is present.  Not sure how it will act if new record.  

Think I am good for now.  
thank you for the help.

Thanks for pointing out that the update is to 2 diff tables.  You have a good eye. I forgot I was doing this.   I do want it this way though.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial