How to concatenate multiple rows into a single string and place into unbound textbox in Microsoft Access 2007?

Hello Experts,
I think this is a tough one, I will do my best to explain.
I am pulling data from our MIS database into our internal database but first showing on a form.
I need to extract data from 2 tables in the MIS database : tblShipping and tblNotes.  These tables have a one to many relationship, there can be mutliple records of notes for 1 shipping address.
On my form I need to show the all related records from tblNotes in a single string in a unbound textbox, In addition, they need to be in order.  My form is a continuous form(if that makes a difference).  Currently my form is just querying the tblShipping.
I feel like I may need to write a loop to grab the the multiple notes records... but I am not sure how.
example:
tblShipping:
[Name] John Doe
[Address] Here
[ST]  FL
[NoteID] 1

tblNotes:
[FK_NoteID] 1
[NoteSequence] 1
[Notes] this is

[FK_NoteID] 1
[NoteSequence] 2
[Notes] a note

[FK_NoteID] 1
[NoteSequence] 3
[Notes] in sequence

So hopefully have my form show
[FormName] John Doe
[FormAddress] Here
[FormST]  FL
[FormNote] This is a note in sequence

Thanks
T
future_hoovesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

josephwalshCommented:
On the OnCurrent event on the form use the followng code :
Private Sub Form_Current()
On Error GoTo Error_Form_Current

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strsql As String
   
    Set dbs = CurrentDb()
   
    With Me
        strsql = "SELECT [Notes] FROM tblNotes WHERE [FK_NoteID] = " & !txtNoteID
        ' Note !txtNoteID is assumed to be the control that is bound to [NoteID]
        strsql = strsql & " ORDER BY [NoteSequence]"
        Set rst = dbs.OpenRecordset(strsql, dbOpenDynaset, dbSeeChanges)
        ' Clear the unbound testbox control
        !txtNotesSummary = ""
        ' The above is assumed to be the name of the unbound control
        If Not rst.BOF And Not rst.EOF Then
            !txtNotesSummary = !txtNotesSummary & rst![Notes] & " "
            rst.MoveNext
        End If
        .Refresh
    End With
    rst.Close
    dbs.Close
    Set rst = Nothing
    Set dbs = Nothing
   
Exit_Form_Current:
    Exit Sub
   
Error_Form_Current:
    MsgBox "Error No :" & Err.Number & ", Description : " & Err.Description
    On Error Resume Next
    rst.Close
    dbs.Close
    Set rst = Nothing
    Set dbs = Nothing
    Resume Exit_Form_Current
End Sub
0
future_hoovesAuthor Commented:
hhmm the unbound txtNotesSummary is only returning the "this is" portion.  So it's only returning the first record.

t
0
josephwalshCommented:
Workaround as follows :
Also Make sure that the textbox control is large enough to hold the data

On the OnCurrent event on the form use the followng code :
Private Sub Form_Current()
On Error GoTo Error_Form_Current

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strsql As String
    Dim strMessage as string
   
    Set dbs = CurrentDb()
   
    With Me
        strsql = "SELECT [Notes] FROM tblNotes WHERE [FK_NoteID] = " & !txtNoteID
        ' Note !txtNoteID is assumed to be the control that is bound to [NoteID]
        strsql = strsql & " ORDER BY [NoteSequence]"
        Set rst = dbs.OpenRecordset(strsql, dbOpenDynaset, dbSeeChanges)
        ' Clear the unbound testbox control
        strMessage = ""
        ' The above is assumed to be the name of the unbound control
        If Not rst.BOF And Not rst.EOF Then
            strMessage = strMessage & rst![Notes] & " "
            rst.MoveNext
        End If
        !txtNotesSummary=strMessage
        .Refresh
    End With
    rst.Close
    dbs.Close
    Set rst = Nothing
    Set dbs = Nothing
   
Exit_Form_Current:
    Exit Sub
   
Error_Form_Current:
    MsgBox "Error No :" & Err.Number & ", Description : " & Err.Description
    On Error Resume Next
    rst.Close
    dbs.Close
    Set rst = Nothing
    Set dbs = Nothing
    Resume Exit_Form_Current
End Sub

0
josephwalshCommented:
Ignore Last Posting
I was not scrolling through the notes record

On the OnCurrent event on the form use the followng code :
Private Sub Form_Current()
On Error GoTo Error_Form_Current

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strsql As String
   
    Set dbs = CurrentDb()
   
    With Me
        strsql = "SELECT [Notes] FROM tblNotes WHERE [FK_NoteID] = " & !txtNoteID
        ' Note !txtNoteID is assumed to be the control that is bound to [NoteID]
        strsql = strsql & " ORDER BY [NoteSequence]"
        Set rst = dbs.OpenRecordset(strsql, dbOpenDynaset, dbSeeChanges)
        ' Clear the unbound testbox control
        !txtNotesSummary = ""
        ' The above is assumed to be the name of the unbound control
        If Not rst.BOF And Not rst.EOF Then
            do until rst.eof
               !txtNotesSummary = !txtNotesSummary & rst![Notes] & " "
               rst.MoveNext
            loop
        End If
        .Refresh
    End With
    rst.Close
    dbs.Close
    Set rst = Nothing
    Set dbs = Nothing
   
Exit_Form_Current:
    Exit Sub
   
Error_Form_Current:
    MsgBox "Error No :" & Err.Number & ", Description : " & Err.Description
    On Error Resume Next
    rst.Close
    dbs.Close
    Set rst = Nothing
    Set dbs = Nothing
    Resume Exit_Form_Current
End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
future_hoovesAuthor Commented:
Absolutely Perfect!  Thank you very much.

Terry
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.