Recordcount error: too few parameters 1

[irishandy]

I've done a search for this in the forum but can't find the exact answer. All I want the piece of code to do is to tell me if the query returned records. I've included my code below. At the moment it runs fine if there are records to return however it produces the following error if there are no records: Too few parameters. Expected 1. Can anyone provide a solution?



Private Sub SourceList_DblClick(Cancel As Integer)
On Error GoTo dclickErr

Dim DB As Database
Dim RS As Recordset
Dim ls_SQL As String
Dim sSelected As String

Set DB = CurrentDb

sSelected = "src_Notes_" & SourceList.ItemData(SourceList.ItemsSelected(0))

ls_SQL = "SELECT * FROM " & "[" & sSelected & "]"
Set RS = DB.OpenRecordset(ls_SQL)

If RS.RecordCount > 0 Then
MsgBox "records"
Else
MsgBox "no records"
End If

Set RS = Nothing
Set DB = Nothing

Me.RecordSource = sSelected

Exit Sub
dclickErr:
MsgBox Err.Description, vbExclamation
Err.Clear
End Sub

 

[scriverb]

Try just using the DCount function:

If DCount("*", "[i]tableorqueryname[/i]") = 0 then
   'do something because there are no records
else
   'do something because there are records
End If

This will return the count of the records in the table or query. Much easier that the method you tried.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[irishandy]

Ah - I like that solution.

I've copped on as to why the parameter error was coming up - the two queries in question both request a Staff ID to be entered (it's a 5 digit number). Any ideas on how I can deal with that problem?

 

[scriverb]

Are you saying in the SQL there is a prompt for this value? Do you not want it to be requested? If you have another way of identifying this value, form control, field in a tble, etc. then we could change the query. If I am confused here it is because I haven't see the SQL. Just post it and I can take a look.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[irishandy]

Here's the SQL from the query...

SELECT Notes.Logged_By AS N_LoggedBy, Notes.*
FROM Notes
WHERE ((([b]Notes.Logged_By)=[Enter Staff ID][/b]) AND ((DatePart("yyyy",[Date_Received]))=DatePart("yyyy",Now())) AND ((DatePart("y",[Date_Received]))=DatePart("y",Now())));

 

[scriverb]

I see the prompt for [Enter Staff ID] but I still need to know why this is a problem. If the query needs the Staff ID to select the records how are you going to get around it.

I already asked is there a form control or a field in a table somewhere where we can go to get the value for this query. I don't know your system and i don't see the problem at this time. It must be because you are asking me to fix it. But, I don't see the reason as to why it is a problem.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[irishandy]

The dCount function isn't able to enter in such a parameter therefore it returns "The expression you entered as a query parameter produced this error: 'The object doesn't contain the Automation object 'Enter Staff ID.''"

To give this some context: the query names are all in a listbox on a form. The user double clicks on the name of the query that they wish to use as the recordset for that form.

There may be no way around this except to place a field on the form where the user can enter a userID if so required.

 

[scriverb]

Create a Global variable and a Function in a database module:

Global lngStaffID as Long
Public Function StaffID() as Long
StaffID = LngStaffID
End Function

Update your query to the following:

SELECT Notes.Logged_By AS N_LoggedBy, Notes.*
FROM Notes
WHERE (((Notes.Logged_By)= [red]StaffID()[/red]) AND ((DatePart("yyyy",[Date_Received]))=DatePart("yyyy",Now())) AND ((DatePart("y",[Date_Received]))=DatePart("y",Now())));

Now in the code to call this use the following:

lngStaffID = InputBox("Enter Staff ID: ")
If DCount("*", "tableorqueryname") = 0 then
   'do something because there are no records
else
   'do something because there are records
End If

Now the prompt is prior to the DCount function call. The staff ID value is stored in the global variable. The Query use a function call to retrieve the global variable value and all is well in SQL land.

Post back if you have any questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[irishandy]

Thanks for your help - I've tweaked it a bit and it's doing the trick.