Filling Word Form Using Access Data and SQL Recordset

[dgj32784]

Alright. Frankly this is over my head. I've tried to read-up on the web and have attempted to customize some code I found for the subject. I'm getting the following error message: "3061Too few parameters. Expected 1."



I believe this means that for some reason txtSurveyID (a control on my Access form) is not being "passed" to the SQL. This code is definitely not clean and I've commented out some stuff that was in it from the original source not thinking I really need it. And there is still probably some stuff that is not needed. What am I missing here? Is my syntax for wrong in the SQL statement? The corresponding field in the Word form is obviously "wrdSurveyID".

Private Sub cmdGetFullSiteReport_Click()


    Dim DOC_PATH As String
    Dim DOC_NAME As String
    Dim appWord As Word.Application
    Dim doc As Word.Document
    Dim rst As DAO.Recordset
    Dim strSQL As String
    'Dim SurveyID As String
    
    DOC_PATH = "C:\aaa\projects\bmp_implementation_survey\2012\database\reports\"
    DOC_NAME = "20130109_bmp_imp_full_site_report_template.doc"
    'SurveyID = Me!txtSurveyID
    
    On Error Resume Next


    Set appWord = GetObject(, "Word.application")
        If Err = 429 Then
            Set appWord = New Word.Application
            Err = 0
        End If
    With appWord
        Set doc = .Documents(DOC_NAME)
            If Err = 0 Then
                If MsgBox("Do you want to save the current document " _
                & "before updating the data?", vbYesNo) = vbYes Then
                .Dialogs(wdDialogFileSaveAs).Show
                End If
                doc.Close False
            End If
    
On Error GoTo ErrorHandler
    
    Set doc = .Documents.Open(DOC_PATH & DOC_NAME, , True)


    If Not IsNull(Me!txtSurveyID) Then
        strSQL = "SELECT tblSurveySites.strSurveyID, tblSurveySites.dtmSurveyDate, tblSurveySites.dtmSurveyTime, tblSurveySites.strSurveyorsFullName, tblSurveySites.strXCoordinate, tblSurveySites.strYCoordinate, tblSurveySiteLocation.strCountyName, tblSurveySiteLocation.strFeatureName, tblSurveySiteLocation.strRoadName, tblSurveySiteLocation.strRiverBasin " & vbCrLf & _
        "FROM tblSurveySites INNER JOIN tblSurveySiteLocation ON tblSurveySites.strSurveyID = tblSurveySiteLocation.strSurveyID " & vbCrLf & _
        "WHERE (((tblSurveySites.strSurveyID)= [txtSurveyID]));"
        Set rst = CurrentDb.OpenRecordset(strSQL)


    'ElseIf Not rst.EOF Then
        'strReportsTo = Nz(rst.Fields(0).Value)
        'rst.Close
    End If


    With doc
        .FormFields("wrdSurveyID").Result = Nz(Me!strSurveyID)
    End With


    .Visible = True
    .Activate


End With


    Set rst = Nothing
    Set doc = Nothing
    Set appWord = Nothing
    
Exit Sub


ErrorHandler:
MsgBox Err & Err.Description


End Sub

[June7]

Which line triggers the error?

[ssanfu]

I believe this means that for some reason txtSurveyID (a control on my Access form) is not being "passed" to the SQL.

You are correct.
If you add a line "Debug.Print strSQL" before the line "Set rst = CurrentDb.OpenRecordset(strSQL)", you can see in the immediate window if the SQL line is formed correctly.

Your WHERE clause is

 "WHERE (((tblSurveySites.strSurveyID)= [txtSurveyID]));"

Try the debug statement first

Then change the line to

 "WHERE (((tblSurveySites.strSurveyID)= " & Me.txtSurveyID & "));"

Check the debug statement


Also, you shouldn't have (don't need) "vbCrLf" in the strSQL string.

[dgj32784]

Thanks for the feedback. With respect to identifying the line of code that triggers the error - pardon my ignorance - but how do I do that?

I think I tried what you suggested John and it was still producing the "3061Too few paramerters. Expected 1." error message.

strSQL = "SELECT tblSurveySites.strSurveyID, tblSurveySites.dtmSurveyDate, tblSurveySites.dtmSurveyTime, tblSurveySites.strSurveyorsFullName, tblSurveySites.strXCoordinate, tblSurveySites.strYCoordinate, tblSurveySiteLocation.strCountyName, tblSurveySiteLocation.strFeatureName, tblSurveySiteLocation.strRoadName, tblSurveySiteLocation.strRiverBasin FROM tblSurveySites INNER JOIN tblSurveySiteLocation ON tblSurveySites.strSurveyID = tblSurveySiteLocation.strSurveyID WHERE (((tblSurveySites.strSurveyID)= " & Me.txtSurveyID & "));"

I then remembered that in a few INSERT statements I used single and double quotes to refer to a control on a form. So I tried that.

strSQL = "SELECT tblSurveySites.strSurveyID, tblSurveySites.dtmSurveyDate, tblSurveySites.dtmSurveyTime, tblSurveySites.strSurveyorsFullName, tblSurveySites.strXCoordinate, tblSurveySites.strYCoordinate, tblSurveySiteLocation.strCountyName, tblSurveySiteLocation.strFeatureName, tblSurveySiteLocation.strRoadName, tblSurveySiteLocation.strRiverBasin FROM tblSurveySites INNER JOIN tblSurveySiteLocation ON tblSurveySites.strSurveyID = tblSurveySiteLocation.strSurveyID WHERE (((tblSurveySites.strSurveyID)= ' " & Me.txtSurveyID & " '));"

This produce the following error message "2465Microsoft Office Access can't find the field 'strSurveyID' referred to in your expression."

Not sure what that's all about. The field most definitely exists. Any guidance would be appreciated.

Thanks,
David

[ssanfu]

Debugging: Read the first two paragraphs after the intro at Chip Pearson's site - http://www.cpearson.com/excel/DebuggingVBA.aspx
(Don't use the STOP command in code)

In the code, click anywhere in this line

DOC_PATH = "C:\aaa\projects\bmp_implementation_survey\2012\da tabase\reports\"

then press the "F9". Go back to the form and run the code. You should be dumped back into the IDE.
Press the "F8" key to execute one line at a time.


Does table "tblSurveySites" have a field named "strSurveyID"?

[June7]

The apostrophe delimiter is need for criteria when field is text datatype. For date/time use # delimiter.

[dgj32784]

Okay, looks like the error is occurring with this line:

.FormFields("wrdSurveyID").Result = Nz(Me!strSurveyID)

And this would be where me fumbling in the dark starts to really become a factor. This was part of the code I've attempted to modify from various online sources. I forget what Nz means, but now reflecting on the error I guess it's because there is no "strSurveyID" on my form. What I need is for this word form field (wrdSurveyID) to be set equal to "strSurveyID" from the strSQL. How to I refer to that value?

[ssanfu]

I now see several things to correct.

    With doc
        .FormFields("wrdSurveyID").Result = Nz(Me!strSurveyID)
    End With

"Me" is a shortcut to "Forms!FormName" and is used on a form. You cannot refer to a query in code using "ME". You have to use the recordset reference, in this case "rst"

You are also missing part of the NZ() function. From Help:

The Nz function is useful for expressions that may include Null values. To force an expression to evaluate to a non-Null value even when it contains a Null value, use the Nz function to return a zero, a zero-length string, or a custom return value.

You are missing the "ValueIfNull" part.

.FormFields("wrdSurveyID").Result = Nz(rst!strSurveyID,ValueIfNull)

"ValueIfNull" should be replaces with what you want to be returned if "strSurveyID" is NULL.

For Instance, if "strSurveyID" is a Long Int, you might want to return a Zero. It would look like this

.FormFields("wrdSurveyID").Result = Nz(rst!strSurveyID,0)

If "strSurveyID" is a string, you might want to return custom text. It would look like this:

.FormFields("wrdSurveyID").Result = Nz(rst!strSurveyID,"Not found. Please re-enter")

You might try

    With doc
        .FormFields("wrdSurveyID").Result = Nz(rst!strSurveyID,0)
    End With

BUT, the main problem is you opened the recordset (rst) , and didn't check to see if there were records returned and handle the error before it gets to the "with DOC" statement

        strSQL = "SELECT tblSurveySites.strSurveyID, tblSurveySites.dtmSurveyDate, tblSurveySites.dtmSurveyTime, tblSurveySites.strSurveyorsFullName, tblSurveySites.strXCoordinate, tblSurveySites.strYCoordinate, tblSurveySiteLocation.strCountyName, tblSurveySiteLocation.strFeatureName, tblSurveySiteLocation.strRoadName, tblSurveySiteLocation.strRiverBasin " & vbCrLf & _
        "FROM tblSurveySites INNER JOIN tblSurveySiteLocation ON tblSurveySites.strSurveyID = tblSurveySiteLocation.strSurveyID " & vbCrLf & _
        "WHERE (((tblSurveySites.strSurveyID)= [txtSurveyID]));"
        Set rst = CurrentDb.OpenRecordset(strSQL)
'not check for records
 If rst.bof and rst.EOF then
    'do something
    ' maybe exit the sub???
end if

Or you could check to see if rst!strSurveyID is NULL and handle the error someway.
You could do this:

    With doc
        If IsNull(rst!strSurveyID) then
           'strSurveyID is NULL
           ' do something
        ELSE
        .FormFields("wrdSurveyID").Result = rst!strSurveyID
       End If
    End With

(don't have to do both)


Sorry this is so long....