Solved

Error report - not finding label

Posted on 2014-01-14
11
164 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 57
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
Independent Software Vendors: 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!

 

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 57
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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.
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

734 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