Open Word Doc - Based on First Row of Filtered Query

[tca_VB]

I'm pretty close and hoping someone can help me.

I would like to click a command button from a form. The command will go to the first row of a query which is filtered by parameter [SelectedPIN] based on PIN_ID from active record on form.

The rest of the command should use the values from the first row to open a word document, update fields based on active record on form, then save and close word document.

Hope you followed that... The error message I get back is "Too Few Parameters. Expected 1"

Thanks in advance!


Private Sub cmdOpenWordDoc_Click()
On Error GoTo Err_cmdOpenWordDoc_Click

Dim oApp As Object
Dim dotString As String
Dim pathString As String
Dim saveString As String
Dim docString As String
Dim db As DAO.Database
Dim nameString As String
'Dim qdfDocs As DAO.QueryDef, might need this
Dim rstUnfiltered As DAO.Recordset
Dim rstFiltered As DAO.Recordset


Set db = CurrentDb()
Set rstUnfiltered = db.OpenRecordset("qryDocstoComplete")
Set rstFiltered = rstUnfiltered.OpenRecordset
rstFiltered.Filter = "[SelectedPIN]=" & Me.PIN_ID


'Go to First Record to Open Document
rstFiltered.MoveFirst
dotString = rstFiltered![Template_Name]
pathString = rstFiltered![Storage_Location] & "\"

Const conTEMPLATE_NAME = "pathString" & "dotString"
'Open Word Instance
Set oApp = CreateObject("Word.Application")
oApp.Visible = True

'Open Template as Document
oApp.Documents.Add Template:=(conTEMPLATE_NAME)
'Code to populate Word Fields
With oApp.ActiveDocument 'need reference to current open word doc or oApp.Selection
.FormFields("crtsPatientName").Result = (Me!Client_Name)
.FormFields("crtsPatientSign").Result = (Me!Client_PIN)
.FormFields("crtsPatientSignDt").Result = Now()
.FormFields("crtsCareNameDt").Result = Now()
End With

' Save the document.
saveString = "Category_Storage" & "\"
docString = rstFiltered![Document_Name]
nameString = rstFiltered![Client_Name]
oApp.ActiveDocument.SaveAs FileName:=saveString & nameString & "_" & docString, FileFormat:=wdFormatDocument, LockComments:=False, Password:="", AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:=False
oApp.ActiveDocument.Close


' Quit Word.
oApp.Quit
' Clear the variable from memory.
Set oApp = Nothing

' Clear other variables from memory.
' Not sure that I have these right below
rstUnfiltered.Close
rstFiltered.Close
Set rstUnfiltered = Nothing
Set rstFiltered = Nothing
Set db = Nothing

Exit_cmdOpenWordDoc_Click:
Exit Sub
Err_cmdOpenWordDoc_Click:
MsgBox Err.Description
Resume Exit_cmdOpenWordDoc_Click

End Sub

[CreganTur]

The error message I get back is "Too Few Parameters. Expected 1"

This error message occurs when you have a SQL statement that uses a WHERE condition, but there is a problem with the parameters that invalidates the SQL statement.

When you debug, what line of code is highlighted when this error occurs?

[tca_VB]

It's not erroring out at a particular code line - that's the strange part. The SQL filter is looking for a parameter to pass in the line:

rstFiltered.Filter = "[SelectedPIN]=" & Me.PIN_ID

When I run the query alone, it asked for the parameter SelectedPIN. I enter 1 and it runs fine. The filter in the VBA should do the same in code and the form should pass the 1 as it is the current record at the time of the button/command click.

SQL where statement: WHERE ((tblPIN.PIN_ID)=[SelectedPIN])

Thanks for any additional input to have this run correctly, or another option. The other thought would be to create a temp table from the query and reference that in the VBA - but it's steps.