UtterAccess Discussion Forums > Put a queries multiple recs into a memo field- append query?

Full Version: Put a queries multiple recs into a memo field- append query?

UtterAccess Discussion Forums > Microsoft® Access > Access Queries

kevinlaw

Jul 17 2008, 12:05 PM

I'm not sure if this can be done (but at UA it can always be done!!). I have a query that runs when a command button is clicked on a form (along with a few other action queries). The results of the query are grouped, with a quantity summed for each group record.

What I then need to do is take this queries results- basically a list- and put them into a memo field in a table. I've done a lot of Append Queries that work really well so I was hoping to turn this query into an Append Query to do that. Problem is I need all the queries records to go into only ONE record of the other table (and in the memo field). For Example:
The query might bring these results:
Marie 4 hours
Steve 2.5 hours
Jon 12.5 hours

I need it to look like this in the memo field of one record in another table (preferably in this 'paragraph' format, each on it's own line in the memo field). Is it possible to do this? Many thanks in advance!

GhostX1

Jul 18 2008, 05:47 AM

Hi there

You can just loop through the records in your query and add them to a string, after that run your INSERT query to populate your memo field. Something like this:

CODE

Dim rst As Recordset
Dim fld As Field
Dim strText As String
Dim strSQL As String

strSQL = "select * from myTable" 'Change to required input data
rst.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly

Do While Not rst.EOF
        For Each fld In rst.Fields
                strText = strText & fld.Value & " "
        Next
        'get rid of the extra space
        strText = Left(strText, Len(strText) - Len(" "))
        strText = strText & vbNewLine
        rst.MoveNext
Loop
strSQL = "INSERT INTO tblArchive (myMemoField) VALUES (" & strText & ");
CurrentProject.Connection.Execute strSQL

HTH

kevinlaw

Jul 18 2008, 12:27 PM

Hey, thanks for the help on this. I'm not completely sure of how this method would be employed. I imagine this code would be put into the onClick even of a command button? If so, is there a way to delineate exactly which record of the table with the memo field the results would go into? There will be a shared value for "BillID" (a number) that each record in the query has that will match the one record's "BillID" number value. I need to make sure the results go into that particular record's memo.

Thanks again for the help, this is a new sort of process for me so I'm not completely sure about all the code.

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.