Concat field related records - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Concat field related records

Andres Joubert

2 years ago

I have three tables that I am working with. I have a source table that stores media sources information (i.e. NYTimes, NBC news, etc.). I have a format table that stores the various media formats sources may use to deliver their media (i.e. video, text, audio, etc.). I also have a junction table since these have a many-to-many relation.

I have a subform that pops up on from a "Add/Edit Sources" form. This subform allows the user to associate format types to the selected source in view, but when the window is closed, the main form does not show the selected options.

I would like to show on the main Add/Edit Sources form all the format types for the selected source in a comma-separated format without having to open the subform of format types. There will be many fields like this in the view so I cannot embed the subform as that would clog up the space. How can I display the various selected formats in a text field in a comma-separated format?

Adam Schwanz @Reply

2 years ago

If I understood the question correctly... I would do something like this (this is probably not the easiest way, I just gravitate to recordsets for things like this. I also don't know your layouts, but I'm assuming this is a single record, Media Source on the page that needs a list of the formats, if this is a subform with multiple Media Sources, this would need to be more advanced.)

I'd just put this in the on current event of the main form

Dim db as database

Dim rs as recordset



Set db = currentdb

set rs = db.openrecordset("Select * From JunctionTable Where MediaSourceID=" & MediaSourceID)



Dim X

X=""

While not rs.eof

If X = "" Then

X= rs!MediaFormat

Else

X=X & ", " & rs!MediaFormat

End If

rs.movenext

wend



TextboxOnMainForm = X



rs.close

db.close

set rs = nothing

set db = nothing

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View