Solved

Error report - not finding label

Posted on 2014-01-14
11
168 Views
Last Modified: 2014-11-23
I am using this code to populate a report, but if there is not data for label2 or Label2 or Col2 or col3 I get an error.  Is there a way to fix this?


Private Sub Report_Open(Cancel As Integer)

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("qryfunds_Crosstab")
   
    Me.Label1.Caption = rs.Fields(2).Name
   Me.Label2.Caption = rs.Fields(3).Name
    Me.Label3.Caption = rs.Fields(4).Name
   
    Me.Col1.ControlSource = rs.Fields(2).Name
    Me.Col2.ControlSource = rs.Fields(3).Name
    Me.Col3.ControlSource = rs.Fields(4).Name
    rs.Close
End Sub
0
Comment
Question by:leezac
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39780879
try this

Private Sub Report_Open(Cancel As Integer)

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("qryfunds_Crosstab")
   
if rs.eof=false then
    Me.Label1.Caption = rs.Fields(2).Name
   Me.Label2.Caption = rs.Fields(3).Name
    Me.Label3.Caption = rs.Fields(4).Name
   
    Me.Col1.ControlSource = rs.Fields(2).Name
    Me.Col2.ControlSource = rs.Fields(3).Name
    Me.Col3.ControlSource = rs.Fields(4).Name
end if
    rs.Close
End Sub
0
 
LVL 25

Accepted Solution

by:
chaau earned 500 total points
ID: 39780895
Sorry, I've messed up. Please modify the procedure like this:
Private Sub Report_Open(Cancel As Integer)
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tb_temp_Crosstab")
    
    Me.Label1.Caption = rs.Fields(2).Name
    Me.Col1.ControlSource = rs.Fields(2).Name
    If rs.Fields.Count > 3 Then
        Me.Label2.Caption = rs.Fields(3).Name
        Me.Col2.ControlSource = rs.Fields(3).Name
    Else
        Me.Label2.Caption = ""
        Me.Col2.ControlSource = ""
    End If
    If rs.Fields.Count > 4 Then
        Me.Label3.Caption = rs.Fields(4).Name
        Me.Col3.ControlSource = rs.Fields(4).Name
    Else
        Me.Label3.Caption = ""
        Me.Col3.ControlSource = ""
    End If
    rs.Close
End Sub

Open in new window

0
 
LVL 58
ID: 39780927
Private Sub Report_Open(Cancel As Integer)

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("qryfunds_Crosstab")

   On Error Resume Next
   
    Me.Label1.Caption = rs.Fields(2).Name
   Me.Label2.Caption = rs.Fields(3).Name
    Me.Label3.Caption = rs.Fields(4).Name
   
    Me.Col1.ControlSource = rs.Fields(2).Name
    Me.Col2.ControlSource = rs.Fields(3).Name
    Me.Col3.ControlSource = rs.Fields(4).Name
    rs.Close

End Sub

Jim.
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:leezac
ID: 39786618
I have the label issue figured out and looks like On Error Resume Next will handle.  I have to be able to Sum the columns and when I use sum(col1) I get an error that Col1 is not recognized.
Database100.zip
0
 
LVL 25

Expert Comment

by:chaau
ID: 39786740
You need to create three textBoxes in the Report Footer band. Call them Col1Sum, Col2Sum, Col3Sum. The thing is, with the PIVOT queries the column names change every time the data changes. Therefore you will need to use VBA script. I have attached the database with the solution.

BTW, I insist on using my solution to check for the Fileds.Count (see above) instead of On Error Resume Next. Using the latter will "swallow" all the errors, even those that are unrelated to the column labelling, that could potentially result in "real" error not being reported.

Private Sub Report_Open(Cancel As Integer)
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tb_temp_Crosstab")
    
    Me.Label1.Caption = rs.Fields(2).Name
    Me.Col1.ControlSource = rs.Fields(2).Name
    Me.Col1Sum.ControlSource = "=Sum([" & rs.Fields(2).Name & "])"
    If rs.Fields.Count > 3 Then
        Me.Label2.Caption = rs.Fields(3).Name
        Me.Col2.ControlSource = rs.Fields(3).Name
        Me.Col2Sum.ControlSource = "=Sum([" & rs.Fields(3).Name & "])"
    Else
        Me.Label2.Caption = ""
        Me.Col2.ControlSource = ""
        Me.Col2Sum.ControlSource = ""
    End If
    If rs.Fields.Count > 4 Then
        Me.Label3.Caption = rs.Fields(4).Name
        Me.Col3.ControlSource = rs.Fields(4).Name
        Me.Col3Sum.ControlSource = "=Sum([" & rs.Fields(4).Name & "])"
    Else
        Me.Label3.Caption = ""
        Me.Col3.ControlSource = ""
        Me.Col3Sum.ControlSource = ""
    End If
    
    rs.Close
