424,054 Members | 1,055 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,054 IT Pros & Developers. It's quick & easy.

Need to Pass multiple results from query back into one memo cell

P: 1
I have been all over the web for days searching for a way to accomplish the following, and I am praying some experts can help me figure it out! I have a client that wants to dynamically build a shipping "approval" email by pulling various text statements in, based on the results of queries. The shipping details will be in one table, and the text statements will be in another table. There could be multiple statements returned for each shipment (e.g. several training statements, several general statements) each going into a memo field for that type of statement (e.g. all training statements into one cell). Each of these statements needs to be added into the memo cell which will then be pulled into the email template for that shipment. The table structures are as follows.

Cross_Border_Grid_Table (holds shipment details)
*ID
*Shipment Type - Query Criteria
*Material Category - Query Criteria
*Permit Required - Query Criteria
*General Statement (memo field to hold all general statements that match criteria)
*Training Statement (memo field to hold all training statements that match criteria)
*Approval Statement (memo field to hold all approval statements that match criteria)

Statement_Table
*Statement Type (e.g. General, Training, Approval)
*Shipment Type - Query Criteria
*Material Category - Query Criteria
*Permit Required - Query Criteria

I successfully have a query (titled StatementSearch) that joins the two and pulls in the correct statements (the only field returned is [Statements} but it will have multiple rows). I can't figure out how to take the query results and append them into the memo cells for the shipment.

The code that has come close is below. It seems to correctly run my query and return the results, but is not putting the results into the memo field for my test record (ID =1 just to test the code, it will eventually run off of a form and the statements will generate off the click of a button). I plan to build a separate query to add each type of statement into the memo cells (there probably exists a better way to do it but it is super complex as it is).

I hope this isn't too confusing and I appreciate any guidance you can provide!

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Function StatementUpdate()
  4.  
  5. Dim dbs As DAO.Database
  6. Dim rstStatements As DAO.Recordset
  7. Dim rstCBG As DAO.Recordset
  8.  
  9. Set dbs = CurrentDb()
  10. Set rstStatements = dbs.OpenRecordset("StatementSearch")
  11. Set rstCBG = dbs.OpenRecordset("select [St_General]from
  12. [Cross_Border_Grid_Table] where [ID]= 1")
  13.  
  14. rstCBG.MoveFirst
  15. 'loop through each record in the CBG that matches select query
  16. Do Until rstCBG.EOF
  17.     rstStatements.MoveFirst
  18.     Do Until rstStatements.EOF
  19.         rstCBG.Edit
  20.         rstCBG![St_General] = rstStatements
  21.         rstCBG.Update
  22.     rstStatements.MoveNext
  23.     Loop
  24.     rstCBG.MoveNext
  25.     Loop
  26.  
  27. rstCBG.Close
  28. rstStatements.Close
  29.  
  30. Set rstStatements = Nothing
  31. Set rstCBG = Nothing
  32. Set dbs = Nothing
  33.  
  34. Debug.Print "Done"
  35.  
  36. End Function 
Sep 22 '15 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.