none
INSERT statement called from event module does not error but no INSERT performed RRS feed

  • Question

  • There is a form with a button whose onClick event fires an INSERT if two ID values are populated and the record does not already exist. This code is exhibiting inconsistent behavior; sometimes it will perform the INSERT but usually nothing happens, no error but no record created.  The SQL string executed as a stand alone query will create a new record.  What might account for this  behavior? The relevant part of the code for the event handler is shown below:

        'Do we have both a work and an event to assign it to ?
        If txtEventID.Value > 0 And txtWorkID.Value > 0 Then
            'Is the event already assigned?
            sTable = "event_work"
            sWHERE = "workid = " & txtWorkID.Value & " AND eventid = " & txtEventID.Value
            iWorkid = txtWorkID.Value
            iEventid = txtEventID.Value

        
            'If record does not already exist, assign?
            If DCount("*", [sTable], [sWHERE]) = 0 Then
        
                'Prepare warning dialog box
                Msg = "Record does not exist. Assign?"
                style = vbOKCancel
                title = "Assign Work to Event"

                'Prompt before assigning
                Response = MsgBox(Msg, style, title)
                If Response = vbOK Then
              
                    'Build INSERT
                    sSQL_01 = "INSERT INTO event_work (workid, eventid) VALUES  (" & iWorkid & " , " & iEventid & ");"
                    Debug.Print "Assignment INSERT  -- " & sSQL_01
                    'INSERT INTO event_work (workid, eventid) VALUES  (274 , 1929);
                    
                    'Create database object
                    Set db = CurrentDb
                    
                    'Perform INSERT
                    db.Execute (sSQL_01)
                    
                     i = DCount("*", "event_work", "eventid = " & iEventid & " AND workid = " & iWorkid)
                     If i = 0 Then
                        MsgBox ("FAIL to create record")
                    End If
       
                    'cleanup and close database objects
                    db.Close
                    Set db = Nothing
                
                    'Forms("event").SUB_event_work.Form.cboWorkID.Requery
                    
                    Exit Sub
                Else
                    MsgBox ("Exiting.")
                    Exit Sub
                End If
            'Record already exist, unassign?
            Else



    Dave1289

    Wednesday, January 25, 2017 9:13 PM

Answers

  • This code is exhibiting inconsistent behavior; sometimes it will perform the INSERT but usually nothing happens, no error but no record created.

    When you call the Execute method, no data error will be reported unless you include the dbFailOnError option:

        db.Execute sSQL_01, dbFailOnError

    You can then trap the error in an error handler in the event procedure.

    I notice that your code does not appear to be declaring any of the variables, which suggests that you are not requiring explicit declaration of variables. This is not a good idea and can easily lead to undetected mistakes when writing code.


    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Thursday, January 26, 2017 12:00 AM Typo corrected.
    • Marked as answer by Dave1289 Thursday, January 26, 2017 12:20 AM
    Wednesday, January 25, 2017 11:59 PM

All replies

  • Hi Dave,

    Have you tried stepping through the code?

    Wednesday, January 25, 2017 9:34 PM
  • This code is exhibiting inconsistent behavior; sometimes it will perform the INSERT but usually nothing happens, no error but no record created.

    When you call the Execute method, no data error will be reported unless you include the dbFailOnError option:

        db.Execute sSQL_01, dbFailOnError

    You can then trap the error in an error handler in the event procedure.

    I notice that your code does not appear to be declaring any of the variables, which suggests that you are not requiring explicit declaration of variables. This is not a good idea and can easily lead to undetected mistakes when writing code.


    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Thursday, January 26, 2017 12:00 AM Typo corrected.
    • Marked as answer by Dave1289 Thursday, January 26, 2017 12:20 AM
    Wednesday, January 25, 2017 11:59 PM
  • Yes but it steps right over the INSERT execution and continues.

    Dave1289

    Thursday, January 26, 2017 12:02 AM
  • I left out the  declarations for brevity.  But thank you for the excellent suggestion on the  trapping option!

    Dave1289

    Thursday, January 26, 2017 12:04 AM
  • That did the trick Ken.  Error 3201 Cannot add or change because a related record is required.  I think it's a dirty read issue. On the calling page it "looks" like the related record was created (it is assigned an auto ID value) but apparently it has not been committed to the database by the time VBA calls its INSERT and so referential integrity prevents the creation of a new record referencing the uncommitted record.  I think all I need to do is make sure the base table record is committed before I create the record in the junction table.  Thanks so much for your help. 

    Dave1289

    Thursday, January 26, 2017 12:20 AM