HELP! My Loop is not including the last record in the recordset

Hi --

Easy question for some of you... but I'm new to looping and can't figure it out!!

I've got a simple list of people tied to a permit number.  My code loops through the recordset, looking for matching permit number and last name.

If it finds a match on both, then it concatenates the first name so it looks like "John and Mary Smith"

If it finds a permit match but not a last name match, then it concatenates to look like "John Smith and Mary Darby"

If is doesn't find a permit match, then there's only one name and that gets listed alone.

This is working great, EXCEPT it does not include the last record.  The last record is a single permit number (one owner).  

I know there are so many combinations of do until, do while etc... I know that somehow it's jumping out of the loop before getting to the last record, but I don't understand where... I think it's in that first MOVENEXT, but I don't know how to fix it.

HELP!!!   Thanks,  Ronda

The code is below:

Sub Multiple_Prop_Owners()
On Error GoTo Err_Multiple_Prop_Owners

Dim MyDB As Database
Dim MySet2 As Recordset, MySet As Recordset, MySetUn As Recordset
Dim strNewName As String
Dim TempFName As String
Dim TempFName2 As String
Dim TempDisplayFName As String
Dim TempLName As String
Dim TempPhone As String
Dim TempBPNum As Integer
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MySetUn = MyDB.OpenRecordset("qryPermit_PropOwners", DB_OPEN_DYNASET)   ' Create dynaset.
MySetUn.Sort = "[bpnumber],[propownrlname]"
Set MySet = MySetUn.OpenRecordset()
Set MySet2 = MyDB.OpenRecordset("tblTempPropOwner", DB_OPEN_TABLE)

'empty temp table before building
MyDB.Execute "DELETE tblTempPropOwner.* FROM tblTempPropOwner;", dbFailOnError

Do Until MySet.EOF ' Loop until end of records.

    TempDisplayFName = Nz(MySet![PropOwnrFName], "NONE")
    TempFName = Nz(MySet![PropOwnrFName], "NONE")
    TempLName = Nz(MySet![PropOwnrLName], "NONE")
    TempBPNum = MySet![BPNumber]
    TempPhone = Nz(MySet![PropOwnrPhone], " ")
    MySet.MoveNext    <================================ I THINK THIS IS THE PROBLEM
    If TempBPNum = MySet![BPNumber] Then
        If TempLName = Nz(MySet![PropOwnrLName], "NONE") Then
            MySet2.AddNew      ' Create new record.
            MySet2![PropOwnrsFirstName] = TempDisplayFName & " and " & Nz(MySet![PropOwnrFName], "")
            MySet2![PropOwnrsLastName] = TempLName
            MySet2![BPNumber] = TempBPNum
            MySet2![PropOwnrPhone] = TempPhone
           
            MySet2.Update ' Save changes.
            MySet.MoveNext
        Else
            MySet2.AddNew   ' Create new record.
            MySet2![PropOwnrsFirstName] = TempDisplayFName & " " & TempLName & " and " & Nz(MySet![PropOwnrFName], "")
            MySet2![PropOwnrsLastName] = MySet![PropOwnrLName]
            MySet2![BPNumber] = TempBPNum
            MySet2![PropOwnrPhone] = TempPhone
           
            MySet2.Update ' Save changes.
            MySet.MoveNext
        End If
    Else
            MySet2.AddNew      ' Create new record.
            MySet2![PropOwnrsFirstName] = TempDisplayFName
            MySet2![PropOwnrsLastName] = TempLName
            MySet2![BPNumber] = TempBPNum
            MySet2![PropOwnrPhone] = TempPhone
            MySet2.Update        ' Save changes.
    End If

Loop                        ' End of loop.

MySet.Close
MySet2.Close

Exit_Prop_Owners:
   
    Exit Sub

Err_Multiple_Prop_Owners:
If Err.Number = 3021 Then
    Resume Exit_Prop_Owners
Else
    MsgBox Err.Number & ": " & Err.Description
    Resume Exit_Prop_Owners
