How to properly swap values of a field between two recordsets that were also queried by those values in VBA Access










0














I am encountering a strange bug when I try to swap values between two recordsets. The source of the problem appears to come from the fact that the field I am swapping is also the field that I query from initially.
Here are the logical steps to understand it more clearly:



  1. Store all the records where PhaseID is equal to X into Recordset A

  2. Store all the records where PhaseID is equal to Y into Recordset B

  3. Set all records in Recordset A to have PhaseID equal to Y

  4. Set all records in Recordset B to have PhaseID equal to X

Here is my actual code:



Private Sub swapReferences(firstRec() As Variant, secondRec() As Variant)
...
Set rstFirst = CurrentDb.OpenRecordset("Select * From Scenarios Where PhaseID = " & firstRec(0))
Set rstSecond = CurrentDb.OpenRecordset("Select * From Scenarios Where PhaseID = " & secondRec(0))

updateRecords rstFirst, "PhaseID", secondRec(0)
updateRecords rstSecond, "PhaseID", firstRec(0)
End Sub

Private Sub updateRecords(rst As Recordset, fieldName As String, newVal As Variant)
While Not rst.EOF
rst.Edit
rst(fieldName) = newVal
rst.update
rst.MoveNext
Wend
End Sub


The strange thing is that this code may work the first time (in that the values are actually swapped). But the second time, it is almost guaranteed to fail. What happens is that instead of the rows being swapped, one of the recordsets gets all of the records and the other gets none. It is as if rstSecond gets dynamically larger when rstFirst updates its records to have the values that rstSecond had queried with.



What is even stranger is that I seemed to have found a fix that does not make any sense. I modified the first function as shown:



Private Sub swapReferences(firstRec() As Variant, secondRec() As Variant)
Set rstFirst = CurrentDb.OpenRecordset("Select * From Scenarios Where PhaseID = " & firstRec(0))
Set rstSecond = CurrentDb.OpenRecordset("Select * From Scenarios Where PhaseID = " & secondRec(0))

'Debug
If Not rstSecond.EOF Then
'For whatever reason .RecordCount isn't accurate unless the last record has been accessed
rstSecond.MoveLast
Debug.Print rstSecond.RecordCount
rstSecond.MoveFirst
End If

updateRecords rstFirst, "PhaseID", secondRec(0)

'Debug
If Not rstSecond.EOF Then
rstSecond.MoveLast
Debug.Print rstSecond.RecordCount
rstSecond.MoveFirst
End If

updateRecords rstSecond, "PhaseID", firstRec(0)
End Sub


Those if blocks were used to see if rstSecond was magically changing its size after rstFirst updated its records. When I ran the code with these debug blocks, I discovered that not only was rstSecond remaining constant but my code worked as intended.



So my question is two-fold: why did adding those debug blocks fix my code? And is there a more appropriate way to do what I want to do so my code does not appear to be so nonsensical?










share|improve this question























  • It's possible the second recordset's records aren't actually retrieved until after the first recordset's updates are sent to the database. The MoveLast forces the retreival to happen before the updates to the other recordset are sent. Another approach would be to wait until all updates are done and then call UpdateBatch on both recordsets.
    – Tim Williams
    Nov 11 at 22:02











  • Do you mean to wait until both recordset's records are retrieved? How does one force it to wait?
    – Luca Guarro
    Nov 12 at 12:44










  • Instead of calling update within each loop, wait until both recordset have been updated and then call updatebatch on each one.
    – Tim Williams
    Nov 12 at 15:37










  • Okay I understand now. However, I am using DAO recordsets. Still, I tried running rstFirst.update dbUpdateBatch but it throws me an invalid argument exception. Would this be a good reason to switch to an ADO recordset?
    – Luca Guarro
    Nov 12 at 16:36










  • I never use DAO, but maybe try checking here first: docs.microsoft.com/en-us/office/client-developer/access/…
    – Tim Williams
    Nov 12 at 16:44
















0














I am encountering a strange bug when I try to swap values between two recordsets. The source of the problem appears to come from the fact that the field I am swapping is also the field that I query from initially.
Here are the logical steps to understand it more clearly:



  1. Store all the records where PhaseID is equal to X into Recordset A

  2. Store all the records where PhaseID is equal to Y into Recordset B

  3. Set all records in Recordset A to have PhaseID equal to Y

  4. Set all records in Recordset B to have PhaseID equal to X

Here is my actual code:



Private Sub swapReferences(firstRec() As Variant, secondRec() As Variant)
...
Set rstFirst = CurrentDb.OpenRecordset("Select * From Scenarios Where PhaseID = " & firstRec(0))
Set rstSecond = CurrentDb.OpenRecordset("Select * From Scenarios Where PhaseID = " & secondRec(0))

