Parameters Expected

  • Thread starter Thread starter rich
  • Start date Start date
R

rich

I am calling an update query to set the current dates
within a recordset on several different records that meet
certain criteria. Here is the code:

strUpdateDate = "UPDATE [S-Signings] SET [S-
Signings].Client_Billed_Date = Date()" & _
"WHERE ((([Forms]![S-Signing Summary]![S-
Signings Subform].[Form]![Loan#])=[Loan#]) AND (([S-
Signings].[Signing Status])=3 Or ([S-Signings].[Signing
Status])=4 Or ([S-Signings].[Signing Status])=5) AND (([S-
Signings].Total_Fee)>0));"
dbs.Execute strUpdateDate

This SQL works fine when I run the actual query, but when
it is in the code, I get an "Expected Parameters 1"
Error. I know it is in the "Where" portion of the SQL
because when I take that out, it updates the date, but
only on the record that has focus. Please tell me why it
will not work in the code if you can, thanks.
 
Access can figure out what the parameter is when the query is run in the
grid. Then Access will inform the Jet Engine what it is.

However, when the same query is run in code, you must tell the Jet engine
what the
parameter is yourself.

This is the slickest way to do it:

With queries that contain parameters that are all references to
controls on open forms, you can simulate the expression service that Access
provides when the queries are run through the user interface, as follows:

Set db = CurrentDb
Set qdf = db.QueryDefs("MyQuery")
Dim prm As Parameter

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset(dbOpenDynaset)
' or qdf.Execute dbFailOnError
 
Back
Top