Getting data from tables and put it into a template

Question

I'm sitting with a word template and I have to generate the template via Access 2007. I've the following code:

Public Function CH05_Generate()

Dim WordApp As Word.Application
Dim Doc As Word.Document
Dim WordPath As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sql As String

Set db = CurrentDb()
Set rst = db.OpenRecordset("Projektdata", dbOpenDynaset)


WordPath = "Where my word template is"

Set WordApp = CreateObject("Word.Application")
Set Doc = WordApp.Documents.Add(WordPath)

With Doc

    .FormFields("PName").Result = [Projektnavn]
    .FormFields("text").Result = Forms![TD-E-PM200-CH05]!Kommentar
    .FormFields("S3").Result = Forms![TD-E-PM200-CH05]!sub.Form!Q1
    .FormFields("S4").Result = Forms![TD-E-PM200-CH05]!sub.Form!Q2
    .FormFields("S5").Result = Forms![TD-E-PM200-CH05]!sub.Form!Q3
    .FormFields("S6").Result = Forms![TD-E-PM200-CH05]!sub.Form!Q4
    .FormFields("S7").Result = Forms![TD-E-PM200-CH05]!sub.Form!Q5
    .FormFields("S8").Result = Forms![TD-E-PM200-CH05]!sub.Form!Q6
    .FormFields("S9").Result = Forms![TD-E-PM200-CH05]!sub.Form!Q7
    .FormFields("S10").Result = Forms![TD-E-PM200-CH05]!sub.Form!Q8
    .FormFields("S11").Result = Forms![TD-E-PM200-CH05]!sub.Form!Q9
    .FormFields("S12").Result = Forms![TD-E-PM200-CH05]!sub.Form!Q10
    .FormFields("S13").Result = Forms![TD-E-PM200-CH05]!sub.Form!Q11
    .FormFields("S14").Result = Forms![TD-E-PM200-CH05]!sub.Form!Q12
    .FormFields("S15").CheckBox.Value = Forms![TD-E-PM200-CH05]!sub.Form!Q13
    .FormFields("S16").CheckBox.Value = Forms![TD-E-PM200-CH05]!sub.Form!Q14
    .FormFields("S17").CheckBox.Value = Forms![TD-E-PM200-CH05]!sub.Form!Q15
    .FormFields("S18").Result = Forms![TD-E-PM200-CH05]!sub.Form!Q16
    .FormFields("S19").Result = Forms![TD-E-PM200-CH05]!sub.Form!Q17
    .FormFields("S20").Result = Forms![TD-E-PM200-CH05]!sub.Form!Q18        
End With

WordApp.visible = True
WordApp.Activate
WordApp.ActiveDocument.Protect wdAllowOnlyFormFields, True

End Function

What I need help with is to get "Projektnavn" from the table named "Projektdata", but I have no idea how to get the data from it. There are many "projektnavn", so maybe I should do some sort of lookup? As you can see there they all have a sagsnr, maybe that helps?

Olle Sjögren · Answer 1 · 2015-12-07T14:27:51.673

0

The contents of the recordset rst depends on how the query Projektdata is designed. If your query returns a single row, you can retreive the data by simply using the column name:

If Not rst.EOF Then
    .FormFields("PName").Result = rst("YOUR_COLUMN_NAME")
End If

The If statement is needed in case the query does not return any rows.

EDIT

I don't know how the query Projektdata is designed. In order to make it only return the correct row, you will have to modify it to accept a parameter to identify which row to return. You can pass a parameter for sagsnr if it is unique for each row. I've expanded the code sample and added some cleanup:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef

Set db = CurrentDb()

Set qdf = db.QueryDefs("Projektdata")
qdf!plSagsnr = 1  'Add parameter value to only fetch data where sagsnr is 1
Set rst = qdf.OpenRecordset

If Not rst.EOF Then
    Debug.Print rst("Field1")
End If

rst.Close
qdf.Close
db.Close

Set rst = Nothing
Set qdf = Nothing
Set db = Nothing

edited Dec 07 '15 at 14:27

answered Dec 07 '15 at 12:38

Olle Sjögren

5,315
3
31
51

That works, but I didn't get the result I was looking for. I got the first "projektnavn" in the column, but I need the specific name. Maybe it should do some sort of lookup? – Uppah Dec 07 '15 at 12:50
You will either have to design your query to only return the correct row or sort through all the rows in the `CH05_Generate` sub. I recommend making sure the query returns what you want, it is probably easier. – Olle Sjögren Dec 07 '15 at 12:54
Could you maybe help me with it? I'm kinda lost here. – Uppah Dec 07 '15 at 13:34
See edit. You have not shown the code for the query. You will have to modify it to accept a parameter for sagsnr and then select the correct row based on the parameter. – Olle Sjögren Dec 07 '15 at 14:16
Also, see [this Q+A](http://stackoverflow.com/a/16571156/1490783) for more information on queries and parameters. – Olle Sjögren Dec 07 '15 at 14:21

Getting data from tables and put it into a template

1 Answers1