Inserting an expression in a text box of a report using VB.

[Nevy]

Hi, I want to insert an Expression in a text box of a report using VB.

This is what I wrote in the code builder section of report header.

Me.totalBox.Value = strTotalExp

Me is the header section of the report.
totalBox is the textbox.
strTotalExp is the expression that I want to enter, it is a string.

How should I do this?

Thanks.

 

[WayneRyan]

Nevy,

Me.totalBox.Value = Forms![YourMainForm]!strTotalExp

The form must be open when the report is run.

Wayne

 

[Nevy]

Cool. Thanks.

The only problem is that it's printing as text. Is it because the string contains errors?

 

[WayneRyan]

Nevy,

Me.totalBox.Value = Format(Forms![YourMainForm]!strTotalExp, "FormatSpec")


Wayne

 

[Bat17]

What is strTotalExp?

Peter

 

[Nevy]

What is strTotalExp?

strTotalExp is the combination of 4 fields.

Depending on what the user chooses, strTotalExp can be a combination of A, B, C and D.

So if the user chooses A and C, then strTotalExp is A+C.

 

[Bat17]

But are you passing it the calculated value that you want it to show or are you trying to set the control to the expression so that it does the maths? it sounds like the second option but you are passing it to the .value of the control so that it will just display what you give it.

to pass the expression rather than the value use

strTotalExp = "=[A] & "
Me.txtTest.ControlSource = strTotalExp

HTH

Peter

 

[Nevy]

It's the second choice " you trying to set the control to the expression so that it does the maths? ".

But I still get it as text....

And for the control source over value, I don't think you can set controlsource after or in the print preview.
Anyways, I'll try to figure out it's not working.

Thanks.

 

[Bat17]

you would set the control source in the onopen event, you can't change it for each record.
But why not just do the maths in code and pass the value to the control? You can do that from the On print event.

peter

 

[Nevy]

Sorry, I don't want to sound like an idiot, but I don't get it.

I'll show you what I've wrote.

This is found in the form:

    Dim strTotalExp As String
    strTotalExp = ""
     ' sL, dL, sLt, dLt refers other textboxes in the report. 
    If (Me!checkOne) Then
        strTotalExp = strTotalExp + "[sL]+"
    End If
    If (Me!checkTwo) Then
        strTotalExp = strTotalExp + "[dL]+"
    End If
    If (Me!checkThree) Then
        strTotalExp = strTotalExp + "[sLt]+"
    End If
    If (Me!checkFour) Then
        strTotalExp = strTotalExp + "[dLt]+"
    End If
    strTotalExp = Left(strTotalExp, Len(strTotalExp) - 1)
    strTotalExp = "=" & strTotalExp

and in the report the "totalNumBox" textbox, I put this in the control source :

=Forms!MainPage!strTotalExp

 

[Bat17]

=Forms!MainPage!strTotalExp
would be refering to a value in a control called strTotalExp on a form called MainPage which I don't think is what you are after.

Setting the controlsource can only be done once in the report so I would add your code to the on open event with something like

Private Sub Report_Open(Cancel As Integer)
Dim strTotalExp As String
 strTotalExp = ""
  ' sL, dL, sLt, dLt refers other textboxes in the report.
 If (Me!checkOne) Then
     strTotalExp = strTotalExp + "[sL]+"
 End If
 If (Me!checkTwo) Then
     strTotalExp = strTotalExp + "[dL]+"
 End If
 If (Me!checkThree) Then
     strTotalExp = strTotalExp + "[sLt]+"
 End If
 If (Me!checkFour) Then
     strTotalExp = strTotalExp + "[dLt]+"
 End If
 strTotalExp = Left(strTotalExp, Len(strTotalExp) - 1)
 strTotalExp = "=" & strTotalExp
 
 Me.YourControl.ControlSource = strTotalExp
    
End Sub

Adding the correct name of the control in the report. Leave the control unbound.

If you want this to change for each record then you need to do the calculation and post its value to the control in the On Print event.

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim dblTotalExp As Double
 dblTotalExp = 0
  ' sL, dL, sLt, dLt refers other textboxes in the report.
 If (Me!checkOne) Then
     dblTotalExp = [sL]
 End If
 If (Me!checkTwo) Then
     dblTotalExp = dblTotalExp + [dL]
 End If
 If (Me!checkThree) Then
     dblTotalExp = dblTotalExp + [sLt]
 End If
 If (Me!checkFour) Then
     dblTotalExp = dblTotalExp + [dLt]
 End If
 
 Me.YourControl = dblTotalExp
End Sub

I hope that this is clearer now

Peter

 

[Rich]

and in the report the "totalNumBox" textbox, I put this in the control source :

=Forms!MainPage!strTotalExp

That should work, is the Form open while the Report's opening?

 

[Nevy]

I hope that this is clearer now

Yes it is, since it's working. Thank you!!!
But I've used Froms![MainPage]!checkOne instead of Me!checkOne.

That should work, is the Form open while the Report's opening?

Yes, the form is open while the report's opening. But it's working now.



Thanks again.