Too few parameters expected 1

[AndyCompanyZ]

I have the above error come up on the following piece of code on the highlighted line:

Dim Rs4 As DAO.Recordset
 [COLOR=red]Set Rs4 = CurrentDb.OpenRecordset("Select * From tblFacultyEvent Where FacultyID = " & Me.cmboFacultyEvent & " And EventEndDay= " & Format(Me.Parent!EventEndDate, "\#mm\/dd\/yyyy\#"))
[/COLOR] If Not Rs4.EOF And Not R43.BOF Then
 MsgBox "Already Scheduled for an Event on same day"
 Exit Sub
 End If

I have run it with a breakpoint and it shows for instance Me.comboFaculty = 2 on the line but only that.

 

[spikepl]

Again and again you are trying to do too much in one go.

Coding by trial-and-error is inefficient, and does not lead to anything but the next error. Code in steps, and make sure each one works, before progressing to the next one. If something fails go back one step.

Otherwise how can you ever ween yourself of using this forum to resolve very basic errors?

When you have an SQL string, make that string first and make sure the content is as expected.

strSQL= "Select * From tblFacultyEvent Where FacultyID = " & Me.cmboFacultyEvent & " And EventEndDay= " & Format(Me.Parent!EventEndDate, "\#mm\/dd\/yyyy\#"

debug.print strSQL

Set RS4=CurrentDB.OPenRecordset(strSQL)

Now if there is something wrong here, eg the variables do not have the expected values then check the two bits in WHERE.

If in doubt about syntax, plug in FIXED values (and not variables) and make it work with that. Once that works, replace the fixed values with the variables.
If you run sour in syntax, stick the SQL into the query designer in SQL view, and see what it says there.

 

[jzwp22]

What is the datatype of the EventEndDay field in tblFacultyEvent?

If it is a date, then try this. Also you had ..And Not R43.BOF, I assume that you meant RS4

Dim Rs4 As DAO.Recordset
 Set Rs4 = CurrentDb.OpenRecordset("Select * From tblFacultyEvent Where FacultyID = " & Me.cmboFacultyEvent & " And EventEndDay=# " & Me.Parent!EventEndDate & "#")
 If Not Rs4.EOF And Not RS4.BOF Then
 MsgBox "Already Scheduled for an Event on same day"
 Exit Sub
 End If

 

[AndyCompanyZ]

Thanks to both of you I've tried jzpw22' s suggestion as yes it is a date field but it gave the same error.

I'm just trying spike's test using strSQL but I'm not sure how to run it
as its giving me an argument not optional error on the code's first line:
Set Rs4 = CurrentDb.OpenRecordset

 

[VilaRestal]

EventEndDay = #" & Format(Me.Parent!EventEndDate, "mm\dd\yyyy") & "#")

would be how I'd write the end of that line

 

[AndyCompanyZ]

I've tried that but to no avail I now have;
Set Rs4 = CurrentDb.OpenRecordset("Select * From tblFacultyEvent Where FacultyID =" & Me.cmboFacultyEvent & " And EventEndDay = #" & Format(Me.Parent!EventEndDate, "mm\dd\yyyy") & "#") as the offending line but it says syntax error what I'fd like to know is how to debug.Print it without running through all the code as that causes a mesaage that i can't.

 

[VilaRestal]

Sorry, I think actually it should be forward slashes in the date format:

Format(Me.Parent!EventEndDate, "mm/dd/yyyy")

you could put:

MsgBox Format(Me.Parent!EventEndDate, "mm/dd/yyyy")

before that line to check that isn't the cause of the problems (I think it must be, the rest looks fine)

 

[AndyCompanyZ]

I put the msgbox in and the date it returned was correct but when I ran the rest of the code again it said too few parameters expected 1. I changed the date slashes the other way / as well.

 

[VilaRestal]

I suppose put
, dbOpenDynaset
before the last bracket
(it's asking for a parameter let's give it an argument :s)
I'm always in the habit of putting that argument in (or snapshot) so it may be it doesn't like it left out. Perhaps that's why I got into that habit (I can't remember )

 

[jzwp22]

You may want to take a look at this site to see how the date was handled in code when the format() function was used.

 

[NigelShaw]

Hi

looking briefly through this thread, it seems that the answer is blatently obvious.

There are too few parameters, expect 1

this means that Access is expecting a parameter, something to tell Access what it it is. Becuase you havent got a parameter set up, you get the error because Access is waiting for you to tell it.

you need to investigate queryDefs and set this up along with your Recordset.

you get this issue when you are using a Where Clause. Consequently, you could change the "WHERE" to "HAVING" and you dont tend to get the issue.

I set up the Parameter by telling Access to look at the underlying form. When i set up my queryDef, i tell Access to look at the object that is holding the criteria. This completes the parameter and no more issue.

i am at work but when i get home tonight, i will post an example but the problem here is not the SQL per se, it is the lack of description or "Parameter" to tell Access. As far as Access would assume, it will be anything unless otherwise stated so an error is created.

in the most simplest form of explanation-

you are on road in your car and you arrive at a 5 point cross roads. each road is 50 miles long. you only have enough fuel to take the road once so what do you do if you dont know which one to take? nothing. you sit and wait or try and make a phone call. as soon as you know, you're on your way again. so,

you sitting at the crossroads wondering which route? = query
phonecall telling you which way to go = Parameter
Success


cheers

Nigel

 

[spikepl]

#11 An interesting hypothesis. If you think it through, you'll find that access asks for misspelled field names all the same, so setting parameters does not help, unless you misspell those too

 

[NigelShaw]

hi,

i dont quite understand this- there are many ways to handle paramaters, here is one from memory-

Dim qdf As Querydef
Dim rst As Recordset
Dim strSQL As String
 
Set strSQL = "Select * From tblFacultyEvent Where FacultyID = " & _ 
Me.cmboFacultyEvent & " And EventEndDay=# " & _ 
Me.Parent!EventEndDate & "#"
 
Set qdf = CurrentDB.QueryDefs(strSQL)
 
'you can use a query(s)
qdf.Parameters(0) = qryStartDate 
qdf.Parameters(1) = qryEndDate
 
'or a form reference
'qdf.Parameters(0) = Me.MyForm!MyTextBox1.Value
'qdf.Parameters(1) = Me.MyForm!MyTextBox2.Value
 
 
Set rst = qdf.OpenRecordset
 
rst.Close
qdf.Close
Set rst = Nothing
Set qdf = Nothing

you can also use a

for each Param in queryDefs but i cant remember without looking

Access will look for whatever you ask it to look for so im unsure of your statement. Could you expand?

Cheers

Nigel

 

[spikepl]

#13 There is not much to expand on. I have sent you a PM, because the rest of what I have to say is not suitable for public consumption.

 

[VilaRestal]

Also, the sql of the recordset is based on something called tblFacultyEvent
I'm guessing that's a table not a query
Tables don't require parameters

 

[Xproterg]

I would assign your complicated formatting to variables, then use the variables in the query. That way you can see what exactly is being generated. Also, don't forget your quotation marks... that is, single quotes when you're trying to pull info in your clauses. eg.

where name='" & string_variable & "';"
not
where name=" & string_variable & ";"

 

[VilaRestal]

Also, it is quite common for Access to give a red-herring error messages.

For example, this code:

    With Me.Detail
        If 1 = 0 Then
            .BackColor = 0
        Else
            .BackColor = 9999
    End With
    If 1 = 1 Then
        MsgBox "1=1"
    End If

will produce the error: End With without With

and that isn't the error. The error is If without End If

Moral of the story: don't assume an Access error message describes the problem.