Solved

Error report - not finding label

Posted on 2014-01-14
11
155 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
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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 24

Accepted Solution

by:
chaau earned 500 total points
Comment Utility
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 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
 

Author Comment

by:leezac
Comment Utility
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 24

Expert Comment

by:chaau
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:leezac
Comment Utility
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
Comment Utility
Nevermind this question
0
 

Author Comment

by:leezac
Comment Utility
Wait before you do anything more - I need to repost.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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 24

Expert Comment

by:chaau
Comment Utility
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
Comment Utility
Great detail.  I am looking at now.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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…

772 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

14 Experts available now in Live!

Get 1:1 Help Now