The DREADED "Too Few Parameters..." error message!

[Bill Mitchell]

Hi,

I am trying to do a simple export to a .txt file from the results of a Query
call "ExportDocES". I am doing this from the database window.

When I run the query it works just fine. I get no #Error message in any
columns. However, when I try to Export the query to the .txt file, I get the
hated "Too Few Parameters given, 1 expected..." error. I've always hated
that message because it usually makes no sense.

Anyway, anyone have any idea on why my query works just fine but I get this
error when trying to export?

Thanks in advance!

Bill

 

[Allen Browne]

Does your query refer to a text box on a form?

If so, it requires the Expression Service to execute the query. The service
is available if you run the query directly from the database window, or if
you use it as the RecordSource for a form or report. It is not available if
you open a recordset or work with the data programmatically. Anything that
Access cannot resolve it calls a parameter.

So, the simplest solution is to create a SQL string and concatenate the
value from the control into the string. Example:

Dim strSql As String
Dim rs As DAO.Recordset
strSql = "SELECT * FROM MyTable Where Surname = """ & Forms!Form1!Text1 &
""";"
Set rs = dbEngine(0)(0).OpenRecordset(strSql)

Note that this will NOT work:
strSql = "SELECT * FROM MyTable Where Surname = Forms!Form1!Text1 ;"