End If



End Sub


Ronda-SAsked:
Who is Participating?
 
nico5038Commented:
I wouldn't do the "sole BPNumber" as that will give the same "puzzle" afterwards.
First decide or "multiple lastnames" (2, 3 or more) need the "concatenate processing".
When that's the case all needed is to check the count being 1 and produce one label and when more keep processing the rows till EndOfFile. The number (2, 3 or more) won't be a problem.
You could even use this concat function for this to get the names like:

Function fncConcatenate(strKeyField  As String) As String

Dim RS As DAO.Recordset

' init function string
fncConcat = ""
Set RS = CurrentDb.OpenRecordset("select PropOwnrFName from tblTest where BPNumber & PropOwnrLName ='" & strKeyField &"'")
If RS.EOF And RS.BOF Then
   Exit Function
End If
RS.MoveFirst
While Not RS.EOF
   fncConcat = fncConcat & ", " & RS!Field1
   RS.MoveNext
Wend
' remove first ", "
fncConcat = Mid(fncConcat, 3)

End Function

Just pass the BPNumber & PropOwnrLName as the parameter and fncConcat will return the needed string.
When the GroupBy is "extended" to hold the other fields that will also be the same, then the Count value > 1 will be the trigger to call this function and all other fields can be selected from the recordet.

Getting the idea ?

Nic;o)

0
 
nico5038Commented:
Try:

MySet.Movefirst ' set start
Do Until MySet.EOF ' Loop until end of records.

    TempDisplayFName = Nz(MySet![PropOwnrFName], "NONE")
    TempFName = Nz(MySet![PropOwnrFName], "NONE")
    TempLName = Nz(MySet![PropOwnrLName], "NONE")
    TempBPNum = MySet![BPNumber]
    TempPhone = Nz(MySet![PropOwnrPhone], " ")

    If TempBPNum = MySet![BPNumber] Then
        If TempLName = Nz(MySet![PropOwnrLName], "NONE") Then
            MySet2.AddNew      ' Create new record.
            MySet2![PropOwnrsFirstName] = TempDisplayFName & " and " & Nz(MySet![PropOwnrFName], "")
            MySet2![PropOwnrsLastName] = TempLName
            MySet2![BPNumber] = TempBPNum
            MySet2![PropOwnrPhone] = TempPhone
           
            MySet2.Update ' Save changes.
            MySet.MoveNext
        Else
            MySet2.AddNew   ' Create new record.
            MySet2![PropOwnrsFirstName] = TempDisplayFName & " " & TempLName & " and " & Nz(MySet![PropOwnrFName], "")
            MySet2![PropOwnrsLastName] = MySet![PropOwnrLName]
            MySet2![BPNumber] = TempBPNum
            MySet2![PropOwnrPhone] = TempPhone
           
            MySet2.Update ' Save changes.
            MySet.MoveNext
        End If
    Else
            MySet2.AddNew      ' Create new record.
            MySet2![PropOwnrsFirstName] = TempDisplayFName
            MySet2![PropOwnrsLastName] = TempLName
            MySet2![BPNumber] = TempBPNum
            MySet2![PropOwnrPhone] = TempPhone
            MySet2.Update        ' Save changes.
    End If
    MySet.MoveNext    <================================ moved
Loop                        ' End of loop.
0
 
nico5038Commented:
Hmm you're also missing a test for an empty recordset, add:

Set MySet = MySetUn.OpenRecordset()
IF MySet.EOF and MySet.BOF then
   ' action for empty set
endif

MySet.movefirst

etc..

Nic;o)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
clarkscottCommented:
Instead of Do Until MySet.EOF    how about

DO WHILE NOT MySet.EOF

I've never had problems with this???

Scott C
0
 
Ronda-SAuthor Commented:
Hi There...

