Label visible on sub report with code (Access 2002)

Topic

New Reply

Situation: Button, cmdApril , on form, frmClient, becomes visible in April, imagine that. When click get MsgBox with Yes No. Yes = print a report. But prior to printing the report rptJonCombined a label on subrptHUDYearlyTotals must become visible. I tried the following without success I also used “!” without success.

Case vbYes
rptJonCombined.subrptHUDYearlyTotals.lblEndReport.Visible = True
DoCmd.OpenReport “rptJonCombined”

Any help would be appreciated. Thank you. Fay

Reply | Quote

Replies

Fay,

You can’t change the visible property of a report until it is open.

You have to set something in your form and check for it in the on open event of the report. A solution is the tag property of your cmd buttom.

Case vbYes
cmdApril.tag = “Yes”
DoCmd.OpenReport “rptJonCombined”
Case vbNo
cmdAprli.tag = “” ‘ You have to empty it in case it is still there from a previous action

In the On Open event of the subreport :
If Forms!frmClient!cmdApril.Tag = “Yes” then
Me.lblEndReport.Visible = True
else
Me.lblEndReport.Visible = False
End If

Reply | Quote

Thank you Francois that was right on the mark. The folks that frequent this board are great. Thank you for your time. So many of the things needed to get the job done are not always apparent.

Thank you. Fay

Reply | Quote

Ran into a problem. Procedure works great when using the cmdApril button for the end of year row up of reports. But the same report is used monthly and should not display the label. The code we used to make the labels visible for the cmdApril button stops the use of the forms when going through the normal monthly routine.

Thank you. Fay

Reply | Quote

If you are calling the report from the same form for your monthly routine, you can pass a second condition with another tag to decide to execute the first condition or not.

If It is from another form, you can copy the code from the Function IsLoaded in the Module Utility Function in the NorthWind mdb in a module of your mdb and modify the event in the report to:
If IsLoaded(“frmClient”) then
If Forms!frmClient!cmdApril.Tag = “Yes” then
Me.lblEndReport.Visible = True
else
Me.lblEndReport.Visible = False
End If
End If

Reply | Quote

I don’t think I explained myself. The data is in a table tblCompanyFacts in a field called AprilSet.
I need to test the table field AprilSet to see if it is = Year(Now) – 1.

My understanding is that Me only refers to the current object not an external object such as a table.
I think the only missing link is how to reference the table and field.

Thank you. Fay

Reply | Quote

Are you saying that the field Aprilset is not displayed on your (sub)form ?

Reply | Quote

Yes. The form is not attached to the table with the data I need to check in this situation. PS the fix on the other problem seems to be doing the trick.

As always thanks a lot. Fay

Reply | Quote

Change the on open event to this (Look to the false and true, I don’t know witch has to be false and witch has to be true.
In the code window, click Tools/references and see if Microsoft DAO 3.6 Libray Object is checked. If not check it.

Private Sub Form_Open(Cancel As Integer)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("tblCompanyFacts", dbOpenDynaset)
If rst.RecordCount = 0 Then
   MsgBox "There is no data", vbOKOnly
   Set rst = Nothing
   Set db = Nothing
   Exit Sub
Else
   rst.MoveFirst
   If rst.AprilSet = Year(Now) - 1 Then
      Me.cmdApril.Visible = True 
   Else
      Me.cmdApril.Visible = False ' I think this si false 
   End If
End If
Set rst = Nothing
Set db = Nothing
End SUB

Reply | Quote

Francois This is getting scary. I think I actually know what you are saying in the above code. The True False was correct the only thing I had to change was If rst.AprilSet It demanded and !. Scary I figured that out. I think it is coming together but I have to do some more testing.

Thank you for all of your help today. Fay

Reply | Quote