Loop ends before updating the last record 1

[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

 

[kjv1611]

Not sure if it's the issue (could be a typo here), or the only issue, but at first glance, I see you Dimm'ed your SQL string one way, and used it another....

You've got:

Dim SQL3 As String
and
SQLIN3 = "UPDATE Tbl_...

I'll continue looking to see what else I see, if anything..

 

[kjv1611]

It looks like you're missing a space before the WHERE function (highlighted):

SQLIN3 = "UPDATE Tbl_TechReturnsInTransit SET Tbl_TechReturnsInTransit.PutAwayLocation = [Forms]![011 Tech Returns]![highlight][TxtPutAway]WHERE[/highlight] (((Tbl_TechReturnsInTransit.[STO#]) = [Forms]![011 Tech Returns]![txtSTO]) And ((Tbl_TechReturnsInTransit.Material) = [Forms]![011 Tech Returns]![Material]))"

 

[kjv1611]

And here's another idea... I'm not terribly certain that you're going about this the best way to begin with, but it could just be something I'm not understanding, or something you've not told us.

Here's my thought: Why not allow the form actions to update the records for the table which is it's control source. Then run a separate query outside of the form that updates the other table you're trying to update.... and that way you do not need to run a SQL statmeent within a recordset loop, which should make it faster anyway. Also, you can put calculated fields within SQL statements if that's what is holding you back on that front.

 

[PHV]

What about this (provided that Me refer to [Forms]![011 Tech Returns]) ?

Dim SQL3 As String
SQL3 = "UPDATE Tbl_TechReturnsInTransit SET PutAwayLocation=[Forms]![011 Tech Returns]![TxtPutAway]" _
 & " WHERE [STO#]=[Forms]![011 Tech Returns]![txtSTO] AND Material=[Forms]![011 Tech Returns]![Material]"
With Me.Recordset
  .MoveFirst
  Do While Not .EOF
    DoCmd.RunSQL SQL3
    .MoveNext
  Loop
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jwrdanka]

Thank you so much, this worked!

 

[kjv1611]

which "this"? I'm assuming you used PHV's full redo.

 

[jwrdanka]

These changes worked like a charm..........thank everyone so much!

Dim SQL3 As String
SQL3 = "UPDATE Tbl_TechReturnsInTransit SET PutAwayLocation=[Forms]![011 Tech Returns]![TxtPutAway]" _
& " WHERE [STO#]=[Forms]![011 Tech Returns]![txtSTO] AND Material=[Forms]![011 Tech Returns]![Material]"
With Me.Recordset
.MoveFirst
Do While Not .EOF
DoCmd.RunSQL SQL3
.MoveNext
Loop
End With

 

[kjv1611]

Glad you got it fixed.

Don't forget to thank PHV for his help (their's a link with a purple star on it at the bottom left of each post - that's how we show "our appreciation" on tek-tips for helpful posts.

Not only will that "show appreciation" to PHV, but it'll help point out to others that something in this thread was helpful.... and therefore may be helpful to them in their situation, if it's anything similar.