VBA code to transpose and print current record in a form
Posted on 2013-01-14
I need some help.
All my reports are based on the transposition of the records entered by users using a form. At regular time intervals I transpose the records in the table "INCPEPTIONFROM" and make more and less complex reports.
The code I use for transposition is:
' requires reference to Microsoft DAO
Dim rs As DAO.Recordset
Dim Counter As Long
Set rs = CurrentDb.OpenRecordset("SELECT * FROM [StagingTable1]")
For Counter = 5 To rs.Fields.Count
.RunSQL "INSERT INTO [INCEPTIONFROM] ([DATE],[REV],[MINER],[APP], [SPECIALITY], [GRADE]) " & _
"SELECT [DATE],[REV],[MINER],[APP], '" & rs.Fields(Counter - 1).Name & "', " & _ "[" & rs.Fields(Counter - 1).Name & "] " & _ "FROM [StagingTable1]"
Set rs = Nothing
Now I have to build a button on the form to print a report(REPORT1) for the current record. The record source for this report is a query composed of several tables including the table resulted from transposition.
The first idea came to me was to put behid this button all the code used for transpostion and to add the code for printing the report corresponding to the current record. The result of this idea is not very good because it takes a lot of time to transpose all records again and again. My VBA code knowledge are poor but I think the best solution would by to transpose only the current record in the form and print the report for this record.
Can anyone give me the code for transposing only the curent form record and print the report correponding to this record?