Automation Access-Word (wrestling with code... and he's winning!)

[davideitaly]

Hi all. I am new in this forum and a newbie on Access (so please be kind and clear to me). I am from Italy, and I am building a database for my office.
In one of my forms I have three buttons leading to a word model (.odtx) with bookmarks. Through an automation, Access populate the Word model with the records included in the main form (based on a teachers anagraphical data table) and in a related subform (based on a lectures and classes for each teacher data table). Everything looks like that (fake data as example, three buttons right side):





The code managing the automation Access-Word is the following (I kept only the essential lines, without all bookmarks repetition):

Private Sub ESPORTA_OCCASIONALE_Click()
Dim Wrd As Word.Application, Doc As Word.Document
Dim Rst As DAO.Recordset
Dim Modello As String, NomeFile As String, i As Integer
Dim Record As String, SQL As String
Dim Tbl As String * 1
Dim TotRiga As Currency, TOTALE As Currency
Dim ReplSel As Boolean
    
     Modello = CurrentDb.Name
    Modello = Left(Modello, Len(Modello) - Len(Dir(Modello))) & "modello_occasionale.dotx"

    On Error Resume Next 'gestione errori step by step
    Set Wrd = GetObject(, "Word.Application")
    If Err.Number = 429 Then
    Set Wrd = CreateObject("Word.Application")
    End If
    
    On Error GoTo 0
    
    Wrd.Visible = True
    Wrd.Activate
    ReplSel = Wrd.Options.ReplaceSelection
    Wrd.Options.ReplaceSelection = True
    
    Set Doc = Wrd.Documents.Add(Modello)
    Doc.Activate
    
    Doc.Bookmarks("Nome").Select
    Wrd.Selection.TypeText Me.Nome

    Doc.Bookmarks("Cognome").Select
    Wrd.Selection.TypeText Me.Cognome
    
    'ECC...

SQL = "SELECT * FROM Anagrafica_art_corsi WHERE ID_Anagrafica_docenti =" & Me.ID_Anagrafica_docenti & _
          " ORDER BY ANNO, DATA_CONTRATTO;"

Set Rst = CurrentDb.OpenRecordset(SQL)

With Rst
            TOTALE = 0
            .MoveLast
            While Not .EOF

    Doc.Bookmarks("Corso").Select
    Wrd.Selection.TypeText !Corso
      
    Doc.Bookmarks("Materia").Select
    Wrd.Selection.TypeText !MATERIA
 
                .MoveNext 'passa al prossimo record
            Wend 'Not .EOF
        End With 'Rst
        Rst.Close: Set Rst = Nothing

    Wrd.Application.WordBasic.MsgBox "Esportazione terminata", "Esportazione dati da Access"
End Sub

Everything works really well for all fields-bookmarks except the last two: "Corso" and "Materia". Both are managed in the source table by two different comboboxes related to two different tables:

1) Anagrafica corsi, three columns: ID_Anagrafica_corsi; Course abbreviation; Course name full lenght



2) Anagrafica materie, two columns: ID_Anagrafica_materie; Nome_materia



These are the relations between them all:



Given all that, my problem is that when the automation meets the two comboboxes, instead of populating Word with the data I need (full lenght name course, and name of the subject), it uses in one case the course abbreviation and in the other case the ID number.
It's like the automation didn't manage to get into the comboboxes columns and choose the right one to take the data from to populate the word .dotx
Useless so far to specify the needed columns like, for example:

- !Corso.Column(1) or
- Me.Corso.Column(1) or similar (i tried several solutions alike)

Probably because of my weak skills in VBA, I can't understand if the problem is in the Wrd.Selection.TypeText code, or in the way I defined the new SQL or... what?

Italian forums on Access didn't find any solution, except insulting me for my weak skills. Is there anyone around the world who can understand my explanation and give a help?

Sorry if I described something in the wrong way or with the wrong terms... Thank you in advance.

Davide

[pbaldy]

Your code is (likely) fine, your SQL needs tweaking. You need to join to those 2 tables so you can make the full text field a field in your recordset. Play with a query in design view and then you can copy the relevant SQL changes to your code.

