Exporting subTable records w/ Primary Records

H

Hdata

Registered User.
Local time
Today, 18:31
Joined
Sep 10, 2013
Messages
56
All seemed to be working well, however, I noticed that all my subtable records in the database are exporting with each Primary table record. In my output, i'm looking to see each primary table record followed by one or more subtable records from a one to many relationship.

Thanks in advance. (Office 2010) Access/Word

Private Sub cmdPrint1_Click()
Dim objWord As Word.Application
Dim docm As Word.Document
Dim db As DAO.Database
Dim rstLandSales As DAO.Recordset
Dim rstLandData As DAO.Recordset
Dim strLandSalesID As String
Dim strPropertyName As String
Dim strClass As String
Dim J As Integer
Set db = CurrentDb
Set rstLandSales = db.OpenRecordset("qryLandSales")
On Error Resume Next
Set objWord = GetObject(, "Word.application")
If Err = 429 Then
Set objWord = New Word.Application
End If
On Error GoTo 0
With objWord
Set docm = .Documents.Add("C:\Users\GlennJ\AppData\Roaming\Microsoft\Templates\Report2007Forms.dotm")
.Visible = True
objWord.ActiveWindow = True
End With
Do While Not rstLandSales.EOF
With rstLandSales '(Primary Table records)
strLandSalesID = Nz(.Fields("[LandSalesID]"))
strPropertyName = Nz(.Fields("[PropertyName]"))
strClass = Nz(.Fields("[Class]"))
objWord.Run "GHLandCompTable1"
With objWord.Selection
.MoveRight Unit:=wdCell, Count:=2
.TypeText strLandSalesID
.MoveRight Unit:=wdCell, Count:=2
.TypeText strPropertyName
.MoveRight Unit:=wdCell, Count:=2
.TypeText strPropertyName
.MoveRight Unit:=wdCell, Count:=2
.TypeText strClass
.MoveRight Unit:=wdCell, Count:=2
End With
On Error Resume Next
ActiveDocument.GoTo What:=wdGoToTable, Which:=wdGoToLast
On Error Resume Next
With ActiveDocument.Content
With Selection
.Tables(1).Cell(1, 1).Select
.MoveRight Unit:=wdCell, Count:=1
.EndKey Unit:=wdLine
J = J + 1
.TypeText Text:=J
.MoveLeft Unit:=wdWord, Count:=1, Extend:=wdExtend
End With
objWord.Run "bmLandCompdetailP1"
objWord.Run "SpaceDeleteUp1"
End With
End With
'LandData (SubTable Records)
Dim strLandType As String
Dim strAcreage As String
Dim strFrontage As String
Dim strZoning As String
Dim L As Integer
L = 0
Set rstLandData = db.OpenRecordset("qryLandData")
Do While Not rstLandData.EOF
With rstLandData
strLandType = Nz(.Fields("[LandType]"))
strAcreage = Nz(.Fields("[Acreage]"))
strFrontage = Nz(.Fields("[Frontage]"))
strZoning = Nz(.Fields("[Zoning]"))
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
.MoveUp Unit:=wdLine, Count:=2
End With
On Error Resume Next
ActiveDocument.GoTo What:=wdGoToTable, Which:=wdGoToLast
On Error Resume Next

With ActiveDocument.Content
With Selection
.Tables(1).Cell(1, 1).Select
.MoveRight Unit:=wdCell, Count:=1
.EndKey Unit:=wdLine
L = L + 1
.TypeText Text:=L
.MoveLeft Unit:=wdWord, Count:=1, Extend:=wdExtend
End With
End With
objWord.Run "bmLandCompdetailP3"
objWord.Run "SpaceDeleteUp1"
rstLandData.MoveNext
Loop
rstLandData.Close


rstLandSales.MoveNext
Loop
rstLandSales.Close
End Sub
 
please use code tags when you splash code on the forum, simply click the # sign in the posting menu, or type [cade] in front and [/cade] behind the code (replace the a by an o offcourse)

The problem mostlikely would be in your query, does that have the proper criteria?
 
