Populate a Word Document from a Listbox?

[magster06]

Hello all,

I am trying to populate a word document from a listbox selection.

The word docx populates just fine with a form (Rowsource is a query) and the code below in the click event of the button.

What I would like to happen, is when the user selects a record from the listbox and then clicks the "Generate word document" button this selection will populate my word doc.

The code was found doing a search (maybe microsoft, not sure):

Option Compare Database
Option Explicit
Const DOC_PATH As String = "C:\Users\arrudad\Documents\AutoForms\"
Const DOC_NAME As String = _
    "e60 Day Procedure-autov2.docm"

Private Sub cmdPrintForm_Click()
Dim appWord As Word.Application
Dim doc As Word.Document
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strCriteria As String
On Error Resume Next
Set appWord = GetObject(, "Word.application")
If Err = 429 Then
    Set appWord = New Word.Application
    Err = 0
End If
With appWord
    Set doc = .Documents(DOC_NAME)
    If Err = 0 Then
'        If MsgBox("Do you want to save the current document " _
'            & "before updating the data?", vbYesNo) = vbYes Then
'                .Dialogs(wdDialogFileSaveAs).Show
'        End If
    doc.Close False
    End If
    On Error GoTo ErrorHandler
    
    Set doc = .Documents.Open(DOC_PATH & DOC_NAME, , True)
    
    
    If Me.lstCaseNumbers.ItemsSelected.Count > 0 Then
         strCriteria = "[CaseNumber]='" & Me![lstCaseNumbers].Column(0) & "'"
        Set rst = CurrentDb.OpenRecordset("Q60DayForm")
        If Not rst.EOF Then
            strCriteria = Nz(rst.Fields(0).Value)
            rst.Close
        End If
    End If
   
    With doc
        .FormFields("fldFirstName").Result = Nz(Me![First Name] & "" & "," & " " & "" & [Last Name])
        .FormFields("fldAddress").Result = Nz(Me![Street Address])
        .FormFields("fldCity").Result = Nz(Me![City] & "," & " " & [State] & "," & " " & [Zip Code])
        .FormFields("fldFirstName1").Result = Nz(Me![First Name] & "" & "," & " " & "" & [Last Name])
        .FormFields("fldCallInDate").Result = Nz(Me![Call_In Date])
        .FormFields("fld60DayDate").Result = Nz(Me![60DayDate])
        .FormFields("fldCaseNumber").Result = Nz(Me!CaseNumber)
        .FormFields("fldIncidentDate").Result = Nz(Me![Incident Date])
        .FormFields("fldInvestigator").Result = Nz(Me![Case Investigator])
    End With
    .Visible = True
    .Activate
End With
Set rst = Nothing
Set doc = Nothing
Set appWord = Nothing
Exit Sub
ErrorHandler:
MsgBox Err & Err.Description
'End If
End Sub

I tried to include my listbox, but I get the error code 3061-too few parameters.

When I remove the listbox code, then the form opens blank

Any help would be appreciated.

 

[magster06]

Ok, now it is working somewhat.

I have the code to populate my form (which works, yeah) from another site:

Private Sub cmdPrintForm_Click()
 
    Dim appWord As Word.Application
    Dim doc As Word.Document
 
    'Avoid error 429, when Word isn't open.
    On Error Resume Next
    Err.Clear
 
    'Set appWord object variable to running instance of Word.
    Set appWord = GetObject(, "Word.Application")
    If Err.Number <> 0 Then
        'If Word isn't open, create a new instance of Word.
        Set appWord = New Word.Application
    End If
 
    Set doc = appWord.Documents.Open("C:\Users\arrudad\Documents\AutoForms\Monday.docx", , True)
        With doc
            .FormFields("fldFirstName").Result = Nz(Me![First Name] & "" & "," & " " & "" & [Last Name])
            .FormFields("fldAddress").Result = Nz(Me![Street Address])
            .FormFields("fldCity").Result = Nz(Me![City] & "," & " " & [State] & "," & " " & [Zip Code])
            .FormFields("fldFirstName1").Result = Nz(Me![First Name] & "" & "," & " " & "" & [Last Name])
            .FormFields("fldCallInDate").Result = Nz(Me![Call_In Date])
            .FormFields("fld60DayDate").Result = Nz(Me![60DayDate])
            .FormFields("fldCaseNumber").Result = Nz(Me!CaseNumber)
            .FormFields("fldIncidentDate").Result = Nz(Me![Incident Date])
            .FormFields("fldInvestigator").Result = Nz(Me![Case Investigator])
 
            .Visible = True
            .Activate
        End With
    Set doc = Nothing
    Set appWord = Nothing
    Exit Sub
errHandler:
MsgBox Err.Number & ": " & Err.Description
End Sub

This is the code in my afterupdate event of the combo box:

Private Sub cboCaseNumbers_AfterUpdate()
    Dim rst As DAO.Recordset
    If Len(Me.cboCaseNumbers & vbNullString) <> 0 Then
        Set rst = Me.RecordsetClone
        With rst
            .FindFirst "CaseNumber = " & Chr(34) & Me.cboCaseNumbers & Chr(34)
            Me.Bookmark = .Bookmark
        End With
        rst.Close
        Set rst = Nothing
    End If
End Sub

Now here is my issues:

1. I have to click my printform button twice to open the word document

2. I have multiple casenumbers with different people in my combo box, for example:

2013-005 Scarpetti
2013-005 Nicholas

When I go to the first selection (2013-005) then Access form is populated with the record for that number. When I go to the second 2013-005, then it still uses the first record for Scarpetti and not Nicholas.

Any ideas on my two issues?

Should I base my search on the last name instead?

 

[magster06]

Ok, new development:

I changed the combo box to populate with [LastName], [CaseNumber] and now it populates the Access form as it should.

I would still like to know why it did not work the other way around, Please.

I still have the issue of having to double click the print button.

 

[magster06]

Anyone have any ideas why I have to click my "Generate Form" button twice to make the Word form open up?

Also, how do I make the Word doc be on top of my access form?