updateRecords rstFirst, "PhaseID", secondRec(0)
updateRecords rstSecond, "PhaseID", firstRec(0)
End Sub

Private Sub updateRecords(rst As Recordset, fieldName As String, newVal As Variant)
While Not rst.EOF
rst.Edit
rst(fieldName) = newVal
rst.update
rst.MoveNext
Wend
End Sub


The strange thing is that this code may work the first time (in that the values are actually swapped). But the second time, it is almost guaranteed to fail. What happens is that instead of the rows being swapped, one of the recordsets gets all of the records and the other gets none. It is as if rstSecond gets dynamically larger when rstFirst updates its records to have the values that rstSecond had queried with.



What is even stranger is that I seemed to have found a fix that does not make any sense. I modified the first function as shown:



Private Sub swapReferences(firstRec() As Variant, secondRec() As Variant)
Set rstFirst = CurrentDb.OpenRecordset("Select * From Scenarios Where PhaseID = " & firstRec(0))
Set rstSecond = CurrentDb.OpenRecordset("Select * From Scenarios Where PhaseID = " & secondRec(0))

'Debug
If Not rstSecond.EOF Then
'For whatever reason .RecordCount isn't accurate unless the last record has been accessed
rstSecond.MoveLast
Debug.Print rstSecond.RecordCount
rstSecond.MoveFirst
End If

updateRecords rstFirst, "PhaseID", secondRec(0)

'Debug
If Not rstSecond.EOF Then
rstSecond.MoveLast
Debug.Print rstSecond.RecordCount
rstSecond.MoveFirst
End If

updateRecords rstSecond, "PhaseID", firstRec(0)
End Sub


Those if blocks were used to see if rstSecond was magically changing its size after rstFirst updated its records. When I ran the code with these debug blocks, I discovered that not only was rstSecond remaining constant but my code worked as intended.



So my question is two-fold: why did adding those debug blocks fix my code? And is there a more appropriate way to do what I want to do so my code does not appear to be so nonsensical?










share|improve this question























  • It's possible the second recordset's records aren't actually retrieved until after the first recordset's updates are sent to the database. The MoveLast forces the retreival to happen before the updates to the other recordset are sent. Another approach would be to wait until all updates are done and then call UpdateBatch on both recordsets.
    – Tim Williams
    Nov 11 at 22:02











  • Do you mean to wait until both recordset's records are retrieved? How does one force it to wait?
    – Luca Guarro
    Nov 12 at 12:44










  • Instead of calling update within each loop, wait until both recordset have been updated and then call updatebatch on each one.
    – Tim Williams
    Nov 12 at 15:37










  • Okay I understand now. However, I am using DAO recordsets. Still, I tried running rstFirst.update dbUpdateBatch but it throws me an invalid argument exception. Would this be a good reason to switch to an ADO recordset?
    – Luca Guarro
    Nov 12 at 16:36










  • I never use DAO, but maybe try checking here first: docs.microsoft.com/en-us/office/client-developer/access/…
    – Tim Williams
    Nov 12 at 16:44














0












0








0


1





I am encountering a strange bug when I try to swap values between two recordsets. The source of the problem appears to come from the fact that the field I am swapping is also the field that I query from initially.
Here are the logical steps to understand it more clearly:



  1. Store all the records where PhaseID is equal to X into Recordset A

  2. Store all the records where PhaseID is equal to Y into Recordset B

  3. Set all records in Recordset A to have PhaseID equal to Y

  4. Set all records in Recordset B to have PhaseID equal to X

Here is my actual code:



Private Sub swapReferences(firstRec() As Variant, secondRec() As Variant)
...
Set rstFirst = CurrentDb.OpenRecordset("Select * From Scenarios Where PhaseID = " & firstRec(0))
Set rstSecond = CurrentDb.OpenRecordset("Select * From Scenarios Where PhaseID = " & secondRec(0))

updateRecords rstFirst, "PhaseID", secondRec(0)
updateRecords rstSecond, "PhaseID", firstRec(0)
End Sub

Private Sub updateRecords(rst As Recordset, fieldName As String, newVal As Variant)
While Not rst.EOF
rst.Edit
rst(fieldName) = newVal
rst.update
rst.MoveNext
Wend
End Sub


The strange thing is that this code may work the first time (in that the values are actually swapped). But the second time, it is almost guaranteed to fail. What happens is that instead of the rows being swapped, one of the recordsets gets all of the records and the other gets none. It is as if rstSecond gets dynamically larger when rstFirst updates its records to have the values that rstSecond had queried with.



