Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Access 2007: Export to word VBA problem when using captions in listbox

Posted on 2011-04-26
Medium Priority
Last Modified: 2012-06-21
Hello, I have a database that users here have helped me improve on several occasions.  One of these improvements was to change a listbox that exported the selected fields to a word document to show the field captions rather than the field names (ie. FName to 'First Name').  However, I'm not sure how the code for the export query needs to be changed to reflect this.  I'm going to post the code for this item as well as the database.  If you choose to download the database, the form that I am talking about here is 'makewordtable'.  
When I run the code found in the attachment, I get an error "Run-time error '424' Object required" and the points to the line: For X = 0 To rs.Fields.Count - 2.
The code was working before the field list was changed to a caption list.  
If any experts know how to work with this change I very much need the help.

Thanks so much, Bevo S.
Option Compare Database

Private Sub Command0_Click()
BuildValueList ("qryAll")
End Sub

Public Function BuildValueList(TableName As String)
On Error GoTo myerror
Dim FinalString As String
Dim db As Database
Dim rs As Recordset
Dim myfield As Field
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from " & TableName & " where 1 = 2;", dbOpenDynaset, dbSeeChanges)

For Each myfield In rs.Fields
    FinalString = FinalString & Nz(myfield.Properties("Caption"), "no caption") & ";"
Next myfield

Me.lstFields.RowSource = FinalString

Exit Function

If Err.Number = 3270 Then 'no existing caption
    FinalString = FinalString & myfield.Name & ";" & "no caption" & ";"
    Resume Next
End If

End Function

Private Sub Command2_Click()
Dim fieldlist As String
Dim nc As Long
Dim nr As Long

For X = 0 To lstFields.ListCount
  If lstFields.Selected(X) Then
    fieldlist = fieldlist & ", " & lstFields.Column(0, X)
  End If
If fieldlist = "" Then
  MsgBox "You must select at least one field"
  Exit Sub
End If

Set objword = CreateObject("Word.Application")
objword.Visible = True

    Set d = objword.Documents.Add(DocumentType:=0)
    Set t = d.content
     t.PageSetup.Orientation = 1
nc = 1
For X = 0 To rs.Fields.Count - 2
  t.insertafter rs.Fields(X).Name & Chr(9)
  nc = nc + 1
t.insertafter rs.Fields(rs.Fields.Count - 1).Name & Chr(13) & Chr(10)

nr = 1
Do Until rs.EOF
    nr = nr + 1
    For X = 0 To rs.Fields.Count - 2
      t.insertafter rs.Fields(X).value & Chr(9)
    t.insertafter rs.Fields(rs.Fields.Count - 1).value & Chr(13) & Chr(10)

    t.ConvertToTable Separator:=1, NumColumns:=nc, NumRows:=nr, AutoFitBehavior:=0
    With t.Tables(1)
        .Style = "Table Grid"
        .ApplyStyleHeadingRows = True
        .ApplyStyleLastRow = False
        .ApplyStyleFirstColumn = True
        .ApplyStyleLastColumn = False
    End With

End Sub

Open in new window

Question by:Bevos
  • 5
  • 4
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35472023
what exactly are you exporting to word? how will the word doc look like after the export?

upload  a sample .doc

Author Comment

ID: 35474810
Hi Capricorn, here is a sample.  It is a simple word document with a landscape layout with selected field names at the top and then the contents of those fields (this was made by hand and not the code above).

LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35474863
and you want the values coming from query "qryAll" ?

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35474896
also, you want to export to word doc, only the items selected from the listbox, using the captions as the field names, is this correct?

Author Comment

ID: 35475038
Yes, the query qryAll has all of the required fields which will be included in the listbox.  If the export can actually use the captions as the field names that would be wonderful (I didn't know this was possible, I thought I would be required to manually change these after the fact).

LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35475061
do you want to export ONLY the ones selected from the listbox?

Author Comment

ID: 35475771
Yes, so the listbox has many fields and I want the user to be able to dynamically create the word table by making a few choices in the listbox.  Only those selected listbox choices should be exported.

LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 2000 total points
ID: 35476626

click the build the list button
select items from listbox
click export to word

Author Comment

ID: 35476726
You're so great Capricorn, thank you!

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question