none
Incomplete Updating of Records RRS feed

  • Question

  • Hi al,

    I am using a continuous form to display records in a table. See inserted picture below. The form is bound to a table where the SCheckField is always False. Once I check the items I want to issue, the SCheckField must be reset back to False. For this I use an Update Query.

    I then run the below code to update the table:

              Sql = "UPDATE Tbl_StockSerialNumbers " _
                  & "SET SActive = 'I' " _
                  & "WHERE SCheckField = True;"
              DoCmd.RunSQL Sql
    

    Only the first record gets updated with "I". The other 3 records does not get updated and remains with "Y".

    I then run the Update Query to reset all the SCheckFields back to "False" in the table, but only the 3 first records gets updated. The last record does not update and the SCheckField stays as True.

    Your assistance is highly appreciated.

    Thanks

    Deon

    Wednesday, October 16, 2019 2:29 PM

Answers

  • Is it possible that the error is that I am opening the form bound to the table, and then try to update the table by using SQL statements while the form is still open?

    Hi Deon,

    Perhaps the current record is still Dirty.

    You could try:    Me.Dirty = False    before executing the query.

    Imb.

    • Marked as answer by Deon SA Thursday, October 17, 2019 8:39 AM
    Thursday, October 17, 2019 7:52 AM

All replies

  • Perhaps you should requery the form after running the update query?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, October 16, 2019 2:34 PM
  • Only the first record gets updated with "I". The other 3 records does not get updated and remains with "Y".

    I then run the Update Query to reset all the SCheckFields back to "False" in the table, but only the 3 first records gets updated. The last record does not update and the SCheckField stays as True.

    Hi Deon,

    Perhaps your form needs a Refresh.

    You can add an myform.Refresh after the Update query.

    Imb.

    Wednesday, October 16, 2019 2:38 PM
  • hmmm if it is not a refresh issue per Hans & Imb and the records are really not changed in the table..... perhaps the SQL statement should be fired with:

    CurrentDb.Execute SQL

    ...that is what I use to fire Action queries with VBA rather than a docmd.   

    give it a try....

    also I don't use the '&' symbol in the syntax that way that you show,  it's a very brief sql so remove the line breaks altogether at least for testing.....

    Wednesday, October 16, 2019 10:51 PM
  • Hi all,

    I have tried the suggestions, but get the same results.

    I changed the SQL statement to

              Sql = "UPDATE [Tbl_StockSerialNumbers] SET [Tbl_StockSerialNumbers].[SActive] = 'I' WHERE [Tbl_StockSerialNumbers].[SCheckField] = True;"
              CurrentDb.Execute Sql

    I also added the requery after the update query ( I don't think the requery has any effect as I am closing the form as soon as the user clicks the "SAVE" button).

    If I choose only 1 record to update, 0 records are updated. If I choose 2 records, only the first record is updated.

    If I choose all 10 records to be updated, only the first 9 are updated and not the 10th record.

    Is it possible that the error is that I am opening the form bound to the table, and then try to update the table by using SQL statements while the form is still open?

    Thursday, October 17, 2019 5:55 AM
  • You could see what happens if you close the form before executing the query...

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, October 17, 2019 7:42 AM
  • Is it possible that the error is that I am opening the form bound to the table, and then try to update the table by using SQL statements while the form is still open?

    Hi Deon,

    Perhaps the current record is still Dirty.

    You could try:    Me.Dirty = False    before executing the query.

    Imb.

    • Marked as answer by Deon SA Thursday, October 17, 2019 8:39 AM
    Thursday, October 17, 2019 7:52 AM
  • Hi Imb,

    This resolved the issue.

    Much appreciated!

    Deon

    Thursday, October 17, 2019 8:39 AM