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.

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?

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

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.

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

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!

Sounds like there is an issue with the SQL statement. Post your SQL and we can take a look.

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.

try this

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

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.