Results 1 to 6 of 6
  1. #1
    matto is offline Novice
    Windows 7 Access 2003
    Join Date
    Jul 2010
    Posts
    9

    Trying to get data from a subform into a word document...

    Ha, this has been fun. I just started using access a few weeks ago, and I'm kind of stuck with this...

    Anyway, here is the completely functional code I've got so far:



    Code:
    Public Function GenerateBradyForm(strDocPath As String)
       If IsNull(strDocPath) Or strDocPath = "" Then
         Exit Function
       End If
    
       Dim dbs As Database
       Dim objWord As Object
       Dim PrintResponse
       Set dbs = CurrentDb
       
       '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("<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
    When I click a button, it opens up a word document, and inserts the appropriate data into the appropriate bookmarks.

    I've got a Case form with everything I need in it, or one of its subforms. What I want to do is traverse all the entries in one of its subforms, and add that information to the word document somehow. Specifically, there are two fields (name, number) in a subform and I want all the names and numbers in the word document.

    I haven't worked with VB much, I got most of the above code from some googling, so bear with me but I'll try to be a quick learner.

    Any ideas?

  2. #2
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    From where are you executing the code? From the main form you mention? You will need to pass a key value (caseID perhaps) to the module. From there you can open up a recordset based on the same table to which the subform is bound and use that to get the related data.

    Code:
    'set up a connection to the database
    Dim cnn1 As ADODB.Connection
    Set cnn1 = CurrentProject.Connection
    
    'set up a recordset
    Dim myRS As New ADODB.Recordset
    myRS.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 [name], [number] ... FROM tablename WHERE caseID=" & me.caseID
    
    '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 myRSC.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
            holdmydata = holdmydata & myRS![name] & ":   " & myRS![number] & vbCr 
            myRS.MoveNext
        Loop
        myRS.Close
    End If
    The variable holdmydata can then be used to populate your bookmark.

    As a side note, I think both the words "name" and "number" are reserved words in Access and should not be used as table or field names. I'm not sure when you referred to them whether they were your actual field names. If so, I would change them.

  3. #3
    matto is offline Novice
    Windows 7 Access 2003
    Join Date
    Jul 2010
    Posts
    9
    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.

  4. #4
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    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:...correct?
    yes, if you accummulate the information in a variable called people, you would use that to populate the bookmark.


    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?


    Actually its: me.controlname (not me.field)

    The "me." is a shortcut notation that refers to a control on the form from which the code was executed. You could use the full form reference:

    forms!formname!controlname

    but the me.controlname notation is more convenient when you are working on code executed from an event of the form or an event of a control on the form.

    I think an example might be better. You mention that your main form has something to do with a case and the subform has people related to the case. In table lingo, I would guess your tables look something like this:

    tblCase
    -pkCaseID primary key, autonumber
    -txtCase

    tblCasePeople
    -pkCasePeopleID primary key, autonumber
    -fkCaseID foreign key to tblCase
    -txtFName
    -txtLName

    I would then guess that your main form is based on tblCase and on that form your have textbox controls for each field. Your subform would then be based on tblCasePeople and be linked to the main form via the pkCaseID-fkCaseID relationship.

    If you are executing the code from a button on the main form, you need the value in the control that holds the pkCaseID in order to get the right people for that case (using the query I suggested). Let's assume that the textbox control bound to pkCaseID is actually called pkCaseID (the name of the the control). To reference that control in the code, you would use me.pkCaseID.

    If you plan on executing the code from somewhere else other than the form (My code was intended to be a private procedure of the form, not a public function.), you will have to use the full form reference for the control.

    Following with my code example, you would then call your function and pass the people to your function.

    Something like this (you will have to alter your function to accept the additional string)

    Code:
    'set up a connection to the database
    Dim cnn1 As ADODB.Connection
    Set cnn1 = CurrentProject.Connection
    
    'set up a recordset
    Dim myRS As New ADODB.Recordset
    myRS.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 [name], [number] ... FROM tablename WHERE caseID=" & me.caseID
    
    '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 myRSC.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
            holdmydata = holdmydata & myRS![name] & ":   " & myRS![number] & vbCr 
            myRS.MoveNext
        Loop
        myRS.Close
    End If
     
    Call GenerateBradyForm(pathvariable, people)

  5. #5
    matto is offline Novice
    Windows 7 Access 2003
    Join Date
    Jul 2010
    Posts
    9
    lol, functions in vba are weeeeird, man.

    But I've got it working. I decided to just have overly large functions for each form I'm generating, because calling the getCops function never works. It's better this way anyway, as each form needs the names formatted differently.

    THANK YOU GUYS SO MUCH. I've learned a lot in the past 24 hours.

  6. #6
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Glad to hear that you got it worked out! Good luck with your project.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 0
    Last Post: 06-14-2010, 07:19 AM
  2. Export single records to new Word Document
    By karmaimages in forum Import/Export Data
    Replies: 0
    Last Post: 11-19-2009, 03:37 PM
  3. Replies: 0
    Last Post: 01-18-2009, 09:18 PM
  4. Word document INTO Access form
    By jonathonhicks in forum Forms
    Replies: 0
    Last Post: 04-30-2007, 05:59 PM
  5. Replies: 1
    Last Post: 12-21-2005, 12:27 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums