To few parameters. Expected 1. db.openrecordset()

[Ruegen]

I have one function that makes a string, the string gets passed on because I use it for row source on a listbox



I also pass the string function into another function in order to count the records so that I can produce a total count on a label on the form - however I get the "too few parameters" error

not sure why

Function LabelCountSQL(SQLstring As String) As Long


Dim strSQL As String


Debug.Print strSQL
strSQL = SQLstring
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, 1)


If rs.RecordCount = 0 Then
rs.Close
Set rs = Nothing
LabelCountSQL = 0
Exit Function
Else
LabelCountSQL = rs.RecordCount
rs.Close
Set rs = Nothing
End If




End Function

The SQL is:

SELECT Max(tblSchools.NewSchoolsID) AS GSchoolID, Max(tblSchools.SchoolName) AS GSchoolName, tblSchools.SchoolEmail AS GSchoolEmail, Max(tblSchools.[1ContactName]) AS G1ContactName, Max(tblSchools.[1ContactSurname]) AS G1ContactSurname
 FROM tblSchools
 WHERE (((tblSchools.Removed) Is Null) AND ((tblSchools.schooldonotemail) Is Null)  AND ((tblSchools.AreaID)=45) AND ((tblSchools.NewSchoolsID) Not In (Select tblSchools.NewSchoolsID from qryHTMLEmailExclude_do_not_delete)) )
 GROUP BY tblSchools.SchoolEmail;

[June7]

Does qryHTMLEmailExclude_do_not_delete have a dynamic parameter?

[Ruegen]

Does qryHTMLEmailExclude_do_not_delete have a dynamic parameter?

Yup. I'm sure it does :-/

How do I get around that?

[June7]

Can't have dynamic parameter in the query (I never use dynamic parameterized query objects).

Will have to construct the subquery in VBA and concatenate the parameter.

You don't show code that constructs SQLstring.

... FROM (SELECT ... WHERE field=" & Me.textbox & ") AS qryHTMLEmailExclude_do_not_delete ...

[Ruegen]

Can't have dynamic parameter in the query (I never use dynamic parameterized query objects).

Will have to construct the subquery in VBA and concatenate the parameter.

You don't show code that constructs SQLstring.

... FROM (SELECT ... WHERE field=" & Me.textbox & ") AS qryHTMLEmailExclude_do_not_delete ...

strSQLEmailForm = "SELECT Max(tblSchools.NewSchoolsID) AS GSchoolID, Max(tblSchools.SchoolName) AS GSchoolName, tblSchools.SchoolEmail AS" & _
" GSchoolEmail, Max(tblSchools.[1ContactName]) AS G1ContactName, Max(tblSchools.[1ContactSurname]) AS G1ContactSurname" & vbCrLf & _
" FROM tblSchools" & vbCrLf & _
" WHERE (((tblSchools.Removed) Is Null) AND ((tblSchools.schooldonotemail) Is Null) " & IIf(IsNull(Me.txtPostCode), "", strPostCodeBetween("IIf(isnothing([SchoolPostCode]),Val(0),Val([SchoolPostCode])))", Me.txtPostCode, Nz(Me.txtRangeNumber, 0))) & _
IIf(IsNull(Me.txtEnroll), "", " AND ((tblSchools.Enrollment)" & Me.cboAboveBelow.Column(0) & "" & Me.txtEnroll & ") ") & _
IIf(IsNull(Me.cboArea), "", " AND ((tblSchools.AreaID)=" & Me.cboArea.Column(0) & ")") & _
" AND ((tblSchools.NewSchoolsID) Not In (Select tblSchools.NewSchoolsID from qryHTMLEmailExclude_do_not_delete)) )" & vbCrLf & _
" GROUP BY tblSchools.SchoolEmail;"

[June7]

Did you try the edit I suggested?

[Ruegen]

slaps own head*

nevermind using

Me.lblCount.Caption = lstSchools.ListCount

[Ruegen]

