Loop ends before last record updated

[jwrdanka]

Hi, I am really a novice and am having an issue with a loop.
I have a continuous form that has a calculated field.
I need to have the field update a table.
The loop works but does not update the last record.
Don't laugh when you read this, like I said I am a novice.
Can someone tell me what I am doing wrong?

Dim rs As DAO.Recordset
Dim strBookmark As String
Dim SQL3 As String
SQLIN3 = "UPDATE Tbl_TechReturnsInTransit SET Tbl_TechReturnsInTransit.PutAwayLocation = [Forms]![011 Tech Returns]![TxtPutAway]WHERE (((Tbl_TechReturnsInTransit.[STO#]) = [Forms]![011 Tech Returns]![txtSTO]) And ((Tbl_TechReturnsInTransit.Material) = [Forms]![011 Tech Returns]![Material]))"

Set rs = Me.RecordsetClone

rs.MoveFirst

Do While Not rs.EOF
strBookmark = rs.Bookmark

DoCmd.RunSQL SQLIN3

Me.Bookmark = strBookmark
rs.MoveNext

Loop

rs.Close

Set rs = Nothing

 

[Golom]

You're sure that works?

You are missing a space before the WHERE clause and the names of your form controls are inside the quotes where they won't get resolved to the value of the controls.

Also, a variable to contain a bookmark needs to be a variant. Bookmarks are not strings.

 

[jwrdanka]

The space issue is because of the copy/paste into the message, it is OK in the code.
The loop works unit the last record (without the loop the SQL statement only updates the first record). There are 8 records, it says it's updating 8 records (I have not turned off the warnings yet) but only 7 records get updated........
OK for the novice, can you explain the bookmark? I'm not sure I understand.

 

[jwrdanka]

I changed the bookmark to variant but the loop still stops before updating the last record.
As I said it steps through all 8 records but when you go to the table only 7 are updated.

 

[dhookom]

I think you need to stop using the form controls. When you create a recordset clone of the form, you should have all of the values from the form in your recordset.

I would try something like this in a copy of your database. You will need to update the field names since you are not referencing controls in this code.

I would actually comment out the Currentdb.Execute and check the debug window to see if the generated SQL is good. This might also depend on the data types of the fields.

Dim rs As DAO.Recordset
Dim SQL3 As String

Set rs = Me.RecordsetClone

rs.MoveFirst

Do While Not rs.EOF
    'don't know what the actual field names are in your recordset
    SQLIN3 = "UPDATE Tbl_TechReturnsInTransit " & _
        "SET PutAwayLocation = """ & rs![TxtPutAway] & _
        """ WHERE [STO#] = """ & rs![txtSTO] & """ And Material = """ & _
        rs![Material & """ "

    Debug.Print SQLIN3
    Currentdb.Execute SQLIN3, dbFailOnError

    rs.MoveNext

Loop

rs.Close

Set rs = Nothing

Duane
Hook'D on Access
MS Access MVP

 

[jwrdanka]

Thanks