VBA Behind Report to Populate Unbound Field

  • Thread starter Thread starter Van S W
  • Start date Start date
V

Van S W

I would like to use VBA to populate an unbound text field
in the detail section of a report with an Integer (1, 2 or
3) dependent upon the value contained in another bound
Integer text field in the detail section. I plan on using
an If-Then-Else statement (3 cases to consider) to
determine the Interger of choice for the unbound field but
I am a beginner programmer and do not quite understand
which report event (Print, Format, Retreat) to use or if a
Sub or Function Procedure should be used. The basic VBA
classes really only dealt with VBA behind forms and not at
all with report logic. If Integer bound text field is Null
or less the 10, the unbound field would be 3; if between
10 and 20, unbound field would be 2; and if >20, unbound
field would be 1. Any help would be appreciated!
Thanks, Van
..
 
I would like to use VBA to populate an unbound text field
in the detail section of a report with an Integer (1, 2 or
3) dependent upon the value contained in another bound
Integer text field in the detail section. I plan on using
an If-Then-Else statement (3 cases to consider) to
determine the Interger of choice for the unbound field but
I am a beginner programmer and do not quite understand
which report event (Print, Format, Retreat) to use or if a
Sub or Function Procedure should be used. The basic VBA
classes really only dealt with VBA behind forms and not at
all with report logic. If Integer bound text field is Null
or less the 10, the unbound field would be 3; if between
10 and 20, unbound field would be 2; and if >20, unbound
field would be 1. Any help would be appreciated!
Thanks, Van
.
Click to expand...

If you must use VBA, you could place your code in the Detail Print
event:

If IsNull([SomeField]) Or [SomeField] < 10 then
[OtherControl] = 3
ElseIf [SomeField] >= 10 and [SomeField]<= 20 Then
[OtherControl] = 2
Else
[OtherControl] = 1
End If

However, you don't need VBA to do this.
You can insert and expression as Control Source of an Unbound control
in the Datail Section:
=IIf(IsNull([SomeField]) Or [SomeField]<10,3,IIf([Somefield]>=10 and
[SomeField]<=20,2,1))
 
Back
Top