Too few parameters. expected 1 Error

[CarlM1975]

Hi all

I have some vba code as follows:

Private Sub Search_button_Click()
'Dim dbs As CurrentDb
Dim rc As DAO.Recordset
Dim Budget As Double
Dim BestValue As Double
Dim Spend As Double
Dim Difference As Double
Dim SQLTXT As String
Dim ManagerID As String

If Len(Me.OD_Combo) + Len(Me.BDM_Combo) = 0 Then MsgBox ("Please Select A Manager"): Exit Sub

'Set dbs = CurrentDb()

If Me.OD_Combo.Value <> "" Then ManagerID = Me.OD_Combo.Value Else ManagerID = Me.BDM_Combo.Value

SQLTXT = "Select * from Qry_Budget where ManagerID =" & ManagerID
Set rc = CurrentDb.OpenRecordset(SQLTXT, dbopendynaset, dbseechanges)
Budget = rc!SumOfInitialBudget
Me.Budget = Budget

rc.Close

Spend = 0

SQLTXT = "Select * from Qry_JobCost where ManagerID = " & ManagerID
Set rc = CurrentDb.OpenRecordset(SQLTXT, dbopendynaset, dbseechanges)

Spend = 0
BestValue = 0
If rc.EOF Then
'MsgBox ("No Jobs Found!")
Else
rc.MoveLast
rc.MoveFirst

Me.Progress.Visible = True
Me.Progress.Max = rc.RecordCount
Me.JobCount = rc.RecordCount

While Not rc.EOF




Spend = Spend + rc!expr1


Me.Progress.Value = rc.AbsolutePosition: DoEvents
rc.MoveNext

Wend

End If

rc.Close
Set rc = Nothing

Difference = Budget - Spend
Me.Spend = Spend
Me.Available = Difference
If Me.Available < 0 Then Me.Available.BackColor = 255 Else Me.Available.BackColor = 16777215
Me.Progress.Visible = False

Spend_Perc = Int((Spend / Budget) * 100)

Me.LBL_PERC.Caption = Str(Spend_Perc) & "%"

The error occurs on line

Set rc = CurrentDb.OpenRecordset(SQLTXT, dbopendynaset, dbseechanges)

I can run the query "Qry Budget" fine in normal view but when I run it from the sql i get the too few parameters message. The query references to a text box on a form

Any help much appreciated

Carl.

 

[namliam]

It is trying to execute this query:
SQLTXT = "Select * from Qry_Budget where ManagerID =" & ManagerID

Which it cannot because
a) ManagerID is empty
b) ManagerID is not a number

Good luck

 

[CarlM1975]

Manager ID is a number and when you debug and hover over the manager ID it displays the ID correctly so thi scan not be causing the issue. I believe the problem lies in the query because when it is like this:

SELECT dbo_SGP_MT1Managers.ManagerName, dbo_SGP_MT1ManagerOccupantLink.ManagerID, Sum(dbo_Periods.InitialBudget) AS SumOfInitialBudget
FROM dbo_SGP_MT1Managers INNER JOIN (dbo_SGP_MT1ManagerOccupantLink INNER JOIN dbo_Periods ON dbo_SGP_MT1ManagerOccupantLink.OccupantID = dbo_Periods.OccupantID) ON dbo_SGP_MT1Managers.ManagerID = dbo_SGP_MT1ManagerOccupantLink.ManagerID
WHERE (((dbo_Periods.Period)<=[forms]![form1]![txtperiod]) AND ((dbo_SGP_MT1Managers.ManagerTypeID)=5) AND ((dbo_Periods.YearNo)=2009))
GROUP BY dbo_SGP_MT1Managers.ManagerName, dbo_SGP_MT1ManagerOccupantLink.ManagerID;

It does not work however replace WHERE (((dbo_Periods.Period)<=[forms]![form1]![txtperiod]) with WHERE (((dbo_Periods.Period)<=3) it works fine

Carl.

 

[LPurvis]

The cause is well documented in forums and elsewhere. (It's gotta be one of the most often asked questions).
For example, have a look here - http://www.access-programmers.co.uk/forums/showthread.php?p=759878#post759878.

(I'd wager you'll end up going with Brent's preferred method. I only use that implementation sometimes - when I'm feeling lazy ;-)