Gathering data from all records in subform into unbound text field

[ReWeFe]

Hello there,

I have a form with Authors. There is a linked subform with publications.

Now I would really like to add an unbound field on the main form, or the subform if it's easier, that goes through all the publications for the Author in view, and put the data (concatenate) in the unbound text field. There I can then copy and paste in other applications (word processing, email etc.) if needed.

I know how to make the unbound field and how to put data in it. But I don't know how to go through all the records in the subform and only get data that matches the particular Author in view. I guess it would be best/easiest to base the subform of a query instead of the table.

Let me know if I need to explain myself a little more.

Thanks

 

[TonyJollans]

Hi ReWeFe,

I don't know of any easy way to run through the subform fields but a function could quite easily run through a recordset which used the same table or query as the subform and concatenated all the data you wanted. Making some assumptions about your data names you could use something like ..

Function Concatenate(AuthorId As Long) As String

    Dim Details As Recordset
    Dim Selection As String

    Selection = "Select Publications.Name " & _
                "From   Publications " & _
                "Where  Publications.AuthorId = " & AuthorId & ";"

    Set Details = CurrentDb.OpenRecordset(Selection, dbOpenDynaset)
    
    Do While Not Details.EOF
        Concatenate = Concatenate & Details!Name & vbNewLine
        Details.MoveNext
    Loop

    Details.Close

End Function

As Data Source for your unbound control put =Concatenate(AuthorId)

Enjoy,
Tony

 

[ReWeFe]

Looks very good, I'm going to try it out right away.

One question though, for your example to work, should I put the unbound field on the mainform or does it have to be on the subform?

Thanks

 

[ReWeFe]

Ok, I your example code in a module. An unbound field on my main form.

However I get the following error:

Run-time error '3001':

Invalid argument.

and when I click "Debug" it highlight this code:

Set Details = CurrentDb.OpenRecordset(Selection, dbOpenDynaset)

Do you have any idea why?

Thanks

 

[ReWeFe]

Here is the complete code in my database, as per your example, which I actually understand fairly well, table and field names a little different. I keep getting error descriped in my previous post.

Function Concatenate(AuthorID As Long) As String
Dim Details As Recordset
Dim Selection As String

Selection = "Select tblMagazineAppearances.txtMagazineName " & _
"From   tblMagazineAppearances " & _
"Where tblMagazineAppearances.numAuthorFKID = " & AuthorID & ";"

Set Details = CurrentDb.OpenRecordset(Selection, dbOpenDynaset)

Do While Not Details.EOF
    Concatenate = Concatenate & Details!txtMagazineName & vbNewLine
    Details.MoveNext
Loop

Details.Close

End Function

 

[TonyJollans]

Hi,

To answer the easy question first, you are right to put your unbound control on the main form. Unfortunately, your main problem is one, I'm afraid, I can't help with.

I cut and pasted your code (which looks good to me) and created a dummy table with your names (numAuthorFKID as Number, tblMagazineName as Text) and passed numeric values from a control on my mainform to it and it worked fine.

Invalid Argument means you are passing something to OpenRecordset which it can't understand. The SQL is a string - it should be happy with that whether or not it's valid SQL. dbOpenDynaset should equate to 2 - it is an Access constant so there is nothing obviously wrong with that - but if it were invalid (=22 or something) that would cause a problem.

I have Access 97. If you have a later version (or earlier) there may be something different you need to do but I don't know what.

Sorry not to be more helpful. Will post back if something comes to me. Maybe someone else has an idea.

Tony

 

[ReWeFe]

Tony you been very helpful already.

I have Access 2000 sp3 on a Windows 2000 OS sp3.

Maybe it's somekind of ActiveX control or add-in I need.

Anyways I'll try and install Access 97 this morning and try it there.

Thanks