asked on

AfterUpdate Issue

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
untitled.JPG

pdvsa

ASKER

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
        
    Else
        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

ASKER CERTIFIED SOLUTION

dqmq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

als315

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

dqmq

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

pdvsa

ASKER

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.