Thank you for your response to my exporting problem. Sometimes I just need a direction or broad evaluation of my issues, so I can research further. I believe my issue in this case is the query design as you mentioned. I will study Chapter 10 from "MS Access Inside Out" Building Complex Queries. Also, in the future I will remember to Use Tags with my code. Thanks again and have a great day.:)
 
Well perhaps post your SQL here, perhaps we can spot the problem.
 
Thanks again, as I mentioned this code runs well, except I get all the subtable records with each primary table record in a 1 to many relationship.:)

Code: 
Private Sub cmdPrint1_Click()
Dim objWord As Word.Application
Dim docm As Word.Document
Dim db As DAO.Database
Dim rstLandSales As DAO.Recordset
Dim rstLandData As DAO.Recordset
Dim strLandSalesID As String
'Dim strPhotograph As Attachment
Dim strPropertyName As String
Dim strClass As String
Dim strAddress As String
Dim strLocation As String
Dim strMunicipality As String
Dim strTownship As String
Dim strCounty As String
Dim strState As String
Dim strTaxParcelID As String
Dim strLatitude As String
Dim strLongitude As String
Dim strRequestor As String
Dim dtmRequestDate As Date
Dim strNotes As String
Dim strDescription As String
Dim strAmount As String
Dim curTotalAmount As Currency
'Table 2
Dim j As Integer
j = 0
Set db = CurrentDb
Set rstLandSales = db.OpenRecordset("qryLandSales")
On Error Resume Next
Set objWord = GetObject(, "Word.application")
If Err = 429 Then
    Set objWord = New Word.Application
End If
On Error GoTo 0
With objWord
        Set docm = .Documents.Add("C:\Users\GlennJ\AppData\Roaming\Microsoft\Templates\Report2007Forms.dotm")
        .Visible = True
        objWord.ActiveWindow = True
End With
Do While Not rstLandSales.EOF
With rstLandSales
    strLandSalesID = Nz(.Fields("LandSalesID"))
    strPropertyName = Nz(.Fields("PropertyName"))
    strPropertyName = Nz(.Fields("PropertyName"))
    strClass = Nz(.Fields("Class"))
    strAddress = Nz(.Fields("Address"))
    strLocation = Nz(.Fields("Location"))
    strMunicipality = Nz(.Fields("City"))
    strTownship = Nz(.Fields("Township"))
    strCounty = Nz(.Fields("County"))
    strState = Nz(.Fields("State"))
    strTaxParcelID = Nz(.Fields("TaxParcelNo"))
    strLatitude = Nz(.Fields("Latitude"))
    strLongitude = Nz(.Fields("Longitude"))
objWord.Run "GHLandCompTable1"
        With objWord.Selection
            .MoveRight Unit:=wdCell, Count:=2
            .TypeText strLandSalesID
            .MoveRight Unit:=wdCell, Count:=2
            .TypeText strPropertyName
            .MoveRight Unit:=wdCell, Count:=2
            .TypeText strPropertyName
            .MoveRight Unit:=wdCell, Count:=2
            .TypeText strClass
            .MoveRight Unit:=wdCell, Count:=2
            .TypeText strAddress
            .MoveRight Unit:=wdCell, Count:=2
            .TypeText strLocation
            .MoveRight Unit:=wdCell, Count:=2
            .TypeText strMunicipality
            .MoveRight Unit:=wdCell, Count:=2
            .TypeText strTownship
            .MoveRight Unit:=wdCell, Count:=2
            .TypeText strCounty
            .MoveRight Unit:=wdCell, Count:=2
            .TypeText strState
            .MoveRight Unit:=wdCell, Count:=2
            .TypeText strTaxParcelID
            .MoveRight Unit:=wdCell, Count:=2
            .TypeText strLatitude
            .MoveRight Unit:=wdCell, Count:=2
            .TypeText strLongitude
            .MoveRight Unit:=wdCell, Count:=2
        End With
        On Error Resume Next
        ActiveDocument.GoTo What:=wdGoToTable, Which:=wdGoToLast
        On Error Resume Next
            With ActiveDocument.Content
                With Selection
                   .Tables(1).Cell(1, 1).Select
                    .MoveRight Unit:=wdCell, Count:=1
                    .EndKey Unit:=wdLine
                    j = j + 1
                    .TypeText Text:=j
                    .MoveLeft Unit:=wdWord, Count:=1, Extend:=wdExtend
            End With
