Go Premium for a chance to win a PS4. Enter to Win

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

Error report - not finding label

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
leezac
Asked:
leezac
  • 5
  • 3
  • 2
  • +1
1 Solution
 
Rey Obrero (Capricorn1)Commented:
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
 
chaauCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
leezacAuthor Commented:
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
 
chaauCommented:
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
 
leezacAuthor Commented:
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
 
leezacAuthor Commented:
Nevermind this question
0
 
leezacAuthor Commented:
Wait before you do anything more - I need to repost.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
chaauCommented:
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
 
leezacAuthor Commented:
Great detail.  I am looking at now.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now