Follow my thoughts here In this code:
-------------------------------------------------------------------------------------------------------------------------------
Do Until MySet.EOF ' Loop until end of records.

    TempDisplayFName = Nz(MySet![PropOwnrFName], "NONE")
    TempFName = Nz(MySet![PropOwnrFName], "NONE")
    TempLName = Nz(MySet![PropOwnrLName], "NONE")
    TempBPNum = MySet![BPNumber]
    TempPhone = Nz(MySet![PropOwnrPhone], " ")
    MySet.MoveNext    <================================ I THINK THIS IS THE PROBLEM
-------------------------------------------------------------------------------------------------------------------------------

what it does is it gets the first record, dumps the contents into the variables , and then this movenext grabs the second record.  It needs to be here in order to make that initial comparison.  When I moved it as you suggested, Nico, I ended up with Mary and Mary Stewart, John and John Bean, etc.

The If statements that follow then does the comparison between the first and second records.  If there's a match on the BPNumber and Last Name, then it combines the names, updates the new recordset, and grabs the third record.  If there's a Match on the BPNumber and NOT a match on the last name, then it lists both names, updates the new recordset, and grabs the third record.  

If there is no BPNumber Match, then it updates the recordset with record one, loops to the top and puts the SECOND record into the variables, then does the movenext to grab record #3 for the comparison.

This code works beautifully until it comes to the very last record.

Let's say the next to the last record is a single owner (John Smith), so there's no BPNumber Match.  It updates the recordset for the BPNumber before him and loops him up to the top.

It takes John Smith and puts his data in the temp variables.  Then it does its movenext and grabs Jeff Truby, the last record.  It compares John to Jeff, sees there's no BPNumber match, drops down to the end  and updates John's record.  But, then it hits the end of the recordset before Jeff's record gets to be updated.  Or so it seems... like I said, I'm still fairly new to looping.

So... I fooled around with it, trying to add a counter that would write the last record.  When I added this code shown below, it made Jeff appear.  It actually tries to append his record twice, but I have the temp table set with the BPNumber as the PK, so it kicks out the duplicate and sends an error, which I ignore.  The net result is I get the last record.

I know this is a sloppy way to do this, so I'm hoping for some clarity on why this is working, and maybe how it could be written better.

THANKS A BUNCH!!!

Ronda



Sub Multiple_Prop_Owners()
On Error GoTo Err_Multiple_Prop_Owners

Dim MyDB As Database
Dim MySet2 As Recordset, MySet As Recordset, MySetUn As Recordset
Dim strNewName As String
Dim TempFName As String
Dim TempFName2 As String
Dim TempDisplayFName As String
Dim TempLName As String
Dim TempPhone As String
Dim TempBPNum As Integer

Dim intRecCount As Integer    <=============================================THIS NEW

Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MySetUn = MyDB.OpenRecordset("qryPermit_PropOwners", DB_OPEN_DYNASET)   ' Create dynaset.
MySetUn.Sort = "[bpnumber],[propownrlname]"
Set MySet = MySetUn.OpenRecordset()
Set MySet2 = MyDB.OpenRecordset("tblTempPropOwner", DB_OPEN_TABLE)

intRecCount = 1                     <=============================================THIS NEW

'empty temp table before building
MyDB.Execute "DELETE tblTempPropOwner.* FROM tblTempPropOwner;", dbFailOnError

MySet.MoveLast
MySet.MoveFirst ' set start                     <==================================== THIS IS YOURS, NICO

