Consulting

Results 1 to 6 of 6

Thread: db.Execute Update method fails without errors

  1. #1
    VBAX Regular
    Joined
    May 2019
    Location
    Brighton
    Posts
    9
    Location

    db.Execute Update method fails without errors

    I have a form that is based on a DAO Recordset. The recordset could contain any number of records as it is based on search parameters entered by the user previously in a separate form.

    3 of the fields in the form can be changed, but not directly via the recordset (these 3 fields are not bound). Instead the user makes a change in field(s) then clicks 'save' button and a db.execute method is run to update the relevant record in the table as per below example code which updates the Name of the Analyst (there are three of these methods, one for each field that can be updated):

    Dim db As Database
    If newAnalyst <> Nz(Trim(Me.Recordset!Analyst), "") Then
    	sqlStr = "UPDATE tblRptHdr SET Analyst = '" & newAnalyst & "' WHERE FileTrack = '" & currentFileTrack & "';"
    	db.Execute sqlStr, dbFailOnError
    End If
    FileTrack is a primary key
    FileTrack and Analyst are both text fields
    currentFileTrack & newAnalyst are declared variables and the values are pulled from the current record displayed in the form
    There is an error handler.
    It is a split DB, tblRptHdr is a linked table

    I have tested this repeatedly on a number of records without issue.

    However, if I sort the order of the records (using the Sort options in the MS Access Home Toolbar) and then make changes, the update often fails (the first record displayed after sorting usually works, but once I move to later records, then the issue occurs).

    By fail I mean the record does not get updated, but no error is thrown, the change simply does not happen.

    Checks:
    checked the variable names, field names, table name, UPDATE method to ensure no typos etc.
    Added all to Watch window to ensure the data they hold is correct.
    Outputted the sqlStr to Immediate window and ensured a valid SQL String is being executed.

    I have tried two other methods for performing the update (Docmd.Runsql and Recordset.Edit), and both of these methods do successfully update the data, however the change does not reflect in the Form Recordset (unlike the db.Execute method which is immediate), so these are not suitable.

    I am using Access for Office 365.

    I would be grateful if anyone has any ideas how to correct this as I have no idea what I am doing wrong here.

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    If Recordset.Edit works, doesn't a simple requery update the form records?
    Is there some reason that you do not allow the user to directly update the data in the Form's fields?

  3. #3
    VBAX Regular
    Joined
    May 2019
    Location
    Brighton
    Posts
    9
    Location
    Depending on the search criteria for the recordset and what changes were made, the record might not be included in the search results after the requery.

    Any changes were immediate, it would be preferable to give the user the opportunity to not save the changes if they make a mistake.

    We were also experiencing issues with the backend suddenly in "inconsistent state", this was always occurring immediately after updating a record, sometimes Access was not able to repair it. I wasn't sure if multiple users having same records open/editable in a recordset was part of the issue, so changed it to read-only and added the db.execute method for updating.

  4. #4
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    OK, how about using a VBA Recordset to update the table using
    .Edit
    to do so, you can use a msgbox to ensure that the data is correct and then use the VBA to enter it direcyly in to the table.

  5. #5
    VBAX Regular
    Joined
    May 2019
    Location
    Brighton
    Posts
    9
    Location
    The issue with using the Recordset.edit method is that while the record is correctly updated in the table, the record does not update in the Form recordset (where as the db.Execute method updates in the Form recordset immediately...when it works), its the same for Docmd.runsql

    And I want to avoid requery of the form recordset if possible as it could change the records available to the user.

  6. #6
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Well, you could it in reverse, update the form real fields using VBA which will automatically update the table, won't it?
    It is very simple to update the current record fields, which can be on the form but in invisible mode.

Tags for this Thread

Posting Permissions

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