What is even stranger is that I seemed to have found a fix that does not make any sense. I modified the first function as shown:



Private Sub swapReferences(firstRec() As Variant, secondRec() As Variant)
Set rstFirst = CurrentDb.OpenRecordset("Select * From Scenarios Where PhaseID = " & firstRec(0))
Set rstSecond = CurrentDb.OpenRecordset("Select * From Scenarios Where PhaseID = " & secondRec(0))

'Debug
If Not rstSecond.EOF Then
'For whatever reason .RecordCount isn't accurate unless the last record has been accessed
rstSecond.MoveLast
Debug.Print rstSecond.RecordCount
rstSecond.MoveFirst
End If

updateRecords rstFirst, "PhaseID", secondRec(0)

'Debug
If Not rstSecond.EOF Then
rstSecond.MoveLast
Debug.Print rstSecond.RecordCount
rstSecond.MoveFirst
End If

updateRecords rstSecond, "PhaseID", firstRec(0)
End Sub


Those if blocks were used to see if rstSecond was magically changing its size after rstFirst updated its records. When I ran the code with these debug blocks, I discovered that not only was rstSecond remaining constant but my code worked as intended.



So my question is two-fold: why did adding those debug blocks fix my code? And is there a more appropriate way to do what I want to do so my code does not appear to be so nonsensical?










share|improve this question















I am encountering a strange bug when I try to swap values between two recordsets. The source of the problem appears to come from the fact that the field I am swapping is also the field that I query from initially.
Here are the logical steps to understand it more clearly:



  1. Store all the records where PhaseID is equal to X into Recordset A

  2. Store all the records where PhaseID is equal to Y into Recordset B

  3. Set all records in Recordset A to have PhaseID equal to Y

  4. Set all records in Recordset B to have PhaseID equal to X

Here is my actual code:



Private Sub swapReferences(firstRec() As Variant, secondRec() As Variant)
...
Set rstFirst = CurrentDb.OpenRecordset("Select * From Scenarios Where PhaseID = " & firstRec(0))
Set rstSecond = CurrentDb.OpenRecordset("Select * From Scenarios Where PhaseID = " & secondRec(0))

updateRecords rstFirst, "PhaseID", secondRec(0)
updateRecords rstSecond, "PhaseID", firstRec(0)
End Sub

Private Sub updateRecords(rst As Recordset, fieldName As String, newVal As Variant)
While Not rst.EOF
rst.Edit
rst(fieldName) = newVal
rst.update
rst.MoveNext
Wend
End Sub


The strange thing is that this code may work the first time (in that the values are actually swapped). But the second time, it is almost guaranteed to fail. What happens is that instead of the rows being swapped, one of the recordsets gets all of the records and the other gets none. It is as if rstSecond gets dynamically larger when rstFirst updates its records to have the values that rstSecond had queried with.



What is even stranger is that I seemed to have found a fix that does not make any sense. I modified the first function as shown:



Private Sub swapReferences(firstRec() As Variant, secondRec() As Variant)
Set rstFirst = CurrentDb.OpenRecordset("Select * From Scenarios Where PhaseID = " & firstRec(0))
Set rstSecond = CurrentDb.OpenRecordset("Select * From Scenarios Where PhaseID = " & secondRec(0))

'Debug
If Not rstSecond.EOF Then
'For whatever reason .RecordCount isn't accurate unless the last record has been accessed
rstSecond.MoveLast
Debug.Print rstSecond.RecordCount
rstSecond.MoveFirst
End If

updateRecords rstFirst, "PhaseID", secondRec(0)

'Debug
If Not rstSecond.EOF Then
rstSecond.MoveLast
Debug.Print rstSecond.RecordCount
rstSecond.MoveFirst
End If

updateRecords rstSecond, "PhaseID", firstRec(0)
End Sub


Those if blocks were used to see if rstSecond was magically changing its size after rstFirst updated its records. When I ran the code with these debug blocks, I discovered that not only was rstSecond remaining constant but my code worked as intended.



So my question is two-fold: why did adding those debug blocks fix my code? And is there a more appropriate way to do what I want to do so my code does not appear to be so nonsensical?







vba ms-access access-vba






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 11 at 17:37

























asked Nov 11 at 14:23









Luca Guarro

3461213




