Dynamic Text Box

A

AL

I've created a procedure which dynamically
allocates a value for a text box on a report based
on sales for a three month period. If sales for a month
are 0 or null, then that months sales is not reported.
Sales are contained within the fields from my query:
month1, month2 and month3 and are hidden on the report.
I haven't shown the month names to simplify.

Though I've read previously on the forum, that text boxes
should be allocated before runtime, this just isn't
possible in this situation.

The problem is that the procedure only evaluates the first
record and carries forth the first records results
throughout the report.

Attached you will find the procedure. Thanks in advance.



Private Sub Detail_Format(Cancel As Integer, FormatCount
As Integer)
Dim M1, m2, m3 mtotal As String


If Me.MONTH1 > 0 Then M1 = "Y" Else M1 = "N"
If Me.MONTH2 > 0 Then m2 = "Y" Else m2 = "N"
If Me.MONTH3 > 0 Then m3 = "Y" Else m3 = "N"

mtotal = M1 & m2 & m3

SELECT CASE MTOTAL
Case "YYY"
Me.MTEXT1 = Me.MONTH1
Me.MTEXT2 = Me.MONTH2
Me.MTEXT3 = Me.MONTH3
Case "YNN"
Me.MTEXT1 = Me.MONTH1
CASE "YYN"
Me.MTEXT1 = Me.MONTH1
Me.MTEXT2 = Me.MONTH2
CASE "NYY"
Me.MTEXT1 = Me.MONTH2
Me.MTEXT2 = Me.MONTH3
CASE "NYN"
Me.MTEXT1 = Me.MONTH2
CASE "NNY"
Me.MTEXT1 = Me.MONTH3
CASE "YNY"
Me.MTEXT1 = Me.MONTH1
Me.MTEXT2 = Me.MONTH3
END SELECT

If Me.MText2 > 0 Then
Me.MText2.Visible = True
End If

If Me.MText3 > 0 Then
Me.MText3.Visible = True
End If


End Sub
 
Top