DoCmd.SetWarnings False
Do Until MySet.EOF ' Loop until end of records.
    TempDisplayFName = Nz(MySet![PropOwnrFName], "NONE")
    TempFName = Nz(MySet![PropOwnrFName], "NONE")
    TempLName = Nz(MySet![PropOwnrLName], "NONE")
    TempBPNum = MySet![BPNumber]
    TempPhone = Nz(MySet![PropOwnrPhone], " ")

    If MySet.RecordCount > intRecCount Then                 <=============================================THIS NEW
        MySet.MoveNext                                                  
        intRecCount = intRecCount + 1                             <=============================================THIS NEW
   End If                                                                      <=============================================THIS NEW

    If TempBPNum = MySet![BPNumber] Then
        If TempLName = Nz(MySet![PropOwnrLName], "NONE") Then
            If TempFName = Nz(MySet![PropOwnrFName], "NONE") Then
                GoTo LastRecord:
            Else
                MySet2.AddNew      ' Create new record.
                MySet2![PropOwnrsFirstName] = TempDisplayFName & " and " & Nz(MySet![PropOwnrFName], "")
                MySet2![PropOwnrsLastName] = TempLName
                MySet2![BPNumber] = TempBPNum
                MySet2![PropOwnrPhone] = TempPhone
           
                MySet2.Update ' Save changes.
                MySet.MoveNext
 
               intRecCount = intRecCount + 1            <=============================================THIS NEW

            End If
        Else
            MySet2.AddNew   ' Create new record.
            MySet2![PropOwnrsFirstName] = TempDisplayFName & " " & TempLName & " and " & Nz(MySet![PropOwnrFName], "")
            MySet2![PropOwnrsLastName] = MySet![PropOwnrLName]
            MySet2![BPNumber] = TempBPNum
            MySet2![PropOwnrPhone] = TempPhone
           
            MySet2.Update ' Save changes.
            MySet.MoveNext

            intRecCount = intRecCount + 1             <=============================================THIS NEW


        End If
    Else            
            MySet2.AddNew      ' Create new record.
            MySet2![PropOwnrsFirstName] = TempDisplayFName
            MySet2![PropOwnrsLastName] = TempLName
            MySet2![BPNumber] = TempBPNum
            MySet2![PropOwnrPhone] = TempPhone
            MySet2.Update ' Save changes.
    End If

Loop                        ' End of loop.

MySet.Close
MySet2.Close

Exit_Prop_Owners:
    DoCmd.SetWarnings True
    Exit Sub

Err_Multiple_Prop_Owners:
If Err.Number = 3021 Or Err.Number = 3022 Or Err.Number = 3061 Then
    Resume Exit_Prop_Owners
Else
    MsgBox Err.Number & ": " & Err.Description
    DoCmd.SetWarnings True
    Resume Exit_Prop_Owners
End If



End Sub


0
 
nico5038Commented:
Hmm, I see I overlooked the double recordset processing.

In this case you could make it easy on yourself by using a groupby query to count the number of rows per [PropOwnrLName] and [BPNumber] on forehand like:

select count(*), [PropOwnrLName], [BPNumber] from tblPropOwners group by [PropOwnrLName], [BPNumber]

using this in your loop gives the number on forehand and will allow the select of the row(s) with another recordset and knowing the processing on forehand.

Is it possible that you have 3 or more with the same combination ?

Nic;o)
0
 
Ronda-SAuthor Commented:
Hi Nico --

I think I understand what you are saying... If there's a husband/wife, then it would know that it would be processing two records.  If there are 3 owners (which do not exist at present with any records, but the database is designed for that to be a possibility), then my code would not work.  But with your idea, it would know there are three records to process.

Would I group solely by BPNumber, so it knows the number of records it will be combining, and then create some select case logic so that if there's one record, process along; if there's 2, then look at the last name and act accordingly, and if there's 3 or more then show the first and last names of all involved?

Could you help me out a bit on how to do this looping?  Would I set my intRecCount Variable to that select statement, or would I use a vlookup on that saved query?  Then in my loop I would say from 1 to intRecCount do what my Select Case statement directs?

Thanks again --

Ronda
0
 
Ronda-SAuthor Commented:
Hi Nico --

Sorry for the delay... on the evening of your last post (5/12), I went roller skating with my 7 year old and ended up in the ER with a broken fibula!!  Had to have surgery, and now I'm laid up in a cast.  Thank God for wireless networks and VPNs, as I'm currently programming from bed with my foot elevated.  :-)

Thanks for the idea... yes, I like your approach and will work that in.

Ronda
0
 
nico5038Commented:
Sorry to hear, but it will give you a lot of time to experiment and post new questions :-)
Hope you're soon "up and running" again !

Nic;o)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.