Text field control source determined at run time

[slames]

Is there a way that I can alter the control source of a text box on a report at run time? I have an address text box and need to format the address differently dependent on the format code which is found in another text box.
Can I do this?

The control source itself is an if statement.

Thanks in advance.

Stef

 

[mordja]

Slames,

You could use the format or print event of your report. From the sounds of it you would want to do it at the detail level. To set the control source of a text box simply use yourtxtboxname.ControlSource = whatever

Hope this helps

Mordja

 

[slames]

Yes, I had looked into doing this but get the following error:
"You can not set the control source property after printing has started"

I have the following code in the detail on format event:

txtFormat1.ControlSource = =IIf(Not IsNull([Name]),Chr(13) & Chr(10) & Trim([Name]),([Name])) & IIf(Len([Dept])>1,Chr(13) & Chr(10) & Trim([Dept]),([Dept])) & IIf(Len([Company])>1,Chr(13) & Chr(10) & Trim([Company]),([Company]))

Where name, dept, company are fields of my query on which the report is based on.

Is there something I'm missing?

Stef

 

[mordja]

Stef,

I think to start with you need "" around the value you are setting it to. But I dont think that would solve the given error.

txtFormat1.ControlSource = "=IIf(Not IsNull([Name]),Chr(13) & Chr(10) & Trim([Name]),([Name])) & IIf(Len([Dept])>1,Chr(13) & Chr(10) & Trim([Dept]),([Dept])) & IIf(Len([Company])>1,Chr(13) & Chr(10) & Trim([Company]),([Company]))"


Try that and see.

Mordja

 

[slames]

No unfortunately not. Thanks anyway though for your suggestion.

 

[mordja]

Stef,

Now that I think about it why not calculate the value in the event instead of setting the control source in the event.

Like



Dim sourceValue As String

If (Name <> Null) Then
sourceValue = Chr(13) & Chr(10) & Trim(Name)
Else
sourceValue = Name
End If

If Len(Dept) > 1 Then
sourceValue = Value & Chr(13) & Chr(10) & Trim(Dept)
Else
sourceValue = Value & Dept
End If

If Len(Company) > 1 Then
sourceValue = Value & Chr(13) & Chr(10) & Trim(Company)
Else
sourceValue = Value & Company
End If

Me.txtformat1.Value = sourceValue


Hope this helps.

Mordja