Infinite Loop with 1st Record

[Hdata]

I have succeeded with retrieving subTable data from a One to many relationship, however, I'm stuck in an infinite loop processing the first record of the subtable and not moving to the next one. Any Help, Thanks in advance.


Dim db As DAO.Database
Dim rstLandData As DAO.Recordset
Set db = CurrentDb
Set rstLandData = db.OpenRecordset("qryLandData", dbOpenDynaset)
Dim strLandType As String
Dim strAcreage As String
Dim strFrontage As String
Dim strZoning As String
Dim strUtilities As String
Dim strTopography As String
Dim strAccess As String
Dim strShape As String
Dim strLandScaping As String
Dim strFloodData As String

strSQL = "SELECT tblLandData.LandDataID,tblLandData.fk_LandSaleID " _
& ",tblLandData.Acreage,tblLandData.Frontage,tblLandData.Zoning " _
& ", tblLandData.Utilities,tblLandData.Topography, tblLandData.Access " _
& ", tblLandData.Shape, tblLandData.Landscaping,tblLandData.FloodData " _
& "FROM tblLandSales INNER JOIN tblLandData ON tblLandSales.LandSalesID = tblLandData.fk_LandSaleID;"
rst.Open strSQL, CurrentProject.Connection, _
adOpenStatic, adLockReadOnly


Do While Not rstLandData.EOF
With rstLandData
strLandType = Nz(.Fields("LandType"))
strAcreage = Nz(.Fields("Acreage"))
strFrontage = Nz(.Fields("Frontage"))
strZoning = Nz(.Fields("Zoning"))
strUtilities = Nz(.Fields("Utilities"))
strTopography = Nz(.Fields("Topography"))
strAccess = Nz(.Fields("Access"))
strShape = Nz(.Fields("Shape"))
strLandScaping = Nz(.Fields("LandScaping"))
strFloodData = Nz(.Fields("FloodData"))
.Close
End With

objWord.Run "GHLandCompTable3"
With objWord.Selection
' .MoveRight Unit:=wdCell, Count:=2
.TypeText strLandType
.MoveRight Unit:=wdCell, Count:=2
.TypeText strAcreage
.MoveRight Unit:=wdCell, Count:=2
.TypeText strFrontage
.MoveRight Unit:=wdCell, Count:=2
.TypeText strZoning
.MoveRight Unit:=wdCell, Count:=2
.TypeText strUtilities
.MoveRight Unit:=wdCell, Count:=2
.TypeText strTopography
.MoveRight Unit:=wdCell, Count:=2
.TypeText strAccess
.MoveRight Unit:=wdCell, Count:=2
.TypeText strShape
.MoveRight Unit:=wdCell, Count:=2
.TypeText strLandScaping
.MoveRight Unit:=wdCell, Count:=2
.TypeText strFloodData
.MoveUp Unit:=wdLine, Count:=2
End With
rstLandData.MoveNext
Loop


End Sub

 

[JHB]

Explain what you are trying perform.

 

[namliam]

try posting code with the code tags [cade] code here [/cade] (replace the a by o offcourse)

Dim db As DAO.Database
Dim rstLandData As DAO.Recordset
Set db = CurrentDb
Set rstLandData = db.OpenRecordset("qryLandData", dbOpenDynaset)
Dim strLandType As String
Dim strAcreage As String
Dim strFrontage As String
Dim strZoning As String
Dim strUtilities As String
Dim strTopography As String
Dim strAccess As String
Dim strShape As String
Dim strLandScaping As String
Dim strFloodData As String
            
strSQL = "SELECT tblLandData.LandDataID,tblLandData.fk_LandSaleID " _
            & ",tblLandData.Acreage,tblLandData.Frontage,tblLandData.Zoning " _
            & ", tblLandData.Utilities,tblLandData.Topography, tblLandData.Access " _
            & ", tblLandData.Shape, tblLandData.Landscaping,tblLandData.FloodData " _
            & "FROM tblLandSales INNER JOIN tblLandData ON tblLandSales.LandSalesID = tblLandData.fk_LandSaleID;"
            rst.Open strSQL, CurrentProject.Connection, _
                adOpenStatic, adLockReadOnly
            
            
      Do While Not rstLandData.EOF
            With rstLandData
                strLandType = Nz(.Fields("LandType"))
                strAcreage = Nz(.Fields("Acreage"))
                strFrontage = Nz(.Fields("Frontage"))
                strZoning = Nz(.Fields("Zoning"))
                strUtilities = Nz(.Fields("Utilities"))
                strTopography = Nz(.Fields("Topography"))
                strAccess = Nz(.Fields("Access"))
                strShape = Nz(.Fields("Shape"))
                strLandScaping = Nz(.Fields("LandScaping"))
                strFloodData = Nz(.Fields("FloodData"))
[COLOR="Red"]                .Close[/COLOR]
            End With

            objWord.Run "GHLandCompTable3"
            With objWord.Selection
          '  .MoveRight Unit:=wdCell, Count:=2
            .TypeText strLandType
            .MoveRight Unit:=wdCell, Count:=2
            .TypeText strAcreage
            .MoveRight Unit:=wdCell, Count:=2
            .TypeText strFrontage
            .MoveRight Unit:=wdCell, Count:=2
            .TypeText strZoning
            .MoveRight Unit:=wdCell, Count:=2
            .TypeText strUtilities
            .MoveRight Unit:=wdCell, Count:=2
            .TypeText strTopography
            .MoveRight Unit:=wdCell, Count:=2
            .TypeText strAccess
            .MoveRight Unit:=wdCell, Count:=2
            .TypeText strShape
            .MoveRight Unit:=wdCell, Count:=2
            .TypeText strLandScaping
            .MoveRight Unit:=wdCell, Count:=2
            .TypeText strFloodData
            .MoveUp Unit:=wdLine, Count:=2
        End With
rstLandData.MoveNext
Loop

I am guessing the ".Close" is the evil doer here.

 

[CJ_London]

.close should be outside the loop, and you should have .movenext instead within the loop

 

[JHB]

...and you should have .movenext instead within the loop

It is in the loop.

...
..
End With
rstLandData.MoveNext
Loop

 

[CJ_London]

Sorry nissed it due to the poor layout

 

[Hdata]

JHB -
Explain what you are trying perform.

Hdata
What I am trying to do is export data from the access database to a word document.


Also,
Problem solved. the ".Close" is the evil doer here. Once I moved this line outside the loop I was exporting the remaining subTable data. Thank you all so much.

 

[namliam]

Sorry nissed it due to the poor layout

So much bad layout everywhere, really wish people would worry about layout when writing code, adding comments here and there..... and offcourse worry about layout when splashing code onto any forum....
Having readable code makes the world of difference.