[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2006-05-11
9
Medium Priority
?
404 Views
Last Modified: 2008-02-01
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


0
Comment
Question by:Ronda-S
  • 5
  • 3
9 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 16663202
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
 
LVL 54

Expert Comment

by:nico5038
ID: 16663212
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
 
LVL 20

Expert Comment

by:clarkscott
ID: 16665532
Instead of Do Until MySet.EOF    how about

DO WHILE NOT MySet.EOF

I've never had problems with this???

Scott C
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Ronda-S
ID: 16669804
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
 
LVL 54

Expert Comment

by:nico5038
ID: 16670238
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
 

Author Comment

by:Ronda-S
ID: 16670813
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
 
LVL 54

Accepted Solution

by:
nico5038 earned 2000 total points
ID: 16670950
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
 

Author Comment

by:Ronda-S
ID: 16754466
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
 
LVL 54

Expert Comment

by:nico5038
ID: 16754955
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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question