Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2010
    Posts
    22

    Unanswered: Access to Word Export Looping Problem

    Hello All,

    I'm trying to use a query(ies) in Access 2007 to generate data in Word. I want a header to print, then a table, then repeat. The problem is that with 1 query, I can't pull out the header separately but with 2 queries I get repeating data. It seems wrong to use 2 queries, but it seems to be impossible to use 1 query and the .EOF designator. I need to be able to use and .EOF or something similar for a piece of data like the header. Nested recordsets seem to be an alternative but I can't find any good information on how to format them.

    Here's a format of what I want the output to look like:

    State: Alabama Location: South

    Cities: Attractions:
    Mobile Seafood
    Montgomery Capital

    State: California Location: West

    Cities: Attractions
    San Diego Zoo
    San Francisco Golden Gate Bridge

    Instead, here's what the output looks like:

    State: Alabama Location: South

    Cities: Attractions:
    Mobile Seafood
    Montgomery Capital
    San Diego Zoo
    San Francisco Golden Gate Bridge

    State: California Location: West

    Cities: Attractions:
    Mobile Seafood
    Montgomery Capital
    San Diego Zoo
    San Francisco Golden Gate Bridge

    The header query contains only header data. The data query contains the header "State" and the rest is only data.

    Code sample to follow.

    Thanks.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What precisely do you call the header of a query? SELECT Queries return one or more rows of data (a rowset). Could you please explain and possibly provide an example of the code you use?
    Have a nice day!

  3. #3
    Join Date
    Dec 2010
    Posts
    22

    Code Sample Using 2 Recordsets

    Code sample for previous post:

    Private Sub city_data()

    Dim aWord As Word.Application
    Dim doc As Word.Document
    Dim dbs As DAO.Database
    Dim rst, rst1 As DAO.Recordset

    Set aWord = GetObject(, "Word.Application")
    Set doc = aWord.Documents.Add

    aWord.Visible = True

    ' Header
    Set dbs = CurrentDb
    Set rst1 = dbs.OpenRecordset("qry_Header", dbOpenDynaset)

    Do While Not rst1.EOF

    With aWord.Selection
    .TypeText "State: " & rst1![State] & ", Location: " & rst1![Loc]

    'Table
    doc.Tables.Add Range:=Selection.Range, _
    NumRows:=2, _
    NumColumns:=2, _
    DefaultTableBehavior:=wdWord9TableBehavior, _
    AutoFitBehavior:=wdAutoFitFixed

    With aWord.Selection.Tables(1)
    If .Style <> "Table Grid" Then
    .Style = "Table Grid"
    End If

    .ApplyStyleHeadingRows = True
    .ApplyStyleLastRow = False
    .ApplyStyleFirstColumn = True
    .ApplyStyleLastColumn = False
    .ApplyStyleRowBands = True
    .ApplyStyleColumnBands = False
    End With

    ' Headings
    With aWord.Selection
    .TypeText Text:="Cities"
    .MoveRight Unit:=wdCell, Count:=1
    .TypeText Text:="Attractions"
    .MoveRight Unit:=wdCell, Count:=1
    End With

    ' Data
    Set rst = dbs.OpenRecordset("qry_Data", dbOpenDynaset)

    Do While Not rst.EOF

    With aWord.Selection
    .TypeText rst![City]
    .MoveRight Unit:=wdCell, Count:=1
    .TypeText rst![Sites]
    .MoveRight Unit:=wdCell, Count:=1
    End With
    rst.MoveNext

    Loop

    aWord.Selection.Rows.Delete

    End With
    rst1.MoveNext

    Loop

    End Sub

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I don't know what SQL statements compose qry_Header and qry_Data but my first thought is that qry_Data should use one or more parameters filtering its returned rowset according to the current row of data returned by qry_Header.

    What does qry_Data looks like, is it parametric and how do you pass the value of the parameter for each call inside the loop?

    On second thought, can't you use a GROUP BY statement in a (single) query that would eliminate the necessity of having an inner loop (and a second query)? Not knowing how your data are organized, I can't be more precise but this could be path worth to be explored.
    Have a nice day!

  5. #5
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Rather than using "qry_Data", use a SQL string.

    Dim strSQL as string

    strSQL = grab the SQL of qry_Data and add WHERE state=rst1!state

    Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

    assumption: qry_Data has a field named state

  6. #6
    Join Date
    Dec 2010
    Posts
    22
    Thanks to you both.

    Sinndho: qry_Header contains top level state information such as the name, state flower, state bird, etc ... any state-wide characteristics that are the same regardless of the city. qry_Data has information just for a city. It is a UNION query with simple select statements. I would love to be able to use only 1 query for simplicity and maintainability but I haven't used GROUP BY much but am having trouble figuring out how it will work since I am formatting the data in MS Word.

    Rogue: Your suggestion seems straight forward and I could follow the logic. However after working through getting the strSQL query in the format needed by VBA, I've run into the error message below with this line:
    Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

    Run Time Error 3061 Too Few Parameters Expected 1

    Any thoughts on what I might be doing wrong?

    Thanks again!

  7. #7
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Sounds like there is an issue with the SQL statement. Post your SQL and we can take a look.

  8. #8
    Join Date
    Dec 2010
    Posts
    22
    Here is what it is like right now:
    strSQL = "SELECT [State], [City], [Site] FROM [City_Data] WHERE [State] = rst1.[State];"

    It is rather stripped down now because I removed all the table aliasing, multiple query continuation lines, and additional restrictions in the WHERE clause just in an effort to troubleshoot and try and find the problem.

    City_Data is the table name which is replacing the previous qry_Data.

  9. #9
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    try this

    strSQL = "SELECT [State], [City], [Site] FROM [City_Data] WHERE [State] = '" & rst1!State &"';"

  10. #10
    Join Date
    Dec 2010
    Posts
    22
    Thanks Rogue. While this didn't work when I tried it yesterday, apparently my computer needed some overnight rest because now it works! Go figure.

    Thanks Again.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •