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

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

Answer 1

Hi Dave,

Have you tried stepping through the code?

Answer 2

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

Answer 3

Yes but it steps right over the INSERT execution and continues.

---

Dave1289

Answer 4

I left out the  declarations for brevity.  But thank you for the excellent suggestion on the  trapping option!

---

Dave1289

Answer 5

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