Thanks June7,
I think that I'll have to spend more time with SQL. With the SQL you provided, I now get "3131 Syntax error in FROM clause." I tried a few things to fix this, but none were successful. Here is the current code now:
Code:
Function Transposer(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("SELECT SequenceNo, SamplePrefix, SampleID, SampleSuffix, ResultDate, Result " _
& "FROM tblTestTypes " _
& "INNER JOIN tblSampleLogIn INNER JOIN tblSampleData " _
& "ON tblSampleLogIn.[AccessionNo] = tblSampleData.[AccessionNoFK] " _
& "INNER JOIN tblResultsData " _
& "ON tblSampleData.[SampleDataID] = tblResultsData.[SampleDataFK] " _
& "ON tblTestTypes.[TestTypesID] = tblResultsData.[TestTypeFK] " _
& "WHERE tblTestTypes.TestType= '" & [Forms]![Main].[NavigationSubform].[Form].[cboTestType] & "'" _
& "AND tblSampleData.AccessionNoFK=" & [Forms]![Main].[NavigationSubform].[Form].[cboLongLabID] & ";", dbOpenSnapshot)
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 Else
MsgBox CStr(Err) & " " & Err.Description
End Select
Exit Function
End Function
TestType = Text
AccessionNoFK = Number
Why searching on TestType and not TestTypesID? Is cboTestType a multi-column combobox with columns for TestTypeID and TestType fields?
cboTestType is a single-column combobox with only one column for TestType field. I set it up to search on TestType, as opposed to TestTypeID, as it will be simpler for the user to search by the text [TestType], rather than searching by just a number in [TestTypeID]. Is there a more logical approach to this?
This code is executed by the main form?
Not yet. I am hoping to build this Transposer function first, then call the function from the main form (and most likely from reports later on).
Lab DB Design
Windows XP 
Access 2010 32bit
Reply With Quote
