Passing a Variable to a text box in a Report, possible? Easy?

[CptCrusty1]

I have a variable strText (DIM strText as String) that I populate with a string. I want to pass this value to a textbox when the report opens. I used.....:

txtMyBox.text = strText, however it says I can't do that.

Next I tried

Report_rptMyReport.txtMyBox = strText and it tells me an Object is required. Any ideas?

Better yet.... the VBA while-loop I use to populate the string with, is it possible to run it directly from the textbox, or something like that?

Thanks
Rich.

 

[dhookom]

If you have an unbound text box in a report's header section, you would use code in the On Format event of the section like:
Me.txtMyBox = strText


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[CptCrusty1]

What about in the body of the report?

(whispering - How long have you been doing this stuff? In Access)

RK

 

[CptCrusty1]

dhookom... it didn't work. Here's my code:

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)

Dim strText As String

'Selects Locations in tblDestinationLoc and Concatenates as a string
With CurrentDb.OpenRecordset("tblDestinationLoc")
Do Until .EOF
strText = strText & "'" & .Fields("Location_Num") & "', "
.MoveNext
Loop
End With

'removes trailing spaces and extra comma
lenStrLength = Len(strText)

'Assigns stripped string value to Variable
strText = Left(strText, (Len(strText) - 2))


Me.txtLocTest.SetFocus
Me.txtLocTest.Text = strText


My client is really pushing on this, I'm not sure why. Do you have any other ideas?

Thanks.
Richard.



End Sub

 

[dhookom]

You can't set the focus to a control on a report. Also, the Text property is rarely if ever used in Access and never in an Access report.

Why would you not use what I suggested?
Me.txtMyBox = strText

Where is your text box located (which section)?

There is a generic Concatenate function at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

that could be used. You would then just add a text box with a control source of:
=Concatenate("SELECT Location_Num FROM tblDestinationLoc")

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[CptCrusty1]

I think I misunderstood. I just now realized that your code Me.txtMyBox = strText. If you look at my last line, I did use what you suggested, however, I didn't see that "text" was not part of it. Also, I included the rest of the code to show you what was being assigned to the strText variable. The generic concatenate function was giving me fits. I couldn't make it work for some reason. Thats why I wrote the while looop. I"ll try again using your code, spelled correctly this time.

Thanks again.
Rich.

 

[fredzeppelin]

I came searching for exactly the same topic and problem. Using dhookom's suggestion, I attached this code to the Report Header OnFormat event. (Note that I'm using a label rather than a text box, but the ideas's the same. Ignore the Select Case logic.

This worked fine.

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
'       LBLTitle is a label control 


        Select Case [Forms]![frmEOHReport]!oleEOHReportBy
            Case 1
                Me.Caption = "Statistics by Foo"
                Me.LBLTitle.Caption = "Statistics by Foo"
            Case 2
                Me.Caption = "Statistics by Bar"
                Me.LBLTitle.Caption = "Statistics by Bar"
            Case 3
                Me.Caption = "Statistics by Bozo"
                Me.LBLTitle.Caption = "Statistics by Bozo"
        End Select
      
End Sub

Thanks for the help

 

[originalxavier]

Crusty, did you get your problem resolved? If not, are you sure that your strText is pulling the data? You can check by adding a "MsgBox strText" in the code. If this works, then forget about "txtMyBox.text = strText" and try this...

[txtMyBox] = strText

Let me know if that works


Xavier

----------------------------------------
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far the Universe is winning"
Rick Cook
----------------------------------------

 

[dhookom]

Rich,
You might want to try open the Debug window (Press Ctrl+G) and enter

? Concatenate("SELECT Location_Num FROM tblDestinationLoc")

See if you get a list of your Location_Num values. What do you see if you paste this sql in to the SQL view of a query:
[tt]
SELECT Location_Num FROM tblDestinationLoc
[/tt]


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[XwBrendanXw]

How would you go about changing the value of a text box in a report, from a form which opens that report?

ie.
Form_button_click:
open report
set text box value on report to something

 

[dhookom]

The control would need to be unbound. Your code might look like:

Me.txtUnBoundBox= Forms!frmYourForm!txtYourTextBox

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[XwBrendanXw]

So that code would go in the report, and gets the value from the form... what about putting code in the form, that sets the value in the report?

 

[XwBrendanXw]

I tried reversing it:
Reports!rptMyReport!txtMyTextBox = Me.txtMyFormTextBox

but it didn't do anything

I can probably use your method to do what I want. But I'd still like to know how to do it the other way.

 

[dhookom]

Is there a reason you don't add a text box on the report with an expression like:
=Forms!frmYourForm!txtYourTextBox


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[CptCrusty1]

Hi guys.. The problem fixed itself when the client changed his mind. No more Form, no more box... very simple solution don't you think? But... I do enjoy reading your entries which are obviously more experienced than my own.

RK