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).Dat
Set MySetUn = MyDB.OpenRecordset("qryPer
MySetUn.Sort = "[bpnumber],[propownrlname
Set MySet = MySetUn.OpenRecordset()
Set MySet2 = MyDB.OpenRecordset("tblTem
'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 <=========================
If TempBPNum = MySet![BPNumber] Then
If TempLName = Nz(MySet![PropOwnrLName], "NONE") Then
MySet2.AddNew ' Create new record.
MySet2![PropOwnrsFirstName
MySet2![PropOwnrsLastName]
MySet2![BPNumber] = TempBPNum
MySet2![PropOwnrPhone] = TempPhone
MySet2.Update ' Save changes.
MySet.MoveNext
Else
MySet2.AddNew ' Create new record.
MySet2![PropOwnrsFirstName
MySet2![PropOwnrsLastName]
MySet2![BPNumber] = TempBPNum
MySet2![PropOwnrPhone] = TempPhone
MySet2.Update ' Save changes.
MySet.MoveNext
End If
Else
MySet2.AddNew ' Create new record.
MySet2![PropOwnrsFirstName
MySet2![PropOwnrsLastName]
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
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).Dat
Set MySetUn = MyDB.OpenRecordset("qryPer
MySetUn.Sort = "[bpnumber],[propownrlname
Set MySet = MySetUn.OpenRecordset()
Set MySet2 = MyDB.OpenRecordset("tblTem
'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 <=========================
If TempBPNum = MySet![BPNumber] Then
If TempLName = Nz(MySet![PropOwnrLName], "NONE") Then
MySet2.AddNew ' Create new record.
MySet2![PropOwnrsFirstName
MySet2![PropOwnrsLastName]
MySet2![BPNumber] = TempBPNum
MySet2![PropOwnrPhone] = TempPhone
MySet2.Update ' Save changes.
MySet.MoveNext
Else
MySet2.AddNew ' Create new record.
MySet2![PropOwnrsFirstName
MySet2![PropOwnrsLastName]
MySet2![BPNumber] = TempBPNum
MySet2![PropOwnrPhone] = TempPhone
MySet2.Update ' Save changes.
MySet.MoveNext
End If
Else
MySet2.AddNew ' Create new record.
MySet2![PropOwnrsFirstName
MySet2![PropOwnrsLastName]
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
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)
Set MySet = MySetUn.OpenRecordset()
IF MySet.EOF and MySet.BOF then
' action for empty set
endif
MySet.movefirst
etc..
Nic;o)
Instead of Do Until MySet.EOF how about
DO WHILE NOT MySet.EOF
I've never had problems with this???
Scott C
DO WHILE NOT MySet.EOF
I've never had problems with this???
Scott C
ASKER
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 <=========================
--------------------------
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 <=========================
Set MyDB = DBEngine.Workspaces(0).Dat
Set MySetUn = MyDB.OpenRecordset("qryPer
MySetUn.Sort = "[bpnumber],[propownrlname
Set MySet = MySetUn.OpenRecordset()
Set MySet2 = MyDB.OpenRecordset("tblTem
intRecCount = 1 <=========================
'empty temp table before building
MyDB.Execute "DELETE tblTempPropOwner.* FROM tblTempPropOwner;", dbFailOnError
MySet.MoveLast
MySet.MoveFirst ' set start <=========================
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 <=========================
MySet.MoveNext
intRecCount = intRecCount + 1 <=========================
End If <=========================
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
MySet2![PropOwnrsLastName]
MySet2![BPNumber] = TempBPNum
MySet2![PropOwnrPhone] = TempPhone
MySet2.Update ' Save changes.
MySet.MoveNext
intRecCount = intRecCount + 1 <=========================
End If
Else
MySet2.AddNew ' Create new record.
MySet2![PropOwnrsFirstName
MySet2![PropOwnrsLastName]
MySet2![BPNumber] = TempBPNum
MySet2![PropOwnrPhone] = TempPhone
MySet2.Update ' Save changes.
MySet.MoveNext
intRecCount = intRecCount + 1 <=========================
End If
Else
MySet2.AddNew ' Create new record.
MySet2![PropOwnrsFirstName
MySet2![PropOwnrsLastName]
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
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 <=========================
--------------------------
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 <=========================
Set MyDB = DBEngine.Workspaces(0).Dat
Set MySetUn = MyDB.OpenRecordset("qryPer
MySetUn.Sort = "[bpnumber],[propownrlname
Set MySet = MySetUn.OpenRecordset()
Set MySet2 = MyDB.OpenRecordset("tblTem
intRecCount = 1 <=========================
'empty temp table before building
MyDB.Execute "DELETE tblTempPropOwner.* FROM tblTempPropOwner;", dbFailOnError
MySet.MoveLast
MySet.MoveFirst ' set start <=========================
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 <=========================
MySet.MoveNext
intRecCount = intRecCount + 1 <=========================
End If <=========================
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
MySet2![PropOwnrsLastName]
MySet2![BPNumber] = TempBPNum
MySet2![PropOwnrPhone] = TempPhone
MySet2.Update ' Save changes.
MySet.MoveNext
intRecCount = intRecCount + 1 <=========================
End If
Else
MySet2.AddNew ' Create new record.
MySet2![PropOwnrsFirstName
MySet2![PropOwnrsLastName]
MySet2![BPNumber] = TempBPNum
MySet2![PropOwnrPhone] = TempPhone
MySet2.Update ' Save changes.
MySet.MoveNext
intRecCount = intRecCount + 1 <=========================
End If
Else
MySet2.AddNew ' Create new record.
MySet2![PropOwnrsFirstName
MySet2![PropOwnrsLastName]
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
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)
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)
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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)
Hope you're soon "up and running" again !
Nic;o)
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
MySet2![PropOwnrsLastName]
MySet2![BPNumber] = TempBPNum
MySet2![PropOwnrPhone] = TempPhone
MySet2.Update ' Save changes.
MySet.MoveNext
Else
MySet2.AddNew ' Create new record.
MySet2![PropOwnrsFirstName
MySet2![PropOwnrsLastName]
MySet2![BPNumber] = TempBPNum
MySet2![PropOwnrPhone] = TempPhone
MySet2.Update ' Save changes.
MySet.MoveNext
End If
Else
MySet2.AddNew ' Create new record.
MySet2![PropOwnrsFirstName
MySet2![PropOwnrsLastName]
MySet2![BPNumber] = TempBPNum
MySet2![PropOwnrPhone] = TempPhone
MySet2.Update ' Save changes.
End If
MySet.MoveNext <=========================
Loop ' End of loop.