Greetings all, I'm Vince and I am new here. I would like to know if anyone can help me with an interesting problem that I am having with one of my reports.

I created a report based on a query that I wrote. The query and report work just fine. I am however trying to fine tune the report. I have added an Overdue label to the report that is normally not visible. I have added several different controls to the form to calculate the DateDiff between when the item was due for calibration and now. The result is either a positive number or negative number which currently show on the report. What I am trying to do is get the "Over Due" label to appear Only for the records where the number of days remaining is less than one. In VBA I wrote some simple code to do this.

Private Sub Report_Load()

If txt_days_remain.Value < 1 Then
lbl_over_due.Visible = True
Else
lbl_over_due.Visible = False
End If

End Sub

The code runs fine and I get the "Over Due" label to appear, but here is the wierd part. If any one of the records has a negative number in the days remaining txt box, then the "Over Due" lable appears for all of the records even if it is not overdue. I am pretty sure that this has something to do with the isolating a record set, but this is something that I haven't figured out how to do just yet. If anyone can help I would appriciate it.

Hi Vince,

Welcome to the forum.

When you change the visibility of a control on a report, it will affect every detail record within that report. In order to achieve your goal you need to place the code in the OnPrint event of the Detail section.

An easier way to set the Visible property would be as follows:

lbl_over_due.Visible = (txt_days_remain.Value < 1)

No If, Then, Else and End If required!

HTH.

Malc.

Malc,
Thanks for the tip. I tried that code and it worked. Now the question is how do I get this into a format that I can add a button or two to send the item to calibration and edit the record. My thought is that I have to pass the tool id to an update query, but here again this is not something that I know how to do just yet. I am wondering if perhaps this process would be simpler to work with a form and add a control to generate the report. The whole idea is a report that will auto run and send the results of the report through an email. If you have any suggestions I would appriciate it.

Hi Vince,

I suspect you want the whole process to be initiated by a user? A form with a Command button to run some VBA code via the OnClick event would be ideal.
Use the DoCmd.SendObject command to run the report and email it. What are you wanting to update? Is it different for each record or the same update for a certain selection of records? After the above command a query can be run to perform the necessary updates using either DoCmd.RunSQL or CurrentDb().Execute.

HTH.

Malc.