Setting Control Source for a Report Control using VBA

  • Thread starter Thread starter Diane Mountford
  • Start date Start date
D

Diane Mountford

My apologies if this is a multiple post, I had a key-punching fit
which took me out of my previous attempt!

I have a form which allows the user to select a variety of options for
reporting on a group of people. I would like to create one report
which changes programmatically based on the options chosen ... rather
than muddying my system with separate reports for each possible
combination (which would be a lot of reports).

For instance, I have Birthdate and Birthplace checkboxes on the form.
I would like to set the control source for a text box on the form to
equal one of the following cases:

1. Both checked: ="Born on " & [Birthdate] & " in " & [Birthplace]
2. Birthdate only checked: ="Born on " & [Birthdate]
3. Birthplace only checked: ="Born in " & [Birthplace]
4. Neither checked: null

I tried a bunch of options for this, but was unsuccessful in finding
the right code and the right place to put it. I solved the problem by
having three stacked controls and using the visible property, but this
seems like a rather inelegant solution.

Any ideas out there?
 
I usually handle this type of expression in control sources or code in the
report. When your "rules" need to be applied to more than just the one
report, then I create a separate module for "business rules".
Your request seemed quite focused on a single report and condition.

--
Duane Hookom
MS Access MVP


Diane Mountford said:
Thanks, Duane, that works like a charm.

Is a formula in the Control Source property box always the best way to
do this? Or is there some way to write this sort of thing into code?

I'm now thinking of a slightly different example where I want a
specific control on the report to display the start date if that check
box is filled, or the end date if the end date box is checked but the
start date box is not, etc. I could obviously do a formula like the
birthdate solution, but was wondering if that's the most efficient
way.

If you have $0.02, I'd love to hear them

Cheers,
Diane


"Duane Hookom" <[email protected]> wrote in message
Assuming Forms!frmA!chkBP and Forms!frmA!chkBD
=IIF(Forms!frmA!chkBP<>0 Or Forms!frmA!chkBD<>0, "Born", "") &
IIf(Forms!frmA!chkBD<>0, " on " & [BirthDate],"") & IIf(
Forms!frmA!chkBP<>0," in " & [BirthPlace],"")

--
Duane Hookom
MS Access MVP


Diane Mountford said:
My apologies if this is a multiple post, I had a key-punching fit
which took me out of my previous attempt!

I have a form which allows the user to select a variety of options for
reporting on a group of people. I would like to create one report
which changes programmatically based on the options chosen ... rather
than muddying my system with separate reports for each possible
combination (which would be a lot of reports).

For instance, I have Birthdate and Birthplace checkboxes on the form.
I would like to set the control source for a text box on the form to
equal one of the following cases:

1. Both checked: ="Born on " & [Birthdate] & " in " & [Birthplace]
2. Birthdate only checked: ="Born on " & [Birthdate]
3. Birthplace only checked: ="Born in " & [Birthplace]
4. Neither checked: null

I tried a bunch of options for this, but was unsuccessful in finding
the right code and the right place to put it. I solved the problem by
having three stacked controls and using the visible property, but this
seems like a rather inelegant solution.

Any ideas out there?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top