Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Error report - not finding label

Posted on 2014-01-14
11
Medium Priority
?
177 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 2000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

660 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