3461213











  • It's possible the second recordset's records aren't actually retrieved until after the first recordset's updates are sent to the database. The MoveLast forces the retreival to happen before the updates to the other recordset are sent. Another approach would be to wait until all updates are done and then call UpdateBatch on both recordsets.
    – Tim Williams
    Nov 11 at 22:02











  • Do you mean to wait until both recordset's records are retrieved? How does one force it to wait?
    – Luca Guarro
    Nov 12 at 12:44










  • Instead of calling update within each loop, wait until both recordset have been updated and then call updatebatch on each one.
    – Tim Williams
    Nov 12 at 15:37










  • Okay I understand now. However, I am using DAO recordsets. Still, I tried running rstFirst.update dbUpdateBatch but it throws me an invalid argument exception. Would this be a good reason to switch to an ADO recordset?
    – Luca Guarro
    Nov 12 at 16:36










  • I never use DAO, but maybe try checking here first: docs.microsoft.com/en-us/office/client-developer/access/…
    – Tim Williams
    Nov 12 at 16:44

















  • It's possible the second recordset's records aren't actually retrieved until after the first recordset's updates are sent to the database. The MoveLast forces the retreival to happen before the updates to the other recordset are sent. Another approach would be to wait until all updates are done and then call UpdateBatch on both recordsets.
    – Tim Williams
    Nov 11 at 22:02











  • Do you mean to wait until both recordset's records are retrieved? How does one force it to wait?
    – Luca Guarro
    Nov 12 at 12:44










  • Instead of calling update within each loop, wait until both recordset have been updated and then call updatebatch on each one.
    – Tim Williams
    Nov 12 at 15:37










  • Okay I understand now. However, I am using DAO recordsets. Still, I tried running rstFirst.update dbUpdateBatch but it throws me an invalid argument exception. Would this be a good reason to switch to an ADO recordset?
    – Luca Guarro
    Nov 12 at 16:36










  • I never use DAO, but maybe try checking here first: docs.microsoft.com/en-us/office/client-developer/access/…
    – Tim Williams
    Nov 12 at 16:44
















It's possible the second recordset's records aren't actually retrieved until after the first recordset's updates are sent to the database. The MoveLast forces the retreival to happen before the updates to the other recordset are sent. Another approach would be to wait until all updates are done and then call UpdateBatch on both recordsets.
– Tim Williams
Nov 11 at 22:02





It's possible the second recordset's records aren't actually retrieved until after the first recordset's updates are sent to the database. The MoveLast forces the retreival to happen before the updates to the other recordset are sent. Another approach would be to wait until all updates are done and then call UpdateBatch on both recordsets.
– Tim Williams
Nov 11 at 22:02













Do you mean to wait until both recordset's records are retrieved? How does one force it to wait?
– Luca Guarro
Nov 12 at 12:44




Do you mean to wait until both recordset's records are retrieved? How does one force it to wait?
– Luca Guarro
Nov 12 at 12:44












Instead of calling update within each loop, wait until both recordset have been updated and then call updatebatch on each one.
– Tim Williams
Nov 12 at 15:37




Instead of calling update within each loop, wait until both recordset have been updated and then call updatebatch on each one.
– Tim Williams
Nov 12 at 15:37












Okay I understand now. However, I am using DAO recordsets. Still, I tried running rstFirst.update dbUpdateBatch but it throws me an invalid argument exception. Would this be a good reason to switch to an ADO recordset?
– Luca Guarro
Nov 12 at 16:36




Okay I understand now. However, I am using DAO recordsets. Still, I tried running rstFirst.update dbUpdateBatch but it throws me an invalid argument exception. Would this be a good reason to switch to an ADO recordset?
– Luca Guarro
Nov 12 at 16:36












I never use DAO, but maybe try checking here first: docs.microsoft.com/en-us/office/client-developer/access/…
– Tim Williams
Nov 12 at 16:44





I never use DAO, but maybe try checking here first: docs.microsoft.com/en-us/office/client-developer/access/…
– Tim Williams
Nov 12 at 16:44


















active

oldest

votes











Your Answer






StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);













draft saved

draft discarded


















StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53249658%2fhow-to-properly-swap-values-of-a-field-between-two-recordsets-that-were-also-que%23new-answer', 'question_page');

);

Post as a guest















Required, but never shown






























active

oldest

votes













active

oldest

votes









active

oldest

votes






active

oldest

votes















draft saved

draft discarded
















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid


  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.





Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


Please pay close attention to the following guidance:


  • Please be sure to answer the question. Provide details and share your research!

But avoid


  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53249658%2fhow-to-properly-swap-values-of-a-field-between-two-recordsets-that-were-also-que%23new-answer', 'question_page');

);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

Darth Vader #20

How to how show current date and time by default on contact form 7 in WordPress without taking input from user in datetimepicker

Ondo