Control source either form field or table field depending on contents

[MattE]

I have a form bound to a table. One of the fields is giving me fits.

That field should get its value from a field in the table unless that
table field value is null. In that case it should get its value from a
field in the calling form. The control source is:

=IIf(IsNull([ValueInTable]),[Forms]![CallingFormName]![ValueInForm],[ValueInTable])

So the first time I do this, the table is empty, so the field gets its
value from the calling form. But when I close this form, the new
record in the table was saved without anything in that field.

Hope this makes sense. Any help out there?

Matt

 

[Douglas J Steele]

If you have a function or calculation as the Control Source, it doesn't
update the table: it simply displays the value on the form.

Assuming that you want ValueInTable to contain what's on the form if it
doesn't already have a value, bind the text box to ValueInTable, and put in
VBA code in the form's Current event to populate the text box.

 

[MattE]

Thanks, that did it!

Matt