none
Insert Statement RRS feed

  • Question

  • The below insert statement is not working. I have looked and looked. I have quotes but it is still not inserting into the dbase.

    data type is text

    Am I using the wrong event?

    Please help!!! Thanks!!

    Private Sub TRANSACTION_AfterUpdate()

    If Me.TRANSACTION = "Inquiry" Then
    Me.BOX_NUM.Enabled = False
        Me.OFFSITE_BOX_NUM.Enabled = False
        Me.BARCODE_NUM.Enabled = False
        Me.LBL.Enabled = False
    If Me.STATUS = "BEFORE" Then
    CC = "DENY"
       strInsert = "INSERT INTO  CHARGES (COMMENTS) VALUES ('" & CC & "')"
     
    Debug.Print strInsert
    CurrentDb.Execute strInsert, dbFailOnError
      End If
      End If
      End Sub



    • Edited by ellis Wa Friday, November 4, 2016 5:48 PM
    Friday, November 4, 2016 5:43 PM

All replies

  • Hi,

    Looks like you're insert statement only executes when Transaction = "Inquiry" and Status = "Before." Are you saying the record is not inserted when both conditions are True?

    Friday, November 4, 2016 5:53 PM
  • Yes. It is not inserting when both conditions are true

    Status is a combo box with these two values:

    BEFORE

    AFTER

    Thanks!!

    • Edited by ellis Wa Friday, November 4, 2016 6:39 PM
    Friday, November 4, 2016 6:37 PM
  • I see. Have you tried stepping through your code to make sure the insert line is executing?
    Friday, November 4, 2016 6:50 PM
  • Yes, I did. the insert statement execute but it does not insert.

    I tried moving same code to the form beforeupdate event but I got runtime error 3314.

    it is telling me to enter a value in my date field. I do have a value in this field.

    the error occurs here:

    CurrentDb.Execute strInsert, dbFailOnError


    fyi. This is on a subform
    • Edited by ellis Wa Friday, November 4, 2016 7:04 PM
    Friday, November 4, 2016 7:03 PM
  • Your code has this:

    Debug.Print strInsert

    What does it say in the Immediate Window?

    Friday, November 4, 2016 7:16 PM
  • When you run the insert statement it is inserting a new record. That means the only field being populated is Comments. Nothing is going into your date field unless you have a default value set up for it.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Friday, November 4, 2016 7:22 PM
  • It show this

    INSERT INTO  CHARGES (COMMENTS) VALUES ('DENY')

    Friday, November 4, 2016 7:28 PM
  • So this this mean that I will need to use a where statement ...

    Also, do I really need an insert statement? Can I just assign the value deny to the field comment since I will also disable the comment field. When the user select transaction type inquiry and status before.

    Friday, November 4, 2016 7:32 PM
  • it looks like this worked. is this a safe way or the lazy way of doing it?

    What is a better approach ?

    Private Sub STATUS_BeforeUpdate(Cancel As Integer)
      If Me.TRANSACTION = "Inquiry" Then
        If Me.STATUS = "BEFORE" Then
        Me.DECISION = "FORM SENT"
        ElseIf Me.STATUS = "AFTER DUE DATE" Then
      Me.DECISION = "DENY"
      End If
      End If
    End Sub

    Friday, November 4, 2016 7:38 PM
  • it looks like this worked. is this a safe way or the lazy way of doing it?

    What is a better approach ?

    Private Sub STATUS_BeforeUpdate(Cancel As Integer)
      If Me.TRANSACTION = "Inquiry" Then
        If Me.STATUS = "BEFORE" Then
        Me.DECISION = "FORM SENT"
        ElseIf Me.STATUS = "AFTER DUE DATE" Then
      Me.DECISION = "DENY"
      End If
      End If
    End Sub

    Hi,

    This way is "editing" the current record. The insert statement you were doing earlier was "adding" new records to the table "Charges."

    Which one do you really need: update existing or add new record?

    Friday, November 4, 2016 8:12 PM