Looping Recordset - won't process last record (1 Viewer)

marvo · Oct 5, 2018

Hi all
Having trouble with processing the last record in a recordset.
I have a nested loop. The code is working great - until the last row in the recordset. As far I can tell, what is happening, is that I have 'MoveNext', which eventually gets to the last row in the recordset, and then loops, evaluates the DoUntil EOF - say's "yep, I'm at the recordset end of file", so quits out of the loop, before processing the final row. Any ideas on fixing this? Thanks

=========================
Dim db As Database
Dim rs As DAO.Recordset
Dim tempFixtureID As Integer
Dim AllUmpires As String

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM qryContactUmpires Where FixtureDate = #10/06/2018#;", dbOpenDynaset)

AllUmpires = ""

Do Until rs.EOF

tempFixtureID = rs.Fields("FixtureID")

Do While tempFixtureID = rs.Fields("FixtureID")

AllUmpires = AllUmpires & rs.Fields("UmpireName") & "; "

rs.MoveNext

Loop

AllUmpires = ""

Loop

Minty · Oct 5, 2018

Have you tried setting the first loop to

Do While Not Rs.EOF ?

And doesn't the AllUmpires = "" at the end mean you never see the result ?

MajP · Oct 5, 2018

I do not think you need a double loop.

Code:

Do while not RS.EOF
   'no change in fixture
   if tempFixtureID = rs.Fields("FixtureID")
    AllUmpires = AllUmpires & ";" &  rs.Fields("UmpireName")
  else
    'Fixture is changed
    debug.print AllUmpires
    tempFixtureID = rs.Fields("FixtureID")
    AllUmpires = rs.Fields("UmpireName")
  end if
  rs.MoveNext
Loop

marvo · Oct 5, 2018

i'll try those thanks
The umpire="" - yes it does look odd, but its just resetting the string between groups of records - I intend to put some action before that - I had a debug.print just before it, and all working fine. Just trying to get the looping right, before moving on to building the action!

marvo · Oct 5, 2018

with some minor tweaks, this code runs nicely - but has same probelm as my original - the last record in recordset never get output before End

MajP · Oct 5, 2018

the last record in recordset never get output before End

That makes sense. I was only outputting when there was a change so on the last record there is no change. What if you add at the end of the loop.

Code:

Do while not RS.EOF
   'no change in fixture
   if tempFixtureID = rs.Fields("FixtureID")
    AllUmpires = AllUmpires & ";" &  rs.Fields("UmpireName")
  else
    'Fixture is changed
    debug.print AllUmpires
    tempFixtureID = rs.Fields("FixtureID")
    AllUmpires = rs.Fields("UmpireName")
  end if
  rs.MoveNext
Loop
'last record
debug.print AllUmpires

isladogs · Oct 5, 2018

Assuming you are using the improved code by MajP, try changing the first line back to

Code:

Do Until Rs.EOF

gemma-the-husky · Oct 5, 2018

How is your data structured?

is there more than one FixtureID for each game?
How do you store the multiple umpires in your table?

June7 · Oct 5, 2018

Except for the missing 'Then' for the 'If`, code works for me. I added a conditional to prevent orphan semi-colon.

AllUmpires = AllUmpires & IIf(AllUmpires="", "", ";") & rs.Fields("UmpireName")

marvo · Oct 5, 2018

gemma-the-husky said:
How is your data structured?

is there more than one FixtureID for each game?
How do you store the multiple umpires in your table?

there is only one fixture ID per game, but multiple umpires from a subform

I want to concatenate all umpires related to a fixture and show in a single row, and update the fixture record in a 'all umpires field,'

marvo · Oct 5, 2018

Ok, I got the looping working, thanks MajP, but now, instead of debug print I want to edit another table with the final out put ( All umpires) for each fixture.
Can you do an 'edit record set WHERE fixture ID = n'

June7 · Oct 5, 2018

Why would you save this data to a table? Calculate when needed. What you are doing is concatenating values from related records. A very common topic and an often-referenced tutorial is http://allenbrowne.com/func-concat.html

marvo · Oct 5, 2018

Yep - concatrelated..that's the routine I was trying to write! All Working !! xxx

MajP · Oct 6, 2018

Why would you save this data to a table? Calculate when needed.

It really depends on the size of the data returned. These concatenate functions in Access are very resource intensive. For every record in the query you are looping another recordset. You do this with 10,000 records and then scroll through the results it will recalculate again. Basically it will be unusable solution. My guess for this problem using it in a function will be just fine, for any real size problems you may have to persist the results somewhere.

Looping Recordset - won't process last record (1 Viewer)

marvo

Registered User.

Minty

AWF VIP

MajP

You've got your good things, and you've got mine.

marvo

Registered User.

marvo

Registered User.

MajP

You've got your good things, and you've got mine.

isladogs

MVP / VIP

gemma-the-husky

Super Moderator

June7

AWF VIP

marvo

Registered User.

marvo

Registered User.

June7

AWF VIP

marvo

Registered User.

MajP

You've got your good things, and you've got mine.

Similar threads

Users who are viewing this thread