Formatting SQL queries in MS Access

For what it's worth, I can repeat your error message if I declare a parameter and don't assign it:

strSQL = "parameters [CID] number; " & _
   "SELECT * FROM VolatilityOutput WHERE CurveID=" & CurveID & " ORDER BY MaturityDate"

Set rs = CurrentDb.OpenRecordset(strSQL, Type:=dbOpenDynaset, Options:=dbSeeChanges)

Gives me the same thing you are reporting:

Too Few Parameters. Expected 1

I think you might be able to kill two birds with one stone here -- fix your error and use parameters, the way several on this thread are suggesting. This is an example of how you would invoke the parameter:

Sub SampleReadCurve()

  Dim rs As Recordset
  Dim iRow As Long, iField As Long
  Dim strSQL As String
  Dim CurveID As Long
  Dim MarkRunID As Long
  Dim ZeroCurveID As String
  Dim qry As QueryDef

  CurveID = 15

  strSQL = "parameters [CID] number; " & _
     "SELECT * FROM VolatilityOutput WHERE CurveID = [CID] ORDER BY MaturityDate"

  Set qry = CurrentDb.CreateQueryDef("GetCurve", strSQL)
  qry.Parameters("CID") = CurveID
  Set rs = qry.OpenRecordset

  CurrentDb.QueryDefs.Delete ("GetCurve")

End Sub

Bear in mind this creates the query definition and clobbers it each time the sub runs, which is not best practice. I would change this to declare the query ahead of time (through normal Access, not VBA) and invoke it within the sub, leaving it there for the next time you need it:

Set qry = CurrentDb.QueryDefs("GetCurve")

As a final note, among the numerous advantages of using the bind variable / parameter are that datatypes are managed. This means if CurveID was a date or a string, this methodology would still work. You would not need to change the SQL to include quotes or do any special handling for date formats -- the bind variables take care of that for you. This means if your string was:

I'd like to say "hello"

(both single and double quotes), there would be no special handling required. Pretty awesome, right?