objWord.Run "bmLandCompdetailP1"
objWord.Run "SpaceDeleteUp1"
End With
'Table 2 (Delete for now)
End With
'LandData
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
Dim L As Integer
L = 0
Set rstLandData = db.OpenRecordset("qryLandData")
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"))
   
                objWord.Run "GHLandCompTable3"
                With objWord.Selection
                    .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
        On Error Resume Next
        ActiveDocument.GoTo What:=wdGoToTable, Which:=wdGoToLast
        On Error Resume Next
                                        With ActiveDocument.Content
                                            With Selection
                                               .Tables(1).Cell(1, 1).Select
                                                .MoveRight Unit:=wdCell, Count:=1
                                                .EndKey Unit:=wdLine
                                                L = L + 1
                                                .TypeText Text:=L
                                                .MoveLeft Unit:=wdWord, Count:=1, Extend:=wdExtend
                                            End With
                                            End With
            
End With
        objWord.Run "bmLandCompdetailP3"
        objWord.Run "SpaceDeleteUp1"
        
rstLandData.MoveNext
Loop
rstLandData.Close
        
rstLandSales.MoveNext
Loop
rstLandSales.Close
End Sub
 
Sorry about my lack of experience with these components. Now I understand. I will post the SQL from the two queries. I assume I should also tag the SQL. Please excuse my learning curve.:D Best regards

qryLandSales

Code: 
SELECT tblLandSales.LandSalesID, tblLandSales.CheckBox1, tblLandSales.State, tblLandSales.Photograph, tblLandSales.PropertyName, tblLandSales.Class, tblLandSales.SaleDate, tblLandSales.ReportedSalePrice, tblLandSales.DeedInstrument, tblLandSales.CashEquiv, tblLandSales.UpwardAdjustment, tblLandSales.UpwardAdjustmentComment, tblLandSales.DownwardAdjustment, tblLandSales.DownwardAdjustmentComment, tblLandSales.IndicatedSalePrice, tblLandSales.Remarks, tblLandSales.SaleHistory, tblLandSales.AcresNet, tblLandSales.[Price/Ac], tblLandSales.[Price/SF], tblLandSales.Location, tblLandSales.Neighborhood, [StreetNumber] & " "+[StreetDirection] & " "+[StreetName] & " "+[StreetType] AS Address, tblLandSales.City, tblLandSales.County, tblLandSales.State, tblLandSales.ZipCode, tblLandSales.Township, tblLandSales.SchoolDistrict, tblLandSales.FireDistrict, tblLandSales.Appraiser, tblLandSales.RecordingDate, tblLandSales.TaxSalePrice, tblLandSales.DeedInstrument, tblLandSales.DeedBook, tblLandSales.DeedPage, tblLandSales.PlatBook, tblLandSales.PlatPage, tblLandSales.Stps, tblLandSales.TaxAces, tblLandSales.AcresGross, tblLandSales.Grantor, tblLandSales.GrantorFileAs, tblLandSales.GrantorAddress, tblLandSales.Grantee, tblLandSales.GranteeFileAs, tblLandSales.GranteeAddress, tblLandSales.LegalDesc, tblLandSales.Subdivision, tblLandSales.TaxParcelNo, tblLandSales.VacantLot, tblLandSales.Latitude, tblLandSales.Longitude, tblLandSales.MarketingTime, tblLandSales.PropertyRights, tblLandSales.Financing, tblLandSales.ConditionofSale, tblLandSales.ExpenditureAfterSale, tblLandSales.StreetName, tblLandSales.Verification, tblLandSales.[Date-Created], tblLandSales.[Created-By], tblLandSales.[Date-Modified], tblLandSales.[Modified-By]
FROM tblLandSales
ORDER BY tblLandSales.LandSalesID;

qryLandData

