Pass input in textbox to report... (1 Viewer)

[ChiTriGuy]

Hello everyone -

I appreciate your attention to this question. I haven't done any real VB programming in decades, however our programmer has quit and I am the designated sucker until we hire someone new to the position.

I have inherited an Access db that, among other things allows our sales people to send letters to our customers. This behemoth is about as user friendly as a brick and I have been working to make it more self service for our sales people (hopefully I can get back to my regular duties). I have created a form with three text boxes. I would like the users to enter text in these three boxes and this to update text in the letters (report) they are going to be sending out. This will be using the Replace function, searching for a specific bit of text and replacing it with what is entered in the text box. However, my code is lacking. I have spent two days on this and every time I do a debug.print on whatever text I put in the text box, it shows up as null and therefore I can't pass that to the control in the report...even if I knew how to do it. I can store the letter text if I use three input boxes, but it would be much more user friendly to use textboxes. Maybe text can't be sent between forms and reports and this is all for naught. I'm sure I am referencing the controls wrong because on the line when I am referencing the report I am getting an error saying that the report doesn't exist when I am using the correct name. In VB I remember using .text or .value properties, the .controlsource has me befuddled.

I don't really have any code because nothing is working, but I can hash out something and hopefully someone can point me in the right direction.

Private Sub cmdUpdateLetters_Click()
    'I'm only showing one text box for brevity
    Dim strInput1stParagraph     As String

    strInput1stParagraph = Forms![Input Form]!txtFirstParagraph.ControlSource
    Debug.Print "strInput1stParagraph = " & strInput1stParagraph         'Getting nothing
    strOutput1stParagraph = Reports![Output Letters]!txtFirst.ControlSource
    Debug.Print "strOutput1stParagraph = " & strOutput1stParagraph        'Getting nothing
    Reports![Output Letters]!txtFirst.ControlSource = Replace(strOutput1stParagraph, _
    "OutputFirstParagraphText", "=" & strInput1stParagraph & ":" & [NewDate])
    
End Sub

Again, I apologize for the newbie question but if you could point me in the right direction, I would greatly appreciate it.

Sincerely

ChiTriGuy

 

[CJ_London]

some example data would be helpful. you shouldn't need to reference controlsource since this is the name of a field or a calculation of some sort - and should be blank if users are entering some text

try

Debug.Print "strInput1stParagraph = " & Forms![Input Form]!txtFirstParagraph

I would expect if your letter is something like

Dear [CustomerName],

To confirm your purchase of [Item], it will be delivered on [DeliveryDate]

.... etc

your report sql would be something like

SELECT replace(replace(replace(letterfield,"[CustomerName],Forms![Input Form]!txtCustomerName),[item],Forms![Input Form]!txtItem),[DeliveryDate],Forms![Input Form]!txtDelDate)
FROM tblLetters
WHERE LetterID=Forms![Input Form]!cboLetter

 

[theDBguy]

Hi @ChiTriGuy. Welcome to AWF!

I'm not sure any code is needed. You may be able to simply bind the report to the table and then use concatenation in the Control Sources of your Textboxes.

 

[ChiTriGuy]

Thank you for your reply.

For example, the report's first paragraph textbox output field contains this: ="FirstParagraph" & [NewDate] & ":"

[NewDate] is a field that the reports recordset contains. I am looking to Replace the text "FirstParagraph" with whatever the user is entering in the input field.

For example, someone might type "Your new product will be available to you ", another might type "We appreciate your business, your pricing will be available on ".

I would do a separate Replace statement for the SecondParagraph in its associated textbox and so on.

Does that make sense?

 

[Pat Hartman]

Here's an example using OLE to automate word. You create a word document with bookmarks as place holders. The application copies the template and puts data in each bookmark. The mapping in this example is hard coded and so it is not flexible or very expandable. The working app manages thousands of documents and uses tables to control the mapping. So, as long as no new data fields are required, they just make new word documents using bookmarks for known fields and I don't need to get involved. It is all handled by them once they understand the concept of bookmarks and mapping.

 

[CJ_London]

as suggested before, provide some example data.

Seems to me you are trying to do this in vba when it would appear to be easier to do in sql per my example - which is easily adapted to using 'firstparagraph' rather than [CustomerName]

 

[bastanu]

Or you can simply add the three controls from the form to the report's record source as calculated fields:
FirstParagraph: Forms![Input Form]!txtFirstParagraph
SecondParagraph: Forms![Input Form]!txtSecondParagraph

Now open the report in design view and in the control source of the textboxes in question enter something like this:

=[FirstParagraph] & [NewDate] & ":"

Please note that with this approach it is obviously important to keep the form open while opening the report.

Cheers,

 

[ChiTriGuy]

Ok, after a good cuppa it finally hit my why replacing the text in the report is giving me an error that it can't find the field...I didn't open the report...duh.

Thank you for moving me in the right direction. At least now I know how to work with the inputted data.