Solved

VBA code to transpose and print current record in a form

Posted on 2013-01-14
30
864 Views
Last Modified: 2013-01-17
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
Comment
Question by:marian68
  • 15
  • 15
30 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 38775745
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
 

Author Comment

by:marian68
ID: 38775903
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38775944
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
 

Author Comment

by:marian68
ID: 38776059
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38776082
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
 

Author Comment

by:marian68
ID: 38776153
App is not numeric, it is text.

Thanks a lot
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38776179
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
 

Author Comment

by:marian68
ID: 38776256
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38776283
Ok... what is the name of the App field as it is seen on your form?
0
 

Author Comment

by:marian68
ID: 38776390
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38776430
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
 

Author Comment

by:marian68
ID: 38776507
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
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 38776522
Put the report name in quotes:

DoCmd.OpenReport "File_Report", WhereCondition:="APP = '" & Me.APPLICATIO & "'"
0
 

Author Comment

by:marian68
ID: 38776553
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
 

Author Closing Comment

by:marian68
ID: 38776557
Thanks a lot.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 61

Expert Comment

by:mbizup
ID: 38776568
Glad to help out...

It should be okay - just a matter of getting the right names for your key fields.
0
 

Author Comment

by:marian68
ID: 38778824
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38781023
Can you upload a sample copy of your database?
0
 

Author Comment

by:marian68
ID: 38782580
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38785277
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
 

Author Comment

by:marian68
ID: 38787103
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38787124
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
 

Author Comment

by:marian68
ID: 38787139
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38787163
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
 

Author Comment

by:marian68
ID: 38787242
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38787326
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
 

Author Comment

by:marian68
ID: 38787428
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38787528
Done - I posted in your follow-up question
0
 

Author Comment

by:marian68
ID: 38787564
I have already given the points.
Thanks again and have a nice day.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38787572
You too :-)
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now