Error 3265

[Betti via AccessMonster.com]

Good day,

I have been tryin to use the following code by David Schofield:
Function CrossToLinear(CrossTableName, NumRowFields, _
ColumnFieldName, _
ValueFieldName) As Boolean '

' Use this for reconverting from crosstab format table.
' Assumes that (row fields + column field) are unique in the output Table ,
ie
' the original crosstab had only one record contributing to each value.
' Assumes that the input table (crosstab table) is in the form:
' all the row heading fields first (NumRowfields of them)
' then the columns.
' Creates an output table in the format:
' all the row heading fields
' then the column heading field
' then the value field
' Example
'CrossTable has fields and values
'Person, Project, Jan, Feb, Mar, Apr ...
'Fred, Holidays, 3,0,0,5, ...
'result = CrossToLinear("CrossTable",2,"Month","Days")
'Output table has fields and values
'Person, Project, Month, Days
'Fred, Holidays, Jan, 3
'Fred, Holidays, Apr, 5
' ...

Dim CurrentDatabase As Database
Dim LinearTableName$
Dim LinearTableDef As TableDef ' for output table
Dim LinearTableSet As Recordset
Dim CrossTableDef As TableDef ' for input table
Dim CrossTableSet As Recordset
Dim myField As Field, myfield2 As Field
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim record_started As Boolean

On Error GoTo myerror
Set CurrentDatabase = DBEngine(0)(0)
LinearTableName$ = CrossTableName & "_Lin"

'Create new TableDef object. Delete first if already there
On Error Resume Next
CurrentDatabase.TableDefs.Delete LinearTableName
On Error GoTo myerror
Set LinearTableDef = CurrentDatabase.CreateTableDef(LinearTableName)

'Open input table def to get details of row and value field details:
Set CrossTableDef = CurrentDatabase.TableDefs(CrossTableName)
For i = 0 To NumRowFields - 1
' Create output Field object:
Set myfield2 = CrossTableDef.Fields(i) 'temp
Set myField = LinearTableDef.CreateField(myfield2.Name, _
myfield2.Type, myfield2.Size)
LinearTableDef.Fields.Append myField
Next i

'now add column heads field
Set myField = LinearTableDef.CreateField(ColumnFieldName, dbText, 297)
' 297 for example
LinearTableDef.Fields.Append myField

'now add value field
Set myfield2 = CrossTableDef.Fields(NumRowFields) 'temp
Set myField = LinearTableDef.CreateField(ValueFieldName, myfield2.Type,
myfield2.Size)
LinearTableDef.Fields.Append myField
CurrentDatabase.TableDefs.Append LinearTableDef

' Open output table
Set LinearTableSet = CurrentDatabase.OpenRecordset(LinearTableName, _
DB_OPEN_DYNASET)

' Open input table
Set CrossTableSet = CurrentDatabase.OpenRecordset(CrossTableName, _
DB_OPEN_DYNASET, DB_FORWARDONLY)

'if there are any records
If Not (CrossTableSet.BOF And CrossTableSet.EOF) Then
record_started = False
Do Until CrossTableSet.EOF 'for each record in crosstableset
For j = NumRowFields To CrossTableSet.Fields.Count - 1
'(for each crosstab column field)
Set myField = CrossTableSet.Fields(j)
If IsNull(myField.Value) Then
'ignore null entries
Else
'Add data to linear table
If Not record_started Then
' Prepare new record.
LinearTableSet.AddNew
record_started = True
For k = 0 To NumRowFields - 1
'copy all the row fields
LinearTableSet.Fields(k) = _
CrossTableSet.Fields(k)
Next k
End If
'now set the column field value
LinearTableSet.Fields(NumRowFields) = myField.Name
'now set the value field value
LinearTableSet.Fields(ValueFieldName) = myField.Value
End If
' Save record.
If record_started Then
LinearTableSet.Update
record_started = False
End If
Next j
CrossTableSet.MoveNext
Loop
End If

LinearTableSet.Close
CrossTableSet.Close
CrossToLinear = True
Exit Function

myerror:
MsgBox "Error in CrossToLinear, number " & Err.Number & ": " & Err.
Description
CrossToLinear = False
'may leave things open!
Exit Function

End Function


I am calling the function through a macro:
CrossToLinear ("Result_1000", 640, "QID", "Answer")

Where Result_1000 is the name of the CrossTab Table
640 is the number of rows
QID is the name for the new defined Column
Answer is the value for the QID

The Result_1000 table looks like the following:
RecID Q1 Q2 Q3....
1 0 1 1
2 1 0 0
3 1 0 1

and so on and I need it to look like:

RecID QID Answer
1 Q1 0
1 Q2 1
1 Q3 1
2 Q1 1
2 Q2 0
2 Q3 0
etc...
Thank you in advnace for all your help.