SELECT tblLandSales.LandSalesID, tblLandData.fk_LandSaleID, tblLandData.LandDataID, tblLandData.Acreage, tblLandData.Frontage, tblLandData.Zoning, tblLandData.Utilities, tblLandData.Topography, tblLandData.Access, tblLandData.Shape, tblLandData.Landscaping, tblLandData.FloodData, tblLandData.[Date-Created], tblLandData.[Created-By], tblLandData.[Date-Modified], tblLandData.[Modified-By]
FROM tblLandSales INNER JOIN tblLandData ON tblLandSales.LandSalesID = tblLandData.fk_LandSaleID
ORDER BY tblLandSales.LandSalesID;
 
I will repost tagging SQL from both queries. Sorry about my lack of experience with these components. Now I understand. I will post the SQL from the two queries. I assume I should also tag the SQL. Please excuse my learning curve.
biggrin.gif
Best regards.

qryLandSales

Code: 
SELECT tblLandSales.LandSalesID, tblLandSales.CheckBox1, tblLandSales.State, tblLandSales.Photograph, tblLandSales.PropertyName, tblLandSales.Class, tblLandSales.SaleDate, tblLandSales.ReportedSalePrice, tblLandSales.DeedInstrument, tblLandSales.CashEquiv, tblLandSales.UpwardAdjustment, tblLandSales.UpwardAdjustmentComment, tblLandSales.DownwardAdjustment, tblLandSales.DownwardAdjustmentComment, tblLandSales.IndicatedSalePrice, tblLandSales.Remarks, tblLandSales.SaleHistory, tblLandSales.AcresNet, tblLandSales.[Price/Ac], tblLandSales.[Price/SF], tblLandSales.Location, tblLandSales.Neighborhood, [StreetNumber] & " "+[StreetDirection] & " "+[StreetName] & " "+[StreetType] AS Address, tblLandSales.City, tblLandSales.County, tblLandSales.State, tblLandSales.ZipCode, tblLandSales.Township, tblLandSales.SchoolDistrict, tblLandSales.FireDistrict, tblLandSales.Appraiser, tblLandSales.RecordingDate, tblLandSales.TaxSalePrice, tblLandSales.DeedInstrument, tblLandSales.DeedBook, tblLandSales.DeedPage, tblLandSales.PlatBook, tblLandSales.PlatPage, tblLandSales.Stps, tblLandSales.TaxAces, tblLandSales.AcresGross, tblLandSales.Grantor, tblLandSales.GrantorFileAs, tblLandSales.GrantorAddress, tblLandSales.Grantee, tblLandSales.GranteeFileAs, tblLandSales.GranteeAddress, tblLandSales.LegalDesc, tblLandSales.Subdivision, tblLandSales.TaxParcelNo, tblLandSales.VacantLot, tblLandSales.Latitude, tblLandSales.Longitude, tblLandSales.MarketingTime, tblLandSales.PropertyRights, tblLandSales.Financing, tblLandSales.ConditionofSale, tblLandSales.ExpenditureAfterSale, tblLandSales.StreetName, tblLandSales.Verification, tblLandSales.[Date-Created], tblLandSales.[Created-By], tblLandSales.[Date-Modified], tblLandSales.[Modified-By]
FROM tblLandSales
ORDER BY tblLandSales.LandSalesID;

qry LandData

Code: 
SELECT tblLandSales.LandSalesID, tblLandData.fk_LandSaleID, tblLandData.LandDataID, tblLandData.Acreage, tblLandData.Frontage, tblLandData.Zoning, tblLandData.Utilities, tblLandData.Topography, tblLandData.Access, tblLandData.Shape, tblLandData.Landscaping, tblLandData.FloodData, tblLandData.[Date-Created], tblLandData.[Created-By], tblLandData.[Date-Modified], tblLandData.[Modified-By]
FROM tblLandSales INNER JOIN tblLandData ON tblLandSales.LandSalesID = tblLandData.fk_LandSaleID
ORDER BY tblLandSales.LandSalesID;
 
Yes you should tag the SQL and much like your code, preferably in a readable state...
Your code needs quite some re-work / re-spacing to make it much much more readable
Much like you did in this part:
Code: 
If Err = 429 Then
    Set objWord = New Word.Application
