Create Report from Memo Field in Open Form

[txgeekgirl]

I have a Tab on a form that is dedicated to rebuilding a response from a supervisor to an auditor when they get dinged in an audit. The response builds off of several fields on a Table by audit number into a big Memo named tbMemoPreview.

From there - the auditor can accept/reject and it will pop to another screen for justification and email the supervisor back with the a notice.

I have added a Print button and made several attempts to create a report so the Auditor and Supervisors can print as needed. However - I cannot get the Responses to rebuild and have tried referencing the open form field but no success.

Any Ideas?

 

[crxftw]

You want memo field data to be display on a single report?

 

[txgeekgirl]

I do - and so finally after all the unsuccessful attempts, I wrote a query that mimicks DLOOKUPs for the display and just made a report off of that.

 

[crxftw]

If the memo field is also on one of the forms you could directly refer to that field control on your report or use DLookUp which should be the easiest solution.

 

[txgeekgirl]

Not like that - The Memo field is comprised of over 20 DLookup calls and it recreates a summary of Q/As, I may not have found the solution I wanted - it's not as slick as I wanted - but receating the structures in Qs was the best solution.

 

[crxftw]

I wouldn't have the memo field as actual field in your table, instead have it as text box in your form/report and you use DLookups to show the information there. If i understood then Q&A's are also in tables, and what you have created is a memo field as a field in your table, which imo is bad design, to have both there. You could have the memo field as text box without control source on your form/report wherever you wish to display it and use DLookups or field references (in case memo is first shown on form -> report).

A sample db would be useful.

 

[txgeekgirl]

I don't actually have a table field that is a memo with query or dlookups saving to it - That would be inefficient and too much work to format/unformat.

I was trying to get a display field on a FORM named tbMemoPreview to push to a report. The back end of that field looks like this:

 Sub FillResponse()
    'Fills in details of Supervisor response
    Dim myStr As String
    Dim Q2 As String
    Dim Q3 As String
    Dim Q4 As String
    
        
    If DLookup("CH2", "Findings", "[FindingID] = " & Me.tbFindings) = True Then
        Q2 = " At this time I am requesting training regarding the area of deficiency. "
        Q2 = Q2 & "Specifically I am requesting: " & DLookup("CH2Text", "Findings", "[FindingID] = " & Me.tbFindings)
    Else
        Q2 = ""
    End If
    
    If DLookup("CH3", "Findings", "[FindingID] = " & Me.tbFindings) = True Then
        Q3 = " At this time I believe the Audit does not reflect the program performance and am requesting mediation with the DED . "
        Q3 = Q3 & "Specifically: " & DLookup("CH3Text", "Findings", "[FindingID] = " & Me.tbFindings)
    Else
        Q3 = ""
    End If
    
    If DLookup("CH4", "Findings", "[FindingID] = " & Me.tbFindings) = True Then
        Q4 = " Audit Findings were due to management issues with staff. "
        Q4 = Q4 & "Specifically: " & DLookup("CH4Text", "Findings", "[FindingID] = " & Me.tbFindings)
    Else
        Q4 = ""
    End If
        
    ' Write Summary
     myStr = "In response to the Finding/Trend: " & DLookup("POIDetails", "Findings", "[FindingID] = " & Me.tbFindings)
     myStr = myStr & vbNewLine & vbNewLine & "I contacted " & DLookup("Auditor", "Findings", "[FindingID] = " & Me.tbFindings) & " on "
     myStr = myStr & DLookup("ContactDate", "Findings", "[FindingID] = " & Me.tbFindings) & " to discuss the Audit Findings."
     myStr = myStr & Q2 & Q3 & Q4
     myStr = myStr & vbNewLine & vbNewLine & "The corrective action I plan to take is the following: " & DLookup("POICorAction", "Findings", "[FindingID] = " & Me.tbFindings)
     myStr = myStr & vbNewLine & vbNewLine & "My plan to oversight the corrective action to ensure future compliance is: " & DLookup("POIOversight", "Findings", "[FindingID] = " & Me.tbFindings)
     myStr = myStr & vbNewLine & vbNewLine & "This POI will be implemented by: " & Str(DLookup("POIImpDate", "Findings", "[FindingID] = " & Me.tbFindings))
     myStr = myStr & vbNewLine & vbNewLine & "Submitting Staff: " & Me.ResMan.Column(1)
     
    Me.tbMemoPreview = myStr
End Sub

I solved the issue by mimicking Queries to feed the report off of the open form. Not really what I wanted as I had to have 2 queries / two different reports. I wanted to be able to push the display to the report.