Results 1 to 1 of 1
  1. #1
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201

    Unanswered: Cancellation by Associated Object.

    Following an earlier post on the same project, I now have another small problem. I have a form which shows all of the records in a buffer table and these records are used to load the data into various database tables under a number of rules. Starting with an empty form, I can click on button LoadNewData and read records from an Excel spreadsheet into the buffer table. The relevant code is:

    Private Sub btnLoadNewdata_Click()
    On Error GoTo Err_btnLoadNewdata
    Dim strSQL As String
    DoCmd.SetWarnings False
    strSQL = "INSERT INTO LoadBuffer (Title, FirstName, LastName, OrganisationName, AddressLine_2, Suburb, State, " & _
    "PostCode, Phone, Mobile, Fax, Email, CustomerType, Distributor, DistributorCode, StockingProducts) " & _
    "SELECT Ucase(Title), Ucase(FirstName), Ucase(LastName), IIf(IsNull(OrganisationName), 'NOVALUE', Ucase(OrganisationName)), " & _
    "Ucase(StreetAddress), Ucase(Suburb), Ucase(State), Ucase(Postcode), Phone, Mobile, Fax, Email, Ucase(CustomerType), " & _
    "Ucase(Distributor), Ucase(DistributorCode), StockingProducts FROM DataLoader"
    DoCmd.RunSQL (strSQL)
    Forms!frmDataLoader.Requery
    Exit_btnLoadNewdata:
    DoCmd.SetWarnings True
    Exit Sub
    Err_btnLoadNewdata:
    MsgBox "Error " & Err.Number & " " & Err.Description
    Resume Exit_btnLoadNewdata
    End Sub

    The works and the reason I show this is that my code for the Clear button (which deletes all records) was exactly the same except that the INSERT statement was replaced by a "DELETE * FROM DataLoader statement. This worked up to a point but it would always leave behind some random records claiming that deleting them would produce lock violations. I therefore modified the code for the Clear button as follows:

    Private Sub btnClearBuffer_Click()
    On Error GoTo Err_btnClearBuffer_Click
    Dim rsDelete As DAO.Recordset
    DoCmd.SetWarnings False
    Set rsDelete = Me.RecordsetClone
    If rsDelete.RecordCount > 0 Then
    MsgBox "Please wait until all records are deleted"
    rsDelete.MoveFirst
    Do Until rsDelete.EOF = True
    rsDelete.Delete
    rsDelete.MoveNext
    Loop
    rsDelete.Close
    Me.Recordset.Requery
    End If
    MsgBox "All records deleted"
    Exit_btnClearBuffer_Click:
    DoCmd.SetWarnings True
    Exit Sub
    Err_btnClearBuffer_Click:
    ' If (Err.Number <> 3426) Then
    MsgBox "Error " & Err.Number & " " & Err.Description
    ' End If
    Resume Exit_btnClearBuffer_Click
    End Sub

    This does remove all of the records, but when it gets to the ReQuery bit it produces an error 3426 which has the description "This action was cancelled by an associated object". The ReQuery is not executed. For test purposes, all locking was off and the database was opened in exclusive mode. Removing the rsDelete.Close statement has no effect. Can anybody advise, please ?

    A late addition. Changing Me!Requery to Forms!frmDataLoader.Requery works better, but produces an Error 3167 "Record is deleted". It could be that the deleted records have not been cleared and are just flagged as deleted.
    Last edited by Jim Wright; 04-11-06 at 03:05.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •