Error - too few parameters. Expected 1

[blindlemonray]

Hi All,

this is the code I am using and I have trawling the forums to try and find an answer but I am stumped. I keep getting error message "too few parameters. expected 1" Can anyone help... Pleeeeeeeeeease!

Dim rst As DAO.Recordset
Dim sBodyText As String
Dim strSQL As String

strSQL = "SELECT Callers_client_planning.User_Name AS Caller, Callers_client_planning.Client, Callers_client_planning.Code, Callers_client_planning.Plan_Hours AS [Planned Hours]  From Callers_client_planning WHERE Callers_client_planning.Plan_Hours Is Not Null AND Callers_client_planning.Code= " & [Forms]![Alter_planning]![Text48]
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpensnapshot)

sBodyText = "Caller" & vbTab & "Client" & vbTab & vbTab & vbTab & "Planned Hours" & vbCrLf

While Not rst.EOF
    sBodyText = sBodyText & rst.Fields("Caller") & vbTab & vbTab & rst.Fields("Client") & vbTab & vbTab & rst.Fields("Planned Hours") & vbCrLf
    rst.MoveNext
Wend

rst.Close

DoCmd.SendObject , , , , , , "Change of Hours", sBodyText, True

Many Thansks in advance

 

[smedvid]

Did u debug.print strSQL in order to see what is being populated in that string? If a value is populated, I typically cut&paste into a New Query window to confirm it will run successfully. Also confirm that [Forms]![Alter_planning]![Text48] is properly populated. I also use the .Value clause, i.e. [Forms]![Alter_planning]![Text48].Value.

htwh,

Steve Medvid
IT Consultant & Web Master

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[alvechurchdata]

It would help if we knew which of these lines caused the error. Single step through the code until you hit the fault and that'll be a good clue.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[smedvid]

One more quick thought...
Callers_client_planning.Code is that numeric or character? Perhaps you need to place text identifers around [Forms]![Alter_planning]![Text48].


Steve Medvid
IT Consultant & Web Master

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[formerTexan]

see

http://www.tek-tips.com/viewthread.cfm?qid=1566385&page=1

 

[blindlemonray]

Hi smedvid,

I did debug the sql and it ok. I create a query and took the code from that in the first instance (cheating I know!). I also added the .value but it still does not work.

Hi Alve,

The code stop on

 Set rst = CurrentDb.OpenRecordset(strSQL, dbOpensnapshot)

Hi Formertexan,

I will have a look at that thread is some more depth... Need to get my head around the code a bit.

Thanks for all you suggestions. I have created a work around for now. I have used a make table query which i run in the code first and then changed the code to dbopentable and it works a treat.

 

[alvechurchdata]

Is your strSQL all on one line in the original code? You can't run a string expression across multiple lines so it might have to use the line continuation characters like this:

strSQL = "SELECT Callers_client_planning.User_Name AS Caller, Callers_client_planning.Client, " & _
"Callers_client_planning.Code, Callers_client_planning.Plan_Hours AS [Planned Hours]  From " & _ ...

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[formerTexan]

Not to flog a dead cat or anything, but what happened when you tried the earlier suggestion pointing you to another thread? According to the error, your issue is with the parameters. It is likely that the SQL syntax is OK since syntax will take precedence over parameters and raise an error first.

http://www.tek-tips.com/viewthread.cfm?qid=1566385&page=1

Cheers, Bill

 

[Gatto37]

Try this:

Callers_client_planning.Code= '" & [Forms]![Alter_planning]![Text48] & "'"

The key is the ' before and after the reference to a form

 

[missinglinq]

Access has a bad habit of highlighting the line afterthe actual offending line in Debug, and if that's happening here, then the real culprit would not be

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpensnapshot)

but rather

Callers_client_planning.Code= " & [Forms]![Alter_planning]![Text48]

and Gatto may well be correct. The original syntax is correct if Callers_client_planning.Code is defined as a Number, but if it is defined as Text, Gatto's suggestion is the correct syntax:

Callers_client_planning.Code= '" & [Forms]![Alter_planning]![Text48] & "'"

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!