too few parameters expected 1 with criteria in query.

[Homegrownandy]

Sorry for the delay, as normal i had something else to do yesterday.



Access - import to word test.zip

Ive removed 99% of the database content leaving only whats relevent and a few test lines.

Please see attached.

[Minty]

I can't see why it doesn't see the form criteria, however if you create the query on the fly based on the current record it works; (The query does, it bombs out as It can't find the word doc...)

Private Sub Command92_Click()




    '-----------------Set up word document to use recordset ------------------
    Dim wrdApp As New Word.Application
    Dim wrdDoc As Word.Document
    Dim filepath As String
    Dim docFile As String
    Dim rst As DAO.Recordset
    '-----------------Set up word document to use recordset ------------------


    '                    --------------------------


    '-----------------define bookmark variables as range -----------------
    Dim test As Word.Bookmarks
    '-----------------define bookmark variables as range ------------------




    '------------------ define query -----------------------------
    Dim db As Database
    Dim sSql As String


    Set db = CurrentDb


    sSql = "SELECT SiteT.Site_ID, SiteT.Site_Name, SiteT.Asset_Type, SiteT.Address_1, SiteT.Address_2, SiteT.Address_3, SiteT.Postcode, ClientT.Company_Name, ClientT.Company_ID, HospitalT.Hospital_Name, HospitalT.Hospital_Postcode, HospitalT.Hospital_Address, HospitalT.Hospital_Telephone, SiteT.lat, SiteT.long "
    sSql = sSql & "FROM HospitalT INNER JOIN (ClientT INNER JOIN SiteT ON ClientT.Company_ID = SiteT.Site_Owner) ON HospitalT.Hospital_ID = SiteT.Hospital_ID "
    sSql = sSql & "WHERE [SiteT].[Site_ID] = " & Me.Site_ID & " "
    sSql = sSql & "ORDER BY SiteT.Site_Name;"


    Set rst = db.OpenRecordset(sSql)


    Debug.Print rst.Fields(1), rst.Fields(2)


    filepath = "C:\Users\ah\Desktop\RAMS AUTOMATION\RAM.docm"








    Set wrdApp = CreateObject("Word.Application")
    wrdApp.Visible = True


    Set wrdDoc = wrdApp.Documents.Open(filepath)


    With wrdDoc


        wrdApp.ActiveDocument.Bookmarks("test").Select
        wrdApp.Selection.Text = rst("Site_ID") & " " & rst("Site_Name")


    End With
    Set rst = Nothing


End Sub

[Homegrownandy]

according to Andre on stack overflow:

A parameter like [Forms]![SurveyRegister_frm]![SurveyID] doesn't get evaluated automatically if you open a recordset in VBA.

I was trying to implement the function provided by him but I was stuck. Your code works perfect, Im sure the SQL is easy enough to understand if I take some time to work it out.

I'm actually quite terrible at VBA the only reason I have a database/other macros is becauseof my persistance and mainly the help from everyone here. So, Once again, thanks to everyone looking into this.

This will save hours of time and allow me to build on it to manage the whole project/ risks through the database.

[Homegrownandy]

Understanding it was much easier than expected. Haha. Appreciate you doing that for me.

[Minty]

No problem - glad to have helped.