when to update and when to leave alone

[fishqqq]

I'm having problems updating a field .

I have a macro that takes data from one form and puts it into another
form but only if the destination field is blank - otherwise the
destination field isnt' to be updated

my setvalue expressions are as follows:

item: Forms![AWB Main Info]![Handling Information]
Expression: Nz([Handling Information],Forms![Flight Details]![leg3])

[Handling Information] = the destination field
and if that field is empty then i would like the info from [Flight
Details]![leg3] to be inserted
if that field has data in it already then i don't want the [leg3] data
entered into it.

Can someone please help me with this expression?

thanks
Steve

 

[John W. Vinson]

I'm having problems updating a field .

I have a macro that takes data from one form and puts it into another
form but only if the destination field is blank - otherwise the
destination field isnt' to be updated

my setvalue expressions are as follows:

item: Forms![AWB Main Info]![Handling Information]
Expression: Nz([Handling Information],Forms![Flight Details]![leg3])

[Handling Information] = the destination field
and if that field is empty then i would like the info from [Flight
Details]![leg3] to be inserted
if that field has data in it already then i don't want the [leg3] data
entered into it.

Can someone please help me with this expression?

thanks
Steve

Well, as written the expression should do exactly what you intend.

The NZ() function returns its first argument if that value is not NULL; if the
first argument is NULL it will return the second. So the SetValue will set the
control named [Handling Information] to itself if there is something there; if
the [Handling information] control is NULL, it will instead set it to the
value in [Leg3].

Is that not what is in fact happening? You say you're having problems but not
what those problems are.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[John W. Vinson]

Is there any chance that the field has a space in it?

If it's a table field, Access will trim trailing spaces, so a field set to " "
will in fact end up NULL: the exception would be if the user has changed the
"Allow Zero Length String" property of the field from the default No to Yes.
That would certainly explain the problem though!
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[fishqqq]

If it's a table field, Access will trim trailing spaces, so a field set to " "
will in fact end up NULL: the exception would be if the user has changed the
"Allow Zero Length String" property of the field from the default No to Yes.
That would certainly explain the problem though!
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

I figured out my mistake....

Expression: Nz([Handling Information],Forms![Flight Details]![leg3])
needed to be changed to:
Nz(forms![awb main info]![Handling Information],Forms![Flight Details]!
[leg3])

thanks for your help John

 

[fishqqq]

If it's a table field, Access will trim trailing spaces, so a field setto " "
will in fact end up NULL: the exception would be if the user has changed the
"Allow Zero Length String" property of the field from the default No toYes.
That would certainly explain the problem though!
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

I figured out my mistake....

Expression: Nz([Handling Information],Forms![Flight Details]![leg3])
needed to be changed to:
Nz(forms![awb main info]![Handling Information],Forms![Flight Details]!
[leg3])

thanks for your help John

I spoke to fast....

Nz(forms![awb main info]![Handling Information],Forms![Flight
Details]! [leg3])

now if [Handling information] is emplty it returns this message:
"Type mismatch"

if [Handling information] is not empty then it doesn't get updated
(which is what it is supposed to do)

any ideas?

thanks
Steve

 

[John W. Vinson]

I spoke to fast....

Nz(forms![awb main info]![Handling Information],Forms![Flight
Details]! [leg3])

now if [Handling information] is emplty it returns this message:
"Type mismatch"

if [Handling information] is not empty then it doesn't get updated
(which is what it is supposed to do)

any ideas?

Try covering the case that the form control is "empty" because it contains a
zero length text string (which a table field cannot) instead of a NULL. A ZLS
is not the same. Try

IIF(forms![awb main info]![Handling Information] & "" = "",forms![awb main
info]![Handling Information],Forms![Flight Details]![leg3])

Of course both forms must be open.

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com