Incomplete Updating of Records

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

Answer 1

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

---

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

Answer 2

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.

Answer 3

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.....

Answer 4

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?

Answer 5

You could see what happens if you close the form before executing the query...

---

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

Answer 6

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.

Answer 7

Hi Imb,

This resolved the issue.

Much appreciated!

Deon