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