Public Sub NormalizeIt()
Dim db As Database
Dim qd As QueryDef
Dim rs As Recordset
Dim pr As Parameter
On Error GoTo EH
'delete existing data from temp table
Set db = CurrentDb
db.Execute "DELETE * FROM tblNormalize;", dbFailOnError + dbSeeChanges
'get a recordset of the column headers
Set qd = db.QueryDefs("qryCrossEmployee")
qd.Parameters("[TempVars]![tmpTeamID]") = TempVars("tmpTeamID").Value
qd.Parameters("[TempVars]![tmpYear]") = TempVars("tmpYear").Value
qd.Parameters("[TempVars]![tmpFundID]") = TempVars("tmpFundID").Value
For Each pr In qd.Parameters
pr.Value = Eval(pr.Name)
Next pr
Set rs = qd.OpenRecordset
rs.MoveFirst
Do While rs.EOF = False
' "un" crosstab the data from tblCrosstab into tblNormalize
db.Execute "INSERT INTO tblNormalize ( Investment, ID, DealerPct )" & Chr(10) & _
"SELECT InvestName, " & rs.Fields("ID") & ", [" & rs.Fields("ID") & "]" & Chr(10) & _
"FROM tblCrosstab;", dbFailOnError + dbSeeChanges
rs.MoveNext
Loop
rs.Close
qd.Close
Set rs = Nothing
'update the original normalized dataset based on edited dataset
Set qd = db.QueryDefs("qryUpRenormalize")
qd.Parameters("[TempVars]![tmpEmployeeID]") = TempVars("tmpEmployeeID").Value
For Each pr In qd.Parameters
pr.Value = Eval(pr.Name)
Next pr
qd.Execute dbFailOnError + dbSeeChanges
EX:
Set rs = Nothing
Set qd = Nothing
Set db = Nothing
Exit Sub
EH:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbOKOnly, "Error"
Resume EX
End Sub