incorparating subforms in ExportXML

MikeSpaans

New member
Local time
Today, 05:16
Joined
Jul 13, 2011
Messages
3
Hello,
I have been searching the web and this forum extensively, but haven't found a real solution; so any thoughts on this are most welcome.
I'll try to give enough information here; maybe I'm on the wrong track tackling this ..

Case
I work for a publisher and am in the process of transferring the PDF's of our journal articles to the Dutch Royal Library. They require a XML file per article. I have been building a database for the articles and want to export the XML from there.

Goal
Export function that creates XML files per article (that part I sort of solved) with information from different tables. End results should looking something like this (given to me by the library):
Code:
    <Article>
        <Journal>
            <PublisherName>Informa Healthcare</PublisherName>
            <Place>Leukemia and Lymphoma</Place>
            <JournalTitle>Leukemia and Lymphoma</JournalTitle>
            <IssueTitle>Titel van nummer</IssueTitle>
            <Volume>51</Volume>
            <Issue>2</Issue>
        </Journal>
        <ArticleTitle>Src-family kinases in the development and therapy of Philadelphia
            chromosome-positive chronic myeloid leukemia</ArticleTitle>
        <ActicleID>LS5102</ActicleID>
        <FirstPage>12</FirstPage>
        <LastPage>44</LastPage>
        <Language>EN</Language>
        <AuthorList>
            <Author>
                <FirstName>Shaoguang</FirstName>
                <LastName>Li</LastName>
            </Author>
            <Author>
                <FirstName>Rianne</FirstName>
                <LastName>Koning</LastName>
            </Author>     
        </AuthorList>
    </Article>
Database
The DB consist of 3 [main] tables (article, authors, journal) linked through many-to-many relations via linking-tables. There's a form[frmMain] that uses the article table and has two sub forms with the information from the other tables: authors can have written multiple articles and an article can have more than one author. Just as one journal contains multiple articles.
I export from the form via this VBA code, where I already tried to use CreateAdditionalData:
Code:
Private Sub Export_Click()
 Dim rsR As DAO.Recordset
 
 Set rsR = CurrentDb.OpenRecordset("tblArticle", dbOpenSnapshot)
 Set objOtherTbls = Application.CreateAdditionalData
 
     ' Add the tables to the data to be exported.
     objOtherTbls.Add "tblAuthor"
   
 Do Until rsR.EOF
   Application.ExportXML acExportForm, "frmMain", _
    Application.CurrentProject.Path & "\" & rsR.Fields("ArticleID").Value & ".xml", , , , , , _
     "[ArticleID] = " & rsR.Fields("ArticleID").Value, objOtherTbls
   rsR.MoveNext
 Loop
 rsR.Close
End Sub
That now generates this kind of files
Code:
<tblArticle>
<ArticleID>1</ArticleID>
<ArticleTitle>Vrije tijd in het Rome van de Keizertijd. Plebs en het feest</ArticleTitle>
<FirstPage>3</FirstPage>
<LastPage>23</LastPage>
<Language>1</Language>
<PublicationType>1</PublicationType>
<PubState>OpenAccess</PubState>
</tblArticle>
<tblAuthor>
<AuthorID>1</AuthorID>
<AuthorFirstName>Anita</AuthorFirstName>
<AuthorLastName>Boonekamp-Van Iterson</AuthorLastName>
</tblAuthor>
<tblAuthor>
<AuthorID>2</AuthorID>
<AuthorFirstName>Hans</AuthorFirstName>
<AuthorLastName>Wijn</AuthorLastName>
</tblAuthor>
<tblAuthor>
<AuthorID>3</AuthorID>
<AuthorFirstName>Spaans</AuthorFirstName>
<AuthorLastName>Mike</AuthorLastName>
......
</tblAuthor>
</dataroot>
Problems

  1. The exported XML files do not only contain the authors of that article but the whole list: all the authors in the DB. How do I limit the list to only those shown in the subform? It seems that there is no link now.

    Additional:
  2. How can I select the fields to be exported from the aditionalData? i.a. I don't need the AuthorID, just the names.
  3. Some of the other values [taken from supporting tables] are now showing as the ID's instead of the text values i.a Language.
That's quite some info I realise :o. Any thoughts on this are much appreciated!:D
 

Attachments

Users who are viewing this thread

Back
Top Bottom