June7,
I copied and pasted the code below to transpose a table:
Code:
Function Transposer(strSource As String, strTarget As String)
Dim db As DAO.Database
Dim tdfNewDef As DAO.TableDef
Dim fldNewField As DAO.Field
Dim rstSource As DAO.Recordset, rstTarget As DAO.Recordset
Dim i As Integer, j As Integer
On Error GoTo Transposer_Err
Set db = CurrentDb()
Set rstSource = db.OpenRecordset(strSource)
rstSource.MoveLast
' Create a new table to hold the transposed data.
' Create a field for each record in the original table.
Set tdfNewDef = db.CreateTableDef(strTarget)
For i = 0 To rstSource.RecordCount
Set fldNewField = tdfNewDef.CreateField(CStr(i + 1), dbText)
tdfNewDef.Fields.Append fldNewField
Next i
db.TableDefs.Append tdfNewDef
' Open the new table and fill the first field with
' field names from the original table.
Set rstTarget = db.OpenRecordset(strTarget)
For i = 0 To rstSource.Fields.Count - 1
With rstTarget
.AddNew
.Fields(0) = rstSource.Fields(i).Name
.Update
End With
Next i
rstSource.MoveFirst
rstTarget.MoveFirst
' Fill each column of the new table
' with a record from the original table.
For j = 0 To rstSource.Fields.Count - 1
' Begin with the second field, because the first field
' already contains the field names.
For i = 1 To rstTarget.Fields.Count - 1
With rstTarget
.Edit
.Fields(i) = rstSource.Fields(j)
rstSource.MoveNext
.Update
End With
Next i
rstSource.MoveFirst
rstTarget.MoveNext
Next j
db.Close
Exit Function
Transposer_Err:
Select Case Err
Case 3010
MsgBox "The table " & strTarget & " already exists."
Case 3078
MsgBox "The table " & strSource & " doesn't exist."
Case Else
MsgBox CStr(Err) & " " & Err.Description
End Select
Exit Function
End Function
I tested the function by typing this into the immediate window:
Code:
?Transposer("tblSampleLogIn", "tblTempResults")
Where [tblSampleLogIn] is a current table, and [tblTempResults] is a new table that is created and transposed from the first table. (it works)
However, most of the data needed is contained in multiple tables. So, I tried to use a query in the argument, like so:
Code:
?Transposer("qrySampleResults", "tblTempResults")
When this is run in the immediate window, the error "3061 Too few parameters. Expected 2". I suspect that this is because the query has two parameters that use criteria from the form (see the first pic in my last post for the query and form). I even tried running the transposer with the query as the first argument with the form opened, and the query source criteria controls filled with data. The same error occurs.
Before I start tweaking the VBA Transposer code more, I would like to try to get the proper data in the recordset first. How can I get it to make the recordset based upon the query results?
Thanks again
Lab DB Design
Windows 7 32bit 
Access 2010 32bit
Reply With Quote
