HELP! Access 2007: Weird behavior in an export word function (1 Viewer)

Bevos

New member
Local time
Today, 14:57
Joined
Apr 20, 2011
Messages
7
Hello, I have a form called MakeWordTable which exports a landscape style table to MS Word based on the selections from a listbox that is populated when the form opens. A user should be able to select as many fields as he or she wants and then export them, but I am getting two errors. If the user selects more than 6 fields I get the error: "run-time error '3601': Too few parameters expected 2." and it points me to this snippet of code in the debugger: "Set rs = CurrentDb.OpenRecordset("select " & Mid(fieldlist, 3) & " from tblStudyDescription")"
The next error "run-time error '13': Type mismatch" occurs after selecting a 'multi-select listbox' field (entries are in the form of a comma separated list) and it points me to this snippet of code in the debugger:"t.insertafter rs.Fields(X).value & Chr(9)"

Any help would be greatly appreciated as I have been working on how to fix this for days with no improvement.

Bevo S.

Code:
Option Compare Database

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
Next
If fieldlist = "" Then
  MsgBox "You must select at least one field"
  Exit Sub
End If


Set rs = CurrentDb.OpenRecordset("select " & Mid(fieldlist, 3) & " from tblStudyDescription")

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
Next
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)
    Next
    t.insertafter rs.Fields(rs.Fields.Count - 1).value & Chr(13) & Chr(10)
    rs.MoveNext
Loop

    t.WholeStory
    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

Private Sub Form_Load()

Set rs = CurrentDb.OpenRecordset("select * from tblStudyDescription")

For Each Field In rs.Fields
  lstFields.AddItem Field.Name
Next

End Sub
 

DCrake

Remembered
Local time
Today, 21:57
Joined
Jun 8, 2005
Messages
8,632
First of all make sure that there are no spaces in your field names.
Secondly you need to look at what fieldlist is returning

Why are you using mid(..,3)?
 

Bevos

New member
Local time
Today, 14:57
Joined
Apr 20, 2011
Messages
7
Thanks for the great suggestion. I removed all the spaces in field names before I saw your post and it does really help. But I still have the error with the fieldlist. Do you have any online resource you can point me to for exporting as word? I've tried google, but couldn't find anything that solved the problem. If you could also point me in the right direction that would be great :).

Thanks so much.

Bevo S.
 

Users who are viewing this thread

Top Bottom