My Assistant
Site Message
(Message will auto close in 2 seconds)
  |
 Put a queries multiple recs into a memo field- append query?
|
|
 Jul 17 2008, 12:05 PM
Post
#1
|
|
|
UtterAccess Ruler Posts: 1,064  |
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! |
 |
 
|
|
Jul 18 2008, 05:47 AM
Post
#2
|
|
|
UtterAccess Guru Posts: 587 From: London, England |
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 |
|
|
|
|
Jul 18 2008, 12:27 PM
Post
#3
|
|
|
UtterAccess Ruler Posts: 1,064 |
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. |
|
|
|
 |
|
Jump To Forum:
|
Go to Top · Lo-Fi Version | Time is now: 23rd May 2013 - 10:25 AM |