Sorry June7 (I do honestly try not to waste anyone's time here).

[Ruegen]

Did you try the edit I suggested?

I had to bring this back up as it became a problem later

the query excludes any id that is in qryHTMLEmailExclude_do_not_delete

so I don't quite know what you mean as the query is built using the two then it's supposed to run through them.

qryHTMLEmailExclude_do_not_delete does have a parameter inside of it so I would have to build the whole query within the other query string....?

[Ruegen]

the queryHTML sql

SELECT tblSchools.NewSchoolsID, tblBookings.BookingDate, tblJncShows.PerformersID
FROM (tblSchools INNER JOIN tblTeacher ON tblSchools.NewSchoolsID = tblTeacher.NewSchoolsID) INNER JOIN ((tblShows INNER JOIN (tblBookings INNER JOIN tblJncTeacher ON tblBookings.BookingsID = tblJncTeacher.BookingsID) ON tblShows.ShowsID = tblBookings.ShowsID) INNER JOIN tblJncShows ON tblShows.ShowsID = tblJncShows.ShowsID) ON tblTeacher.TeacherID = tblJncTeacher.TeacherID
WHERE (((tblBookings.BookingDate)>Date()) AND ((tblJncShows.PerformersID)=[forms]![frmEmailSchoolHTML]![cboPerformer]));

[Ruegen]

This here doesn't give me a parameter error but I get no results

strSQLEmailForm = "SELECT Max(tblSchools.NewSchoolsID) AS GSchoolID, Max(tblSchools.SchoolName) AS GSchoolName, tblSchools.SchoolEmail AS" & _
" GSchoolEmail, Max(tblSchools.[1ContactName]) AS G1ContactName, Max(tblSchools.[1ContactSurname]) AS G1ContactSurname" & vbCrLf & _
" FROM tblSchools" & vbCrLf & _
" WHERE (((tblSchools.Removed) Is Null) AND ((tblSchools.schooldonotemail) Is Null) " & IIf(IsNull(Me.txtPostCode), "", strPostCodeBetween("IIf(isnothing([SchoolPostCode]),Val(0),Val([SchoolPostCode])))", Me.txtPostCode, Nz(Me.txtRangeNumber, 0))) & _
IIf(IsNull(Me.txtEnroll), "", " AND ((tblSchools.Enrollment)" & Me.cboAboveBelow.Column(0) & "" & Me.txtEnroll & ") ") & _
IIf(IsNull(Me.cboArea), "", " AND ((tblSchools.AreaID)=" & Me.cboArea.Column(0) & ")") & _
" AND ((tblSchools.NewSchoolsID)  Not In (Select tblSchools.NewSchoolsID from qryHTMLEmailExclude_do_not_delete WHERE tblBookings.BookingDate >Date() AND tblJncShows.PerformersID =" & Me.cboPerformer & "))" & vbCrLf & _
" GROUP BY tblSchools.SchoolEmail;"

[Ruegen]

ok this works:

strSQLEmailForm = "SELECT Max(tblSchools.NewSchoolsID) AS GSchoolID, Max(tblSchools.SchoolName) AS GSchoolName, tblSchools.SchoolEmail AS" & _
" GSchoolEmail, Max(tblSchools.[1ContactName]) AS G1ContactName, Max(tblSchools.[1ContactSurname]) AS G1ContactSurname" & vbCrLf & _
" FROM tblSchools" & vbCrLf & _
" WHERE (((tblSchools.Removed) Is Null) AND ((tblSchools.schooldonotemail) Is Null) " & IIf(IsNull(Me.txtPostCode), "", strPostCodeBetween("IIf(isnothing([SchoolPostCode]),Val(0),Val([SchoolPostCode])))", Me.txtPostCode, Nz(Me.txtRangeNumber, 0))) & _
IIf(IsNull(Me.txtEnroll), "", " AND ((tblSchools.Enrollment)" & Me.cboAboveBelow.Column(0) & "" & Me.txtEnroll & ") ") & _
IIf(IsNull(Me.cboArea), "", " AND ((tblSchools.AreaID)=" & Me.cboArea.Column(0) & ")") & _
" AND ((tblSchools.NewSchoolsID) Not In (Select tblSchools.NewSchoolsID from qryHTMLEmailExclude_do_not_delete WHERE ((tblBookings.BookingDate) > Date()) AND ((tblJncShows.PerformersID) =" & Me.cboPerformer.Column(0) & ")  )))" & vbCrLf & _
" GROUP BY tblSchools.SchoolEmail;"

[Ruegen]

I think this is what I will go with

Baisically it is any school that doesn't have a performance above this date with this performer (qryHTMLEmailExclude a query of performances of a performer that is above current date, so all the schools with bookings of performer XYZ will not show in the other query).

strSQLEmailForm = "SELECT Max(tblSchools.NewSchoolsID) AS GSchoolID, Max(tblSchools.SchoolName) AS GSchoolName, tblSchools.SchoolEmail AS" & _
" GSchoolEmail, Max(tblSchools.[1ContactName]) AS G1ContactName, Max(tblSchools.[1ContactSurname]) AS G1ContactSurname" & vbCrLf & _
" FROM tblSchools" & vbCrLf & _
" WHERE (((tblSchools.Removed) Is Null) AND ((tblSchools.schooldonotemail) Is Null) " & IIf(IsNull(Me.txtPostCode), "", strPostCodeBetween("IIf(isnothing([SchoolPostCode]),Val(0),Val([SchoolPostCode])))", Me.txtPostCode, Nz(Me.txtRangeNumber, 0))) & _
IIf(IsNull(Me.txtEnroll), "", " AND ((tblSchools.Enrollment)" & Me.cboAboveBelow.Column(0) & "" & Me.txtEnroll & ") ") & _
IIf(IsNull(Me.cboArea), "", " AND ((tblSchools.AreaID)=" & Me.cboArea.Column(0) & ")") & _
" AND ((tblSchools.NewSchoolsID) Not In (Select tblSchools.NewSchoolsID from qryHTMLEmailExclude_do_not_delete WHERE ((tblBookings.BookingDate) > Date()) " & IIf(IsNull(Me.cboPerformer), "", " AND ((tblJncShows.PerformersID) =" & Me.cboPerformer.Column(0) & ")") & ")))" & vbCrLf & _
" GROUP BY tblSchools.SchoolEmail "