I think I see what you're saying...thanks for saying it, btw... and I've got two more questions, now:
Assuming I create a separate function to do what you're saying which generates a big string full of all the info I need, lets call that string people, I could populate a bookmark after successfully doing the above like so:
Code:
ActiveDocument.Bookmarks.("thepeople").Select
Selection.Text = (people)
correct?
Also, if this function gets executed from a form (which has a specific row in a table that's being looked at), what exactly does Me.field refer to? I'm being told that i have an invalid use of the Me keyword... would Me.Name be a string? If not, how would I make it a string?
...Meh, I did some workarounding. Anyway, here's what I got, now: Object not found error at this line on the first function:
Code:
Set cops = getCops(CStr(Forms!Case!DName))
Here's the whole module...
Code:
Option Compare Database
Public Function GenerateBradyForm(strDocPath As String)
If IsNull(strDocPath) Or strDocPath = "" Then
Exit Function
End If
Dim cops As String
Dim dbs As Database
Dim objWord As Object
Dim PrintResponse
Set dbs = CurrentDb
'Get the cops
Set cops = getCops(CStr(Forms!Case!DName))
'create reference to Word Object
Set objWord = CreateObject("Word.Application")
'Word Object is created - now let's fill it with data.
With objWord
.Visible = True
.Documents.Open (strDocPath)
'move to each bookmark, and insert correct text.
.ActiveDocument.Bookmarks("defendant").Select
.Selection.Text = (CStr(Forms!Case!DName))
.ActiveDocument.Bookmarks("casenumber").Select
.Selection.Text = (CStr(Forms!Case!CaseNo))
.ActiveDocument.Bookmarks("police").Select
.Selection.Text = (cops)
' .ActiveDocument.Bookmarks("<bookmark name>".Select
' .Selection.Text=(Cstr(Forms!<form name>!<field
' name>))
' .ActiveDocument.Bookmarks.Add Name:=<bookmark name>,
' Range = Selection.Range
'** continue the ActiveDocument and Selection statements for each bookmark that you have on the Word Document **
End With
'release all objects
'Set objWord = Nothing
Set dbs = Nothing
End Function
Public Function GenerateSubpoenaForm(strDocPath As String)
If IsNull(strDocPath) Or strDocPath = "" Then
Exit Function
End If
Dim cops As String
Dim dbs As Database
Dim objWord As Object
Dim PrintResponse
Set dbs = CurrentDb
'Get the cops
Set cops = getCops(CStr(Forms!Case!DName))
'create reference to Word Object
Set objWord = CreateObject("Word.Application")
'Word Object is created - now let's fill it with data.
With objWord
.Visible = True
.Documents.Open (strDocPath)
'move to each bookmark, and insert correct text.
.ActiveDocument.Bookmarks("defendant").Select
.Selection.Text = (CStr(Forms!Case!DName))
.ActiveDocument.Bookmarks("casenumber").Select
.Selection.Text = (CStr(Forms!Case!CaseNo))
.ActiveDocument.Bookmarks("charges").Select
.Selection.Text = (CStr(Forms!Case!Charges))
.ActiveDocument.Bookmarks("incidentdate").Select
.Selection.Text = (CStr(Forms!Case!IncDate))
.ActiveDocument.Bookmarks("incidentnumber").Select
.Selection.Text = (CStr(Forms!Case!IncidentNo))
.ActiveDocument.Bookmarks("jurytrial").Select
.Selection.Text = (CStr(Forms!Case!JT))
' .ActiveDocument.Bookmarks("<bookmark name>".Select
' .Selection.Text=(Cstr(Forms!<form name>!<field
' name>))
' .ActiveDocument.Bookmarks.Add Name:=<bookmark name>,
' Range = Selection.Range
'** continue the ActiveDocument and Selection statements for each bookmark that you have on the Word Document **
End With
'release all objects
'Set objWord = Nothing
Set dbs = Nothing
End Function
Public Function getCops(DName As String)
'set up a connection to the database
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
'set up two recordsets
Dim myRS As New ADODB.Recordset
myRS.ActiveConnection = cnn1
Dim myRS2 As New ADODB.Recordset
myRS2.ActiveConnection = cnn1
'create a query based on the same table to which the subform is bound
'use data from the main form to filter the related data
Dim mySQL As String
mySQL = "SELECT [star] FROM PoliceAssignments WHERE DName=" & DName
'set up a variable to hold the data you need
Dim holdmydata As String
'open a recordset based on the query just created
myRS.Open mySQL
'check to make sure the recordset has records in it
If myRS.BOF And myRS.EOF Then
'MsgBox "No related data"
Exit Sub
Else
'if data exists then loop through it and store the info from each related record in a variable
Do Until myRS.EOF
'new query to get officer name from star number
mySQL = "SELECT [Officer] FROM Police WHERE Star=" & myRS![star]
myRS2.Open mySQL
holdmydata = holdmydata & myRS2![Officer] & ": " & myRS![star] & vbCr
myRS.MoveNext
myRS2.MoveNext
Loop
myRS.Close
myRS2.Close
End If
getCops = holdmydata
End Function
Lemme know whatcha think / any questions... I really appreciate the help, this is a side project I'm working on at this unpaid internship I'm doing.