Conditional DLookup..

[CrystalSurfer]

the following code retrieves a default value for a textbox on a continuous subform:

Me.BodyText.Value = DLookup("[DefaultBodyText]", "[tblHeading]", "[ID] =" & Forms![frmClient]![sbfrmClientDocument]!Heading.Value)

This works nicely except it overwrites any value typed into [BodyText].
So how can I code it so that it will not perform the DLookup if there is a value already in [BodyText]?
I've tried using various Nz() without success.

Many thanks

 

[RuralGuy]

Me.BodyText.DefaultValue = ...

 

[pbaldy]

If IsNull(Me.BodyText) Then
  Me.BodyText = ...
End If

 

[RuralGuy]

If IsNull(Me.BodyText) Then
  Me.BodyText = ...
End If

This is another method but I would use: If Len(Me.BodyText & "") > 0 Then to catch ZeroLengthStrings (ZLS) as well.

 

[pbaldy]

I would actually do that too, though I use:

If Len(Me.BodyText & vbNullString) > 0 Then

as it's a little more efficient. I think the default value method would only work for new records, and the OP didn't specify if that was the situation, so I offered the other alternative.

 

[CrystalSurfer]

Excellent! Thats done it! Thanks for your help guys.

Yes, I wanted it to re-retrieve any default if the user deletes all the text and they changed the drop down value.
I also just reversed the sign..

If Len(Me.BodyText & vbNullString) <= 0 Then
    Me.BodyText.Value = DLookup("[DefaultBodyText]", "[tblHeading]", "[ID] =" & Forms![frmClient]![sbfrmClientDocument]!Heading.Value)
End If

btw, I particularly appreciate our time differences because I get stuck in a problem at the end of a day, post up the question to this great forum and then have the answer when I come into the office in the morning. Now that really is solving a problem by sleeping on it!

Many thanks!

 

[RuralGuy]

Just for the record: Len(Me.BodyText & vbNullString) will *never* be < 0

 

[CrystalSurfer]

ahh, I was 'erring' on the side of caution with that one.

Thanks RG

 

[RuralGuy]

np - just keeping the record straight.