Macro to respond to Empty Field or Control

  • Thread starter Thread starter Dan Goble
  • Start date Start date
D

Dan Goble

I am printing to a report which is actually a contract that auto fills the
info from a form.
EX- John A Smith And Mary B Smith
John A Smith is a control concatenated
And is a label
Mary B Smith is a control concatenated

When John is not married and I only want John A Smith to appear theres "And"
left.

The name of the label for And is And
The name of the Wifes control is Sbuyername

On the condition line I put [Sbuyername]=""
Action line... Setvalue

[And].[Visible]
No

I have tried Null and "" both to solve for an empty field in the condition
line but they dont work for some reason.
Help
Thanks
Dan
 
Dan,

You did not mention where you have assigned this macro. I have never
tried a SetValue action like this in a report, so can not confirm that
it is an effective solution. But I would say the On Format event
property of the Report Section where these controls are located would be
the applicable place.
[Sbuyername]="" is unlikely to be correct as the Condition. The
[Sbuyername] field is unlikely to have a value of "". I would expect it
should be:
[Sbuyername] Is Null
Mind you, you said that "Mary B Smith is a control concatenated", which
I'm not sure what that means... do you mean that it is derived from a
concatenation of, let's sat, FirstName and Surname fields in the table,
or some such?

As an aside, 'and' is a Reserved Word (i.e. has a special meaning), and
as such should not be used as the name of a field or control. But I
don't think that is related to your problem.

As another aside, to aid your thinking in these matters, "auto fills the
info from a form" is not correct. Forms do not have "info". The data
is in tables, sometimes interpreted through queries. In fact, when it
comes to reports, their Record Source is almost always a query. Forms
act as a convenient avenue of accessing and viewing and editing the
data, but the data can not be said to be in the form.

As an alternative approach, consider trying this...
On the report, just put one textbox where you show the two names
concatenated, so:
John A Smith And Mary B Smith
or
John A Smith
.... if there is no Mary. This will be done via a concatenation
expression, either in the textbox's Control Source property, or else in
the query that the report is based on.
I think you will find thsi simpler and more satisfactory.
 
Back
Top