End If
Each component "needs" an indent and when closed "re-dented" back, like you do here with If/End If
You should do this with your With/End With, Do while/Loop even Private Sub/End Sub and any more that are simular like For/Next

ALso it is "good practice" to only define (DIM) variables at the top of any sub/function, so you dont end up redeclaring variable you already have.

Now as for your "original" problem
Code: 
SELECT tblLandSales.LandSalesID, tblLandData.fk_LandSaleID, tblLandData.LandDataID, tblLandData.Acreage, tblLandData.Frontage, tblLandData.Zoning, tblLandData.Utilities, tblLandData.Topography, tblLandData.Access, tblLandData.Shape, tblLandData.Landscaping, tblLandData.FloodData, tblLandData.[Date-Created], tblLandData.[Created-By], tblLandData.[Date-Modified], tblLandData.[Modified-By]
FROM tblLandSales 
INNER JOIN tblLandData ON tblLandSales.LandSalesID = tblLandData.fk_LandSaleID
ORDER BY tblLandSales.LandSalesID;
What you are doing here is select everything from your tblLandSales and tblLandData, there is no "where" anywhere to limit the amount of data being exported

This is my assumption... is this correct?
What you have is:
All LandSales
all LandData
What I am guessing you want to do is
LandSale1
Landdata1
LandSale2
LandData2
...
Landsale99
Landdata99
 
Thanks, I will work on this today, although I may have some questions during this re-work, I want to get it correct.
 
This is my assumption... is this correct?
What you have is:
All LandSales
all LandData
What I am guessing you want to do is
LandSale1
Landdata1
LandSale2
LandData2
...
Landsale99
Landdata99

Let me show you what I am currently getting
LandSale1
LandData1
LandData2
and all remaining LandData Records

LandSale2
LandData1
LandData2
and all remaining LandData Records

and so on thru the end of LandSale Records


Let me show you what I want
LandSale1
LandData1
LandData2
and all remaining LandData Records associated with LandSale1 on a 1:many relation.

LandSale2
LandData1
LandData2
and all remaining LandData Records associated with LandSale2 on a 1:many relation

and so on thru the end of LandSale Records
 
Ah yes, I can see that now thru the coding mess... however my earlier statement remains correct, "No where clause" to do what you require it to do... try replacing this line:
Code: 
Set rstLandData = db.OpenRecordset("qryLandData")
by
Code: 
    Set rstLandData = db.OpenRecordset(" Select * " & _
                                       " from qryLandData " & _
                                       " where LandSalesID = " & rstLandSales!LandSalesID)
That should fix your problem for you... I do however seriously advice you to clean up your coding.... readable code is maintainable code.
 
Incredible, simply incredible. I cannot thank you enough. Works great just like I was looking for. I hope you have a great day, you made my day!
 
Hdata said:
you made my day!
Click to expand...
And you will make my day if you make your code readable
You will agree with me that... 1) is a lot worse than 2)
Code: 
    Set rstLandData = db.OpenRecordset(" Select                                     *                                                                        from                                                                      qryLandData                                                          where                                                                              LandSalesID                                                                                                                     = "  & rstLandSales!LandSalesID)
    Set rstLandData = db.OpenRecordset(" Select * " & _
                                       " from qryLandData " & _
                                       " where LandSalesID = " & rstLandSales!LandSalesID)
Yet both will work...
 
I agree! And I will learn your ways and make my code readable! Thanks
 
You must log in or register to reply here.

Similar threads

F
Clear Word Table Using VBA
Replies
1
Views
540
JHB
J
D
Solved Export All Access Contacts to Outlook
2 3
Replies
48
Views
5,466
dgreen
D
G
Find Record Count during transaction
Replies
7
Views
1,458
The_Doc_Man
The_Doc_Man
Frothingslosh
Weird Adobe Export Error
Replies
4
Views
2,601
The_Doc_Man
The_Doc_Man
H
Infinite Loop with 1st Record
Replies
7
Views
1,054
namliam
N

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom