Error 3061 - too few parameters

  • Thread starter Thread starter dbguru316
  • Start date Start date
D

dbguru316

I have read many responses for the error code 3061 but to no avail. Below is
the code, and when run get the error message 3061 - too few paramters,
expected 6.

The error is driven by the strSaveFileName parameter in the strSQL variable.
I have added an quote on both sides, still not resolved.

Function AddDocumentFile()
Dim strFilter As String
Dim lngFlags As Long
Dim strSaveFilePath, strSaveFileName, strSQL As String
Dim db As DAO.Database
Set db = CurrentDb
DoCmd.RunCommand (acCmdSaveRecord)
strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")
strSaveFilePath = ahtCommonFileOpenSave(InitialDir:="C:\", _
Filter:=strFilter, FilterIndex:=3, flags:=lngFlags, _
DialogTitle:="Choose the file to add into Document Control")
strSaveFileName = Right(strSaveFilePath, Len(strSaveFilePath) -
InStrRev(strSaveFilePath, "\"))
strSQL = "INSERT INTO tmpDocListing ( DocNumber, DocRev, DocType,
Customer, DocDescription, AddedBy, DocFileName ) " & _
"SELECT [Forms]![frmDocumentAdd]![DocNumber] AS Expr1,
[Forms]![frmDocumentAdd]![DocRev] AS Expr2,
[Forms]![frmDocumentAdd]![DocType] AS Expr3, " & _
"[Forms]![frmDocumentAdd]![Customer] AS Expr4,
[Forms]![frmDocumentAdd]![DocDescription] AS Expr5,
[Forms]![frmDocumentAdd]![AddedBy] AS Expr6, '" & strSaveFileName & "' AS
Expr7;"
db.Execute strSQL, dbFailOnError
Set db = Nothing
End Function
 
I'm not sure if there are other syntax errors or not, but for starters you
are trying to use the expression service
([Forms]![frmDocumentAdd]![DocNumber]) coupled with the db.Execute method.

The Execute method does not evaluate ES values. You will need to "manually"
enter these into the SQL string (or use DoCmd.RunSQL, which does eval the ES,
but for a few reasons Execute is better IMO).

SQL = "SELECT " & Forms("frmDocumentAdd").[DocNumber] & " AS Expr1".... etc
etc

or SQL = "SELECT " & Eval([Forms]![frmDocumentAdd]![DocNumber]) & " AS Expr1"

although you might run into some datatype issues using Eval

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)
 
Many Thanks. That did the trick. Also, since some of the data could have a
space or other symbol, I had to also add an apostrophe before and after each
ES. Example:
"SELECT '" & [Forms]![frmDocumentAdd]![DocNumber] & "' AS Expr1,


Jack Leach said:
I'm not sure if there are other syntax errors or not, but for starters you
are trying to use the expression service
([Forms]![frmDocumentAdd]![DocNumber]) coupled with the db.Execute method.

The Execute method does not evaluate ES values. You will need to "manually"
enter these into the SQL string (or use DoCmd.RunSQL, which does eval the ES,
but for a few reasons Execute is better IMO).

SQL = "SELECT " & Forms("frmDocumentAdd").[DocNumber] & " AS Expr1".... etc
etc

or SQL = "SELECT " & Eval([Forms]![frmDocumentAdd]![DocNumber]) & " AS Expr1"

although you might run into some datatype issues using Eval

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)



dbguru316 said:
I have read many responses for the error code 3061 but to no avail. Below is
the code, and when run get the error message 3061 - too few paramters,
expected 6.

The error is driven by the strSaveFileName parameter in the strSQL variable.
I have added an quote on both sides, still not resolved.

Function AddDocumentFile()
Dim strFilter As String
Dim lngFlags As Long
Dim strSaveFilePath, strSaveFileName, strSQL As String
Dim db As DAO.Database
Set db = CurrentDb
DoCmd.RunCommand (acCmdSaveRecord)
strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")
strSaveFilePath = ahtCommonFileOpenSave(InitialDir:="C:\", _
Filter:=strFilter, FilterIndex:=3, flags:=lngFlags, _
DialogTitle:="Choose the file to add into Document Control")
strSaveFileName = Right(strSaveFilePath, Len(strSaveFilePath) -
InStrRev(strSaveFilePath, "\"))
strSQL = "INSERT INTO tmpDocListing ( DocNumber, DocRev, DocType,
Customer, DocDescription, AddedBy, DocFileName ) " & _
"SELECT [Forms]![frmDocumentAdd]![DocNumber] AS Expr1,
[Forms]![frmDocumentAdd]![DocRev] AS Expr2,
[Forms]![frmDocumentAdd]![DocType] AS Expr3, " & _
"[Forms]![frmDocumentAdd]![Customer] AS Expr4,
[Forms]![frmDocumentAdd]![DocDescription] AS Expr5,
[Forms]![frmDocumentAdd]![AddedBy] AS Expr6, '" & strSaveFileName & "' AS
Expr7;"
db.Execute strSQL, dbFailOnError
Set db = Nothing
End Function
Click to expand...
Click to expand...
 
Back
Top