End Sub

Open in new window

Database100.mdb
0
 

Author Comment

by:leezac
ID: 39786769
chaau - I can use your code but I need to add more labels and columns  for up to 12 entries.  I am not sure I am understanding your logic
0
 

Author Comment

by:leezac
ID: 39786780
Nevermind this question
0
 

Author Comment

by:leezac
ID: 39786813
Wait before you do anything more - I need to repost.
0
 
LVL 58
ID: 39786833
There are any number of ways to handle this, but if you want to use chaau's code, you can make it more generic:

Private Sub Report_Open(Cancel As Integer)

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
   
    Dim intK as integer

    Set db = CurrentDb
    Set rs = db.OpenRecordset("tb_temp_Crosstab")

    For intK = 1 to rs.Fields.Count
         Me("Label" & intk).Caption = rs.Fields(intK+1).Name
         Me("Col" & intK).ControlSource = rs.Fields(intK+1).Name
         Me("Col" & intK & "Sum").ControlSource = "=Sum([" & rs.Fields(intK+1).Name & "])"
    Next intK    

    rs.Close
    Set rs = nothing

    set db = nothing

End Sub

  You might have to play with the loop a bit (intK, intK+1) depending on what you name the columns and where the fields land in the result set.

Jim.
0
 
LVL 25

Expert Comment

by:chaau
ID: 39786870
OK, let me explain. When you build a PIVOT query there are some columns that are always remain the same, and some columns that are made form the values from your original table. In your case, the column names highlighted yellow are constant, and the rest to the right are dynamicPIVOT queryWhen you build a report you will need to replace the control source for the columns. Report. Control SourceThe way I proposed to do this is to use VBA code. In the code I first open the query:
Set rs = db.OpenRecordset("tb_temp_Crosstab")

Open in new window

then replace the control Source for the Column with the actual field name from the query. This line:
Me.Col1.ControlSource = rs.Fields(2).Name

Open in new window

is actually takes the name of the column 3 from the query (it is Fields(2), but the actual column number is 3, because the field elements start with 0, like Fileds(0), Fields(1), etc)
I have then added a new column for the Report Footer that I have called Col1Sum. Normally, you would written in the ControlSource an expression "=Sum([Column1])", where Column1 is a column from your query. Here you can't do this. The Column name is dynamic. Therefore I use VBA for that as well:
Me.Col1Sum.ControlSource = "=Sum([" & rs.Fields(2).Name & "])"

Open in new window

I think this explains how I created the dynamic columns.

Now, let me explain this chunk of code:
If rs.Fields.Count > 3 Then
        Me.Label2.Caption = rs.Fields(3).Name
        Me.Col2.ControlSource = rs.Fields(3).Name
        Me.Col2Sum.ControlSource = "=Sum([" & rs.Fields(3).Name & "])"
    Else
        Me.Label2.Caption = ""
        Me.Col2.ControlSource = ""
        Me.Col2Sum.ControlSource = ""
    End If

Open in new window

Imagine, your data has all the dates the same:Table with the same datesIn this case, the PIVOT query will look like this:Query for the data with the same datesIf we try to use "Me.Col2.ControlSource = rs.Fields(3).Name" directly there will be a crash, as "rs.Fields(3)" does not exist (Fields(0) will be Item_no, Fields(1) will be Total of Sales, Field(2) will be 10/30/2013. There will be no Field(3))
So, in this case we check that the total number of Fields is greater than 3. Then it will be safe to use Field(3) in the VBA code.
You can easily extend the code to support more columns. Just make sure you continue to use the same If...Then code and use the proper numbers. E.g. for the column4 the code will be:
    If rs.Fields.Count > 5 Then
        Me.Label4.Caption = rs.Fields(5).Name
        Me.Col4.ControlSource = rs.Fields(5).Name
        Me.Col4Sum.ControlSource = "=Sum([" & rs.Fields(5).Name & "])"
    Else
        Me.Label4.Caption = ""
        Me.Col4.ControlSource = ""
        Me.Col4Sum.ControlSource = ""
    End If

Open in new window

I hope this explains.
0
 

Author Comment

by:leezac
ID: 39788519
Great detail.  I am looking at now.
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

624 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