[Gicu]

Hi Davide,

Your relationships look a bit weird. I would have expected the Anagrafica_Corsi lookup table to be joined to the CORSO field in the Anagrafica_art_corsi and similarly the ID_anagrafica_materie to be joined to the MATERIA field. As Paul said you need to bring the fields in your recordset before you can use them to populate Word with them. Right now you only have the fields from the Anagrafica_art_corsi table with looks like both contain the code and ID not the full names.

Cheers,
Vlad

[pbaldy]

That does look odd, I hadn't looked at the relationship window.

[pbaldy]

By the way, welcome to the site and I got a chuckle out of the thread title.

[davideitaly]

Your code is (likely) fine, your SQL needs tweaking. You need to join to those 2 tables so you can make the full text field a field in your recordset. Play with a query in design view and then you can copy the relevant SQL changes to your code.

Thanks for advising. I am trying to understand with my weak competences your suggestions.
You're saying I should join in a query the 2 tables: are you meaning the ones behind form and subform? Or should I add in the query also the two used for lookup?
Once joined I should refer to the query in the SQL in the code, did i got it right?
Thanks again.

[davideitaly]

Hi Davide,

Your relationships look a bit weird. I would have expected the Anagrafica_Corsi lookup table to be joined to the CORSO field in the Anagrafica_art_corsi and similarly the ID_anagrafica_materie to be joined to the MATERIA field. As Paul said you need to bring the fields in your recordset before you can use them to populate Word with them. Right now you only have the fields from the Anagrafica_art_corsi table with looks like both contain the code and ID not the full names.

Cheers,
Vlad

Hi Vlad! Actually at the beginning relations was as you suggested. Then a guy in an Italian forum suggested me to chance them as they are now. I was not so persuaded it was a logic solution, but he sounded wiser than me, so I tried. Actually it didn't solve anything, so now I'll get back establishing the original relations, the way you suggested. Thanks for your advise!

[pbaldy]

Thanks for advising. I am trying to understand with my weak competences your suggestions.
You're saying I should join in a query the 2 tables: are you meaning the ones behind form and subform? Or should I add in the query also the two used for lookup?
Once joined I should refer to the query in the SQL in the code, did i got it right?
Thanks again.

No, I mean the table currently in your recordset and the tables that contain the text value you want. Generally:

SELECT Table1.FieldName, Table2.TextFieldName FROM Table1 INNER JOIN Table2 ON Table1.KeyField = Table2.KeyField WHERE..."

I don't really understand your tables to be more specific, but perhaps you can post the updated relationships?

[davideitaly]

No, I mean the table currently in your recordset and the tables that contain the text value you want. Generally:

SELECT Table1.FieldName, Table2.TextFieldName FROM Table1 INNER JOIN Table2 ON Table1.KeyField = Table2.KeyField WHERE..."

I don't really understand your tables to be more specific, but perhaps you can post the updated relationships?

Ok, got it. Thank for your advise. Actually I needed to make a query, and I did it including all the involved tables: the two sources of mask and submask, and the other two used for lookup.
Then I modify the original SQL adding the one resulting from the query:

SQL  = "FROM Anagrafica_materie INNER JOIN (Anagrafica_corsi INNER JOIN  (Anagrafica_docenti INNER JOIN Anagrafica_art_corsi ON  Anagrafica_docenti.[ID_Anagrafica_docenti] =  Anagrafica_art_corsi.ID_Anagrafica_docenti) ON  Anagrafica_corsi.Sigla_corso = Anagrafica_art_corsi.CORSO) ON  Anagrafica_materie.ID_materie = Anagrafica_art_corsi.MATERIA" & _
      "ORDER BY Anagrafica_art_corsi.DATA_CONTRATTO;"

Anyway that was not enough. I obviously had to change the set recordset line from this:

Set Rst = CurrentDb.OpenRecordset(SQL)

To that:

Set Recordset = CurrentDb.OpenRecordset("Select * from sqlbookmark")

where "sqlbookmark" is the name of the new query I did.

Now it works perfectly.

Thank you again.

[pbaldy]

Happy to help!