I need a Report with 10 queries placed in it!!!! 2

[chubby]

Hello all,
I have 10 queries that I need to place in one report called "NSC DAILY MUSTER REPORT". This report needs to contain 10 queries that are based on counting items in text fields from the "Personnel info table". Examples:[Officers/Enlisted],[PG], [Rate],[Gender],[status]and [remarks] just to name a few. The count is fine what need this infomation in one report...

I have tried to create a report that is not connected via record source to any query or table and then enter in text boxes that have the control source set to the query item that I would like reported and all that shows up is the #Name? error message.

I tried Access bible??? no luck!!

I tried making an 11th query to bring the other 10 into. That just screwed everything up. I Have all the right numbers, I just need them placed in one report...

and by the way, it's Access 97. I use 2000 at home
Thanks
Chubby...

 

[LonnieJohnson]

What about making 10 subreports on your main report? ljprodev@yahoo.com
Professional Development
MS Access Applications

 

[Aivars]

Hi!

Other way: create one query from yor ten queries.
Aivars

 

[petelyons]

I had this same challenge before. The only way I could incorporate the output of multiple queries into a singel report was to make a unique subreport for each and every query. Although tedious, this is the only real way to do it.

 

[Telsa]

OR you could try to populate a WOrd doc instead. I use up to 11 SQL queries to populate a WOrd doc in designated places. SOme of my data required tables.. some required only a single piece of info. I'm not talking merge, but using Word objects to populate the doc based on the name of the bookmarks.

Here is info on how it is done:

Bet you can figure out how to make it work for you! You will need to add the Word Object via Tools-References. I found the info to develop this in Access 2000 Beginning VBA by Wrox. Between that and the Word 2000 VBA book by Wrox, I have successfully made an easy to update Word template for Access.

Here it is!

Generating Word Doc from Access without using Merge

WORD TEMPLATE
Create your Word document with a template format. Save it as a template file (.dot). Use bookmarks to mark the place you want the data to be pulled in. You can have as many bookmarks as you want. If you require the data to be pulled into tables, don't create tables in Word, but let Access VBA create the tables for you.

ACCESS DATABASE
Set up queries showing the fields you want to transfer to the Word document. You may need to set up more than one query. If you have to do that, then you will need to set up each as a recordset in the code with it's own SQL string. The idea of the SQL string is to narrow down the records in the recordset to the exact info you need.

Assuming only two queries were made, code as follows:

In a module, key in a Public variable to be shared in database


Option Compare Database
Option Explicit

' location of the documents and templates -
' Where will Access find the Word Template?
Public Const m_strDIR As String = "d:\database\"
Public Const m_strTEMPLATE As String = "submittalcd.dot"

' set up objects for use and Public variables to be shared in database
Private m_objWord As Word.Application
Private m_objDoc As Word.Document
Public strProdNum As String

In the Forms Button for starting the event…

Create SQL statements based on the values of the active record (i.e., prodnum)

Click event:

Dim db As DAO.Database
Dim recSubmittal As DAO.Recordset
Dim recSubmittal2 As DAO.Recordset
Dim strSQL As String
Dim strSQL2 As String


' Capture the field whose value will narrow your recordset down
strProdNum = Me.PartsID

strSQL = "SELECT * FROM qrySubmittalBase WHERE ProdNum= '" & strProdnum & "';"
Set db = CurrentDb()
Set recSubmittal = db.OpenRecordset(strSQL)

StrSQL2 = "SELECT * FROM qrySubmittalDetail WHERE ProdNum= '" & strProdnum & "';"
Set db = CurrentDb()
Set recSubmittal2 = db.OpenRecordset(strSQL2)



' This CreateSubmittal sub is created in the module
CreateSubmittal recSubmittal, recSubmittal2

Back in the module, create the above sub (remember, this is referenced in the Forms click procedure)
This can be a little confusing here… the recSubmit is capturing the recSubmittal and the recSubmit2 is capturing the recSubmittal2 recordsets.

Public Sub CreateSubmittal(recSubmit As DAO.Recordset, recSubmit2 As DAO.Recordset)

Set m_objWord = New Word.Application
Set m_objDoc = m_objWord.Documents.Add(m_strDIR & m_strTEMPLATE)

m_objWord.Visible = True

InsertTextAtBookmark "basepart", recSubmit("base&quot
InsertTextAtBookmark "title", recSubmit("title-version&quot
InsertTextAtBookmark "bundledparts", recSubmit("bundledparts&quot
InsertTextAtBookmark "ReleaseDate", recSubmit("ReleaseDate&quot
InsertTextAtBookmark "version", recSubmit("Version&quot


' Generate the table data
InsertSummaryTable recSubmit2


Set m_objDoc = Nothing
Set m_objWord = Nothing


End Sub

Private Sub InsertTextAtBookmark(strBkmk As String, varText As Variant)
' This finds the bookmarks in the Word template to place the data.
m_objDoc.Bookmarks(strBkmk).Select
m_objWord.Selection.Text = varText & ""

End Sub

Private Sub InsertSummaryTable(recR As DAO.Recordset)
' This pulls in the data for a table then highlights the data
' and creates a table in the Word document at a bookmark location
' for each field you want in the column of the table, have tabs
' surround it. Items in quotes are field names from the query/recordset
' If you need to have a blank column, just place vbTab in twice
On Error GoTo No_Record_Err
Dim strTable As String
Dim objTable As Word.Table

recR.MoveFirst
strTable = ""
While Not recR.EOF
strTable = strTable & vbTab & recR("discontinuedpart&quot & vbTab & vbTab & recR("5x5No&quot & vbCr
recR.MoveNext
Wend

InsertTextAtBookmark "DiscPart", strTable
Set objTable = m_objWord.Selection.ConvertToTable(Separator:=vbTab)

objTable.Select
objTable.Columns(1).Width = InchesToPoints(1.51)
objTable.Columns(2).Width = InchesToPoints(2.56)
objTable.Columns(3).Width = InchesToPoints(1.44)
objTable.Columns(4).Width = InchesToPoints(2.14)

Set objTable = Nothing
No_Record_Err:
Exit Sub

End Sub



HTH

Mary X-)

 

[chubby]

Thanks Mary, You just blow me away with that answer!!!!! I can see I have a lot to learn...