Update recordset skipping the last record (2 Viewers)

[Geirr]

Hi all.

I could really need some help here...
I just cannot figure out why the last record in a do while loop does not get updated.

Background:
Sql Server backend, connection string - not linked tables.

The records I want to be updated are selected in a form with one check box on each row (CableMarked - part of criteria below)
Then, a button on the form runs some queries to get the wanted values to some variables, (tmpFromTag and tmpFromText) and finally run this code:

rsUpd.Open "SELECT * FROM " & tmpCableTable & " WHERE <long series of criteria I know works 100%> ORDER BY CableID", gConn, adOpenStatic, adLockOptimistic
If Not (rsUpd.BOF And rsUpd.EOF) Then
rsUpd.MoveFirst
Do While Not rsUpd.EOF
tmpCableID = rsUpd!CableID 'thesse two line just to see if records are found
MsgBox "Cable ID: " & tmpCableID

cmd.CommandText = "UPDATE " & tmpCableTable & " SET FromTag = '" & [tmpFromTag] & "', " _
& "FromText = '" & [tmpFromText] & "', " _
& "CableMarked = ? " _
& "CableLocalID = '" & "" & "', " _
& "CableMarked = ? " _
& "WHERE SystemID = '" & [tmpSystemID] & "' And CableMarked <> 0 And CableLocalID = '" & [tmpUserID] & "' AND ProjectNo = '" & [tmpProjectNo] & "'"
cmd.CommandType = adCmdText 'The criteria abowe is the same as used u\in rsUpd.Open
cmd.Parameters.Append cmd.CreateParameter("CableMarked", adBoolean, adParamInput, , tmpMarked)
cmd.Execute
cmd.Parameters.Delete 0

rsUpd.MoveNext
Loop
End If
rsUpd.Close

When this code run, it will skip the last record. I've tried with 2, 3, 5 or 10 record, the last one is skipped. Alle data is saved correctly, but not on the last one
I've also tried this code with 'remmed out' everything related to cmd. (all updateing exluded, just running through all record in criteria) so the criteria CableLocalID and CableMarked is not changed - to avoid to do anything with the criteria in rsUpd.open or in cmd.Commandtext. - no difference.

Also need to mention that I've also have tried with update the recordset with rsUpd!fields = values and rsUpd,Update - version with no better resutl I believe it's nothing about the saving of data because it skips the last record without any type of updating of data involved.

I've also tried with Do Until loop, with the rsUpd.MoveFirst removed, and all other trix I've found so far, but with no luck.
I've got 'totally blind' on this case, so I hope someone can help me pinning out where I'm doing something wrong.

Btw; the original code worked perfectly with linked tables, but my organization 'force' med to use connection to server without linking due to security reason,,,

Thank you!
Best regards, Geirr.

 

[theDBguy]

Not sure why you need a recordset for this, couldn't you just use a single action query? Or, maybe you could move the logic to a stored procedure instead?

 

[Geirr]

Not sure why you need a recordset for this, couldn't you just use a single action query? Or, maybe you could move the logic to a stored procedure instead?

Thank you for input.
I'm not using separate/stored queries for the same reason I cannot use linked tables.
I've 'transferred' several queries to vba code in form/report code, and maybe I should look into that approach - Nevertheless - I would really like to know where I went wrong because I'm little fresh on dealing with connections to server and not linked tables...

Geirr.

 

[LarryE]

Hi all.

I could really need some help here...
I just cannot figure out why the last record in a do while loop does not get updated.

Background:
Sql Server backend, connection string - not linked tables.

The records I want to be updated are selected in a form with one check box on each row (CableMarked - part of criteria below)
Then, a button on the form runs some queries to get the wanted values to some variables, (tmpFromTag and tmpFromText) and finally run this code:

rsUpd.Open "SELECT * FROM " & tmpCableTable & " WHERE <long series of criteria I know works 100%> ORDER BY CableID", gConn, adOpenStatic, adLockOptimistic
If Not (rsUpd.BOF And rsUpd.EOF) Then
rsUpd.MoveFirst
Do While Not rsUpd.EOF
tmpCableID = rsUpd!CableID 'thesse two line just to see if records are found
MsgBox "Cable ID: " & tmpCableID

cmd.CommandText = "UPDATE " & tmpCableTable & " SET FromTag = '" & [tmpFromTag] & "', " _
& "FromText = '" & [tmpFromText] & "', " _
& "CableMarked = ? " _
& "CableLocalID = '" & "" & "', " _
& "CableMarked = ? " _
& "WHERE SystemID = '" & [tmpSystemID] & "' And CableMarked <> 0 And CableLocalID = '" & [tmpUserID] & "' AND ProjectNo = '" & [tmpProjectNo] & "'"
cmd.CommandType = adCmdText 'The criteria abowe is the same as used u\in rsUpd.Open
cmd.Parameters.Append cmd.CreateParameter("CableMarked", adBoolean, adParamInput, , tmpMarked)
cmd.Execute
cmd.Parameters.Delete 0

rsUpd.MoveNext
Loop
End If
rsUpd.Close

When this code run, it will skip the last record. I've tried with 2, 3, 5 or 10 record, the last one is skipped. Alle data is saved correctly, but not on the last one
I've also tried this code with 'remmed out' everything related to cmd. (all updateing exluded, just running through all record in criteria) so the criteria CableLocalID and CableMarked is not changed - to avoid to do anything with the criteria in rsUpd.open or in cmd.Commandtext. - no difference.

Also need to mention that I've also have tried with update the recordset with rsUpd!fields = values and rsUpd,Update - version with no better resutl I believe it's nothing about the saving of data because it skips the last record without any type of updating of data involved.

I've also tried with Do Until loop, with the rsUpd.MoveFirst removed, and all other trix I've found so far, but with no luck.
I've got 'totally blind' on this case, so I hope someone can help me pinning out where I'm doing something wrong.

Btw; the original code worked perfectly with linked tables, but my organization 'force' med to use connection to server without linking due to security reason,,,

Thank you!
Best regards, Geirr.

I have always found that moving to the last record and then moving to the first record sets the recordset counter properly. So, in your case try:

If Not (rsUpd.BOF And rsUpd.EOF) Then
rsUpd.MoveLast
rsUpd.MoveFirst

You should also try:
Do Until rsUpd.EOF
instead of:
Do While Not rsUpd.EOF

 

[Geirr]

I have always found that moving to the last record and then moving to the first record sets the recordset counter properly. So, in your case try:

If Not (rsUpd.BOF And rsUpd.EOF) Then
rsUpd.MoveLast
rsUpd.MoveFirst

You should also try:
Do Until rsUpd.EOF
instead of:
Do While Not rsUpd.EOF

Thank you for input, bud sadly it gave the same result here...

Geirr.

 

[RonPaii]

Are you sure the last record is not triggering an error? If you don't have error trapping on this function, add it and try again.

 

[Gasman]

I would debug.print some unique field from the recordset.
Forget the rest of the code until you get that correct.

From the code presented (without code tags ) I would have said that should work.
I use Do While Not EOF all the time, without any issue, because of the logic

 

[DaveMD]

Currently based on the OP's code posted above, I see nothing from the recordset being used in the UPDATE query. For example, currently, you are looping over each record in the recordset, but you have nothing in the WHERE of your UPDATE query or in the SET that is being used from the recordset. As @theDBguy indicates, it seems this might be able to be accomplished with just an UPDATE query without the need for a recordset but more information is needed to help you with an approach. It would be helpful to have more context of the intent with the use of the recordset.

 

[LarryE]

I would debug.print some unique field from the recordset.
Forget the rest of the code until you get that correct.

From the code presented (without code tags ) I would have said that should work.
I use Do While Not EOF all the time, without any issue, because of the logic

I use it too so I am wondering if it's tripping over:
If Not (rsUpd.BOF And rsUpd.EOF) Then
I have never used this logic so I am just guessing.

 

[The_Doc_Man]

The highlighted test for .BOF and .EOF being TRUE together is specifically a test for "empty recordset" and it is a well-known method. I don't see an issue there.

 

[theDBguy]

I'm not using separate/stored queries for the same reason I cannot use linked tables.

If that was in response to my suggestion of using stored procedures, those are two different things. In case you weren't aware, stored queries are created in Access, while stored procedures are created in SQL Server.