Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 927
  • Last Modified:

VBA code to transpose and print current record in a form

Hi guys,

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:
Sub Transpose1()
 
    ' requires reference to Microsoft DAO
 
    Dim rs As DAO.Recordset
    Dim Counter As Long
 
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM [StagingTable1]")
 
    With DoCmd
        .SetWarnings False
        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]"
        Next
        .SetWarnings True
    End With
 
    rs.Close
    Set rs = Nothing
 
    MsgBox "Done"
 
End Sub

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?
Thank you
0
marian68
Asked:
marian68
  • 15
  • 15
1 Solution
 
mbizupCommented:
You can change your code like this to use the current record:

Sub Transpose1(lngID as long)
    Dim rs As DAO.Recordset
    Dim Counter As Long
 
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM [StagingTable1] WHERE ID = " & lngID)
 
    With DoCmd
        .SetWarnings False
        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]"
        Next
        .SetWarnings True
    End With
 
    rs.Close
    Set rs = Nothing
 
    MsgBox "Done"
 
End Sub

Open in new window


You would change the calling statement to this, assuming that the sub is called from the form in question:

Transpose1 Me.YourIDField 

Open in new window



And you can add criteria what opening your report to limit it to the current record:

Docmd.OpenReport "YourReportName", WhereCondition := "YourIDField = " & Me.YourIDField 

Open in new window

0
 
marian68Author Commented:
Thank  you mbizup for your prompt reply,

I replaced the code for transposition with the code you gave me.
I put on my button click event the code "Transpose1 Me.YourIDField  ".
Now can you please tell me where I have to add the criteria:
"Docmd.OpenReport "YourReportName", WhereCondition := "YourIDField = " & Me.YourIDField "?

Thank you
0
 
mbizupCommented:
The criteria is in that statement already, in the WhereCondition (this is an example of how to restrict a report to a given record).  The WhereCondition restricts the report to the ID field as seen on the form.

I'm assuming that your report has a recordsource that is either defined by a table or by a query along these lines:

SELECT YourIDField, anotherfield, yetanotherfield FROM YourTable


The VBA to open that report from your form would be:

Docmd.OpenReport "YourReportName", WhereCondition := "YourIDField = " & Me.YourIDField

You simply need to change this to include the actual names of your report and ID field.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
marian68Author Commented:
Thank you mbizup,

Let's say my "YourIDField" is named "APP" and "YourReportName" is named "Report1".
If I understood well I have to add, after calling transposition  VBA code, on the same button click_event the statmement like as follows:

Private Sub TRS_PRINT_APP_Click()
Transpose1 Me.APP
Docmd.OpenReport Report1, Where  APP = &Me.APP
End Sub

Let me know if it is correct or if it is not show me the errror?
Thank you
0
 
mbizupCommented:
Very close...  Here it is corrected:

Private Sub TRS_PRINT_APP_Click()
Transpose1 Me.APP
Docmd.OpenReport Report1, WhereCondition := "APP = "  &  Me.APP
End Sub

Open in new window


Also, I'm assuming that App is numeric.  The syntax would be a bit different if it is text (let me know if this is the case).
0
 
marian68Author Commented:
App is not numeric, it is text.

Thanks a lot
0
 
mbizupCommented:
For text, your code would change as follows -

Private Sub TRS_PRINT_APP_Click()
       Transpose1 Me.APP
      Docmd.OpenReport Report1, WhereCondition := "APP = '"  &  Me.APP & "'"
End Sub

Open in new window


and your sub:

Sub Transpose1(strApp as String)
    Dim rs As DAO.Recordset
    Dim Counter As Long
 
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM [StagingTable1] WHERE APP  = '" & strApp  & "'")
 
    With DoCmd
        .SetWarnings False
        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]"
        Next
        .SetWarnings True
    End With
 
    rs.Close
    Set rs = Nothing
 
    MsgBox "Done"
 
End Sub

Open in new window

0
 
marian68Author Commented:
When I clicked the button I received the message :
Compile error:
Method or data member not found
and APP from the below statement on second line  was highlighted
"Private Sub TRS_PRINT_APP_Click()
Transpose1 Me.APP"

Thank you for you patience
0
 
mbizupCommented:
Ok... what is the name of the App field as it is seen on your form?
0
 
marian68Author Commented:
The name of the App field on my form is "APPLICATIO" , it is text and the record source of my form is a table called "TMREVIEW1 where "APPLICATIO " is the primary key.
Using the table "TMREVIEW1" I built the query "StagingTable1" and I changed the name of the field in question from "APPLICATIO" to "APP".
I use query "StagingTable1" in the transposition code to transpose the data in the table "INCEPTIONFROM". After transposition the field in question is not primary key in the table "INCEPTIONFROM". I use the table "INCEPTIONFROM" to get my report "Report1"

Thank you again for your patience
0
 
mbizupCommented:
Ok - your Transpose function needs to be called using the name of the field as it appears on your form.

From your description:

Transpose1 Me.APPLICATIO

Open in new window



Next, when you open the report, you need to specify which field (in your reports recordsource) should match  APPLICATIO from your form:


Docmd.OpenReport Report1, WhereCondition := "APPFieldAsOnYourReport = '"  &  Me.APPLICATIO & "'"

Open in new window



In my OpenReport statement, replace "APPFieldAsOnYourReport" with the correct field name from your report.
0
 
marian68Author Commented:
I received the message:
"Run-time error 2497:
The action or method requires a report name argument"
and the third line of the below code was highlighted.
(My report is called "File_Report").

"Private Sub TRS_PRINT_APP_Click()
Transpose1 Me.APPLICATIO
DoCmd.OpenReport File_Report, WhereCondition:="APP = '" & Me.APPLICATIO & "'"
End Sub"

Thank you
0
 
mbizupCommented:
Put the report name in quotes:

DoCmd.OpenReport "File_Report", WhereCondition:="APP = '" & Me.APPLICATIO & "'"
0
 
marian68Author Commented:
Thank you a lot for your patience,
Now I have to go - family tasks.
Tomorrow morning I will tell you if it works - anyway I give you the points now.
Thank you again and have a nice evening.

Marian68
0
 
marian68Author Commented:
Thanks a lot.
0
 
mbizupCommented:
Glad to help out...

It should be okay - just a matter of getting the right names for your key fields.
0
 
marian68Author Commented:
Hi mbizup,

I tested the code and there is a problem: after I enter all the data in the form record I push the button and the report is printed empty. I saved the current record but the same result. The only way to have the report printed with the entered data is to go to a new record and to come back to the record just entered.
Another problem I notice is that if I change the place of the database from the desktop to share drive it doesn't work any more.
Can you please how to include all this new topic in a new question related to the yesterday's question.
Thank you
0
 
mbizupCommented:
Can you upload a sample copy of your database?
0
 
marian68Author Commented:
Hi mbizup,

Thank you for your reply.
I have already asked another question concerning the save topic.
Anyway I have a question for you.
The code for transposition you have sent me, should transpose all records from the table "StagingTable1" into "INCEPTIONFROM" or only the current form record?
I have this question because as I noticed that the code transposes all records (it takes a lot of time when the number of records are important and because the table "StagingTable1" has more than 60 fields) and for me is important to have transposed only the current record as I mentioned in my question.
Can you please help me?
Thanks
0
 
mbizupCommented:
See if this makes a difference:

Sub Transpose1(lngID as long)
    Dim rs As DAO.Recordset
    Dim Counter As Long
 
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM [StagingTable1] WHERE ID = " & lngID)
 
    With DoCmd
        .SetWarnings False
        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]  WHERE ID = " & lngID
        Next
        .SetWarnings True
    End With
 
    rs.Close
    Set rs = Nothing
 
    MsgBox "Done"
 
End Sub

Open in new window

0
 
marian68Author Commented:
Hi mbizup,

I replaced with the last code and I received the message:

"Run-time error 3061:
Too few parameters. Expected 1."
and the following line of the code was highlighted:
"Set rs = CurrentDb.OpenRecordset("SELECT * FROM [StagingTable1] WHERE ID = " & lngID)"

Thank you,
0
 
mbizupCommented:
Opps - modified the wrong block of code.

Sub Transpose1(strApp as String)
    Dim rs As DAO.Recordset
    Dim Counter As Long
 
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM [StagingTable1] WHERE APP  = '" & strApp  & "'")
 
    With DoCmd
        .SetWarnings False
        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] WHERE APP  = '" & strApp  & "'")
        Next
        .SetWarnings True
    End With
 
    rs.Close
    Set rs = Nothing
 
    MsgBox "Done"
 
End Sub

Open in new window

0
 
marian68Author Commented:
I received the message: Syntax error
and the following chunk of code is red:
"            .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] WHERE APP  = '" & strApp  & "'")"
Thank you
0
 
mbizupCommented:
Sub Transpose1(strApp As String)
    Dim rs As DAO.Recordset
    Dim Counter As Long
 
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM [StagingTable1] WHERE APP  = '" & strApp & "'")
 
    With DoCmd
        .SetWarnings False
        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] WHERE APP  = '" & strApp & "'"
        Next
        .SetWarnings True
    End With
 
    rs.Close
    Set rs = Nothing
 
    MsgBox "Done"
 
End Sub

Open in new window

0
 
marian68Author Commented:
Thank you. It worked.
I have another opened question related to this. Can you help me?
I have a button and behind it the following code:
"Private Sub TRS_VIEW_APP_Click()
Transpose1 Me.APPLICATIO
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "File_Report", acViewPreview, WhereCondition:="APP = '" & Me.APPLICATIO & "'"
End Sub"
When I push 1st time the report is printed empty.
When I push 2nd time the report is printed properly.
I think the query(which include also the transposed table) on which the report is based doesn't run 1st time.

Thank you
0
 
mbizupCommented:
I think your code is in the wrong order.

Try running the Transpose1 code AFTER saving the record:

Private Sub TRS_VIEW_APP_Click()

DoCmd.RunCommand acCmdSaveRecord
Transpose1 Me.APPLICATIO

DoCmd.OpenReport "File_Report", acViewPreview, WhereCondition:="APP = '" & Me.APPLICATIO & "'"
End Sub

Open in new window

0
 
marian68Author Commented:
Thank you. You were right.
As I said I have an opened question with the same content.
Can you please copy the last answer in my opened question so that I can give you the points
or tell me how to give you the points?
Thanks a lot,
0
 
mbizupCommented:
Done - I posted in your follow-up question
0
 
marian68Author Commented:
I have already given the points.
Thanks again and have a nice day.
0
 
mbizupCommented:
You too :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 15
  • 15
Tackle projects and never again get stuck behind a technical roadblock.
Join Now