can memo field be filled with query results? 2

[naoaja]

I have a table/form with records that contain different titles. On another form is a memo field that I would like to have the records listed first and then allow the user to add to the memo field whatever text they want to add.
I thought I would have to make a query to get all the records(titles) for each particular project and then make the query results as the record source for the memo field.
The query runs fine and gives me the results that I expect, but when I try to get the results into the memo field, it just shows the first record(title) and not the rest of them. How to I get it to show all the records?

Thanks,
Naoaja

 

[fneily]

You could using VBA, loop through your query dataset and concatenate the titles, eg. memoinfo = title1 & "," & title2, etc. then use memoinfo as the record source for the memo field.

 

[naoaja]

Thanks for the response!
I'm not so hot on VBA. Is there somewhere that I could look at an example of how to loop thru the query dataset?

thanks again,
Naoaja

 

[BoxHead]

What table holds the memo field and how do you link it to the title?


John

Use what you have,
Learn what you can,
Create what you need.

 

[naoaja]

thanks for the response,

The table that hold the memo field is called libraries. I hadn't linked it to the title. I thought that it would just be based on the query. Does this help at all?

thanks!
Naoaja

 

[BoxHead]

Naoaja,

There has to be some way for Access to know which comments go with which title. If you want only one comment field per title, you could include that field in the table with title.

If you want multiple comment fields for each title (which sounds like your situation), you would have an ID field for each title and then a table with at least two fields, the TitleID and the Comment (memo field).

How do you have your tables structured?

Your original post sounds like you want the memo field to hold a list of titles AND that you want the memo field to be a field which the users can "add whatever they want".

Could you clarify which it is you're after?

Thanks,



John

Use what you have,
Learn what you can,
Create what you need.

 

[naoaja]

Thanks for staying with me.

The first table has Title, ProjectNo, TitleDate, Author, etc. Works fine.

Then the next table has the fields Libraries, LibUpdate, etc.

Libraries is a memo field. What I want is for it to be "preloaded" to say, "This Library contains: Title1, Title2, Title3..." etc. with the titles that matched a certain ProjectNo.
I thought that I would need to run a query based on the table with the Title field that was based on the ProjectNo and then use this to go into the library fields. The query works fine -- it will bring up all the titles associated with the ProjectNo, but when I try to use the query as the recordsource for the memo field, it will only put in the first title, not all of them. (It says, "This library contains: Title1" even though there are several titles in the query result.

Does this make more sense?
thanks again,
Naoaja

 

[BoxHead]

fneilly had the answer for you, I think.

Paste the following function into a new module and save it.

In a query on your first table, add the ProjectNo field and in the column next to that enter:

Project Titles:ConcatField("[Title]",", " ,"firstTableName","[ProjectNo] = " & [ProjectNo])

Replace 'firstTableName' with the actual name of your first table.

This will concatenate the titles for each ProjectNo with a comma in between each.

There really isn't any reason to store this new value in a table, however. You would have to update the table whenever an update was made and that is what queries do by definition.

Public Function ConcatField(MyFld as String, MyBreak As String, _ 
TblQryName as String, Optional MyCrt As String) As String

Dim myString As String, strSql As String
Dim db as DAO.Database, rst as DAO.Recordset

If myCrt = "" Then
StrSql = "SELECT " & myFld & " FROM " & TblQryName & ";"
Else
StrSql = "SELECT " & myFld & " FROM " & TblQryName & "  WHERE " & MyCrt & ";"
End If

Set db = CurrentDb
Set rst = db.Openrecordset(strSql, dbOpenDynaset)

Do Until rst.EOF
If InStr(myString, rst.Fields(0) & MyBreak) = 0 Then
MyString = myString & rst.Fields(0) & myBreak
End If
Rst.MoveNext
Loop

If Len(myString) > Len(myBreak) Then
ConcatField = Left(myString, Len(myString)-Len(MyBreak))
Else
ConcatField = myString
End If

End Function

When you paste this into a new module, compile to see if it is being read correctly. Sometimes with the copying and pasting process, you'll have to replace the quotation marks and the minus sign in the fifth to last line.

HTH


John

Use what you have,
Learn what you can,
Create what you need.

 

[naoaja]

THANK YOU!!
THANK YOU!!

This is wonderful.

Naoaja

 

[BoxHead]

Glad to help!

John

Use what you have,
Learn what you can,
Create what you need.