leezac
asked on
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("qryfunds
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.
ASKER
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
Database100.zip
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.
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
Database100.mdb
ASKER
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
ASKER
Nevermind this question
ASKER
Wait before you do anything more - I need to repost.
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.
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_
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.
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 dynamicWhen you build a report you will need to replace the control source for the columns. The way I proposed to do this is to use VBA code. In the code I first open the query:
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:
Now, let me explain this chunk of code:
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:
Set rs = db.OpenRecordset("tb_temp_Crosstab")
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
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 & "])"
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
Imagine, your data has all the dates the same:In this case, the PIVOT query will look like this:If 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
I hope this explains.
ASKER
Great detail. I am looking at now.
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
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