Editable Text Box

[depawl]

Here’s what I’m trying to accomplish:
I have a customer survey. Let’s say for example (simplified, of course) that 3 customers respond to a survey as follows:
Customer 1 Response: Good
Customer 2 Response: Bad
Customer 3 Response: Ugly
These responses are entered onto a form which populates a field in a table.
I have a module with code that concatenates these responses.
Next I have a second form that a staff member of my agency will use. I have a text box on that form that pulls the concatenated results of the customer responses, like so:
=ConcatenateRecs("Table1","CustomerResponse")
Which results in:
Good, Bad, Ugly
In one text box when my staff member opens the form.
So what I need is to make that text box editable, so that my staff can add a narrative to the customer responses. For example: “Same comments we received last year”.
So the edited text would appear as:
Same comments we received last year: Good, Bad, Ugly.
Then I need to be able to populate this info into a text box on a report.
Is this possible, and if so, how would on go about it?
As always, thanks in advance for any assistance.

 

[RainLover]

Not possible in the same Field.

You need to add another field then Concatenate the new result.

 

[depawl]

Hmmm,
I'll have to think about that one for awhile but I basically understand what you are saying (now just how to accomplish it). My existing concatenate function combines the records from the same field, but this one would have to combine the records from 2 fields, correct? Thanks again for the expert advise.

 

[RainLover]

Don't understand what you are saying.

Concatenate means getting the information from two or more fields and displaying them in one text box.

What do you mean by > My existing concatenate function combines the records from the same field <

 

[depawl]

Going back to my original example, that field in my table would be named 'CustomerResponse'. And there would be 3 records. My existing concatenate function combines the 3 responses from that field into one record. Lets call it 'ConcatResponse'.
Now what you are suggesting is that I would need to combine responses from 2 different fields.
the 'ConcatResponse' field and the 'narrative' field.

 

[RainLover]

I am not sure that Concatenate is the correct word to use in this situation. Hence my misunderstanding.

I can only but guess as to how you do this.

Could you post a sample database?

Might make it easier to understand your methods.

 

[RainLover]

Upon further thought I do need to see your table structure before comming up with a suggestion.

 

[depawl]

Yes, let me work up a sample. My agency is such that they are quite concerned about client confidentiality. And thanks in advance for your continued assistance.

 

[RainLover]

Sure thing

Just rip out all the sensitive stuff.

 

[boblarson]

Sure thing

Just rip out all the sensitive stuff.

Boy, my eyes do need new glasses. I saw your response and what I THOUGHT I saw was "rip out all the SENSIBLE stuff." I did a "double-take" on that one.

 

[depawl]

OK, here's my scaled down database. You will see that data entered into Form1 feeds Table1. Then on Form2 you will see the concatenate function (see the module) combines the 'Comments' field. So what I need to accomplish now is to concatenate the 'comments' and the 'narrative' fields from Form2.

 

[RainLover]

Have you sent me the full Table designs.

What you ask for has no relationships, etc.

I think you left something out.

 

[depawl]

The full table has clientID, client location, etc. which is not relevant to the current issue I am facing, as far as I know. Also, the full table has about 40 survey questions and a comment box for each. But if I can get this to work for one I should easily be able to extract it to work for the remainder.
All I need to figure out how to do is use that concatenate function to combine 2 fields.
Thanks.

 

[RainLover]

Without Relationships how would you know which records from each table you need to use?

Or do you want to use every single record from both tables and join them together.

 

[depawl]

Yes, for this survey my agency is only interested in the responses to the total of all records.

 

[RainLover]

If that is what you want all you have to do is to loop through the other table and add the results to the first string.

Public Function ConcatenateRecs(strQryTbl As String, strField As String) As String
On Error GoTo err_proc
 
    Dim dbs As Database, qdf As QueryDef, rst As Recordset
    Dim strSQL As String
    Dim strMsgString
    strSQL = "SELECT " & strQryTbl & "." & strField & " FROM " & strQryTbl & " WHERE " & strQryTbl & "." & strField & " is not null;"
    Set dbs = CurrentDb
    Set qdf = dbs.CreateQueryDef("")
    With qdf
        .SQL = strSQL
        Set rst = .OpenRecordset()
        If rst.BOF = True Then Exit Function
        While rst.EOF = False
            strMsgString = strMsgString & rst(strField) & ", "
            rst.MoveNext
        Wend
    End With
    
    strMsgString = Left(strMsgString, Len(strMsgString) - 2)
    
    strSQL = "SELECT Table2.Narrative FROM Table2 "
    
    Set qdf = dbs.CreateQueryDef("")
    With qdf
        .SQL = strSQL
        Set rst = .OpenRecordset()
        If rst.BOF = True Then Exit Function
        While rst.EOF = False
            strMsgString = strMsgString & rst!narrative & ", "
            rst.MoveNext
        Wend
    End With
    strMsgString = Left(strMsgString, Len(strMsgString) - 2)
    
    ConcatenateRecs = strMsgString
    
exit_proc:
    Exit Function
    
err_proc:
    MsgBox Err.Description
    Resume exit_proc
    
End Function

 

[RainLover]

As a BTW.

Memo fields are prone to corruption.

So use Text if you can.