Runtime 3061 Too Few Parameters (1 Viewer)

[mousemat]

Hi All

I have created a database that allows for the emailing of a standard message to multiple email address via a module and VBA code. This creates a recordsource from a query.

All work well, however, if I add parameters to the underlying query, I get a RunTime 3061 error too few paramaters.


Here's the code that it highlights

Set rs = db.OpenRecordset("SELECT PName, PEmail, EventName, EmailSent, EmailSentDate, EventID, EventDate, AmountPaid " & _
                                " FROM qryEmailAddresses", dbOpenDynaset)

Basically, I need to filter out the list of email addresses where the EventID is equal to that on the form together with the AmountPaid = null

The underlying query is thus

SELECT tblParticipants.PName, tblParticipants.PEmail, tblBookings.EventID, tblEvents.EventName, tblEvents.EventDate, tblBookings.AmountPaid, tblBookings.EmailSent, tblBookings.EmailSentDate
FROM (tblBookings INNER JOIN tblEvents ON tblBookings.EventID = tblEvents.EventID) INNER JOIN tblParticipants ON tblBookings.ParticipantID = tblParticipants.ParticipantID
WHERE (((tblBookings.EventID)=[forms]![frmBookingReport]![EventID]) AND ((tblBookings.AmountPaid) Is Null) AND ((tblBookings.EmailSent)=0));

Any help in where I am going wrong would be great.

 

[isladogs]

Add delimiters

If EventID is a number field...

SELECT tblParticipants.PName, tblParticipants.PEmail, tblBookings.EventID, tblEvents.EventName, tblEvents.EventDate, tblBookings.AmountPaid, tblBookings.EmailSent, tblBookings.EmailSentDate
FROM (tblBookings INNER JOIN tblEvents ON tblBookings.EventID = tblEvents.EventID) INNER JOIN tblParticipants ON tblBookings.ParticipantID = tblParticipants.ParticipantID
WHERE (((tblBookings.EventID)= " & [forms]![frmBookingReport]![EventID] & ") AND ((tblBookings.AmountPaid) Is Null) AND ((tblBookings.EmailSent)=0));

If it's a text field...

SELECT tblParticipants.PName, tblParticipants.PEmail, tblBookings.EventID, tblEvents.EventName, tblEvents.EventDate, tblBookings.AmountPaid, tblBookings.EmailSent, tblBookings.EmailSentDate
FROM (tblBookings INNER JOIN tblEvents ON tblBookings.EventID = tblEvents.EventID) INNER JOIN tblParticipants ON tblBookings.ParticipantID = tblParticipants.ParticipantID
WHERE (((tblBookings.EventID)= '" & [forms]![frmBookingReport]![EventID] & "') AND ((tblBookings.AmountPaid) Is Null) AND ((tblBookings.EmailSent)=0));

 

[mousemat]

Hi

Thanks for that

I have changed the underlying queries SQL and now get a data mismatch in criteria expression

EventID is an Autonumber

 

[isladogs]

Perhaps I didn't make it clear in the first reply but I was expecting you to use the expression directly in the recordset.
You don't need a query as a 'stepping stone' and TBH I'd actually say its not helpful here

You say its an autonumber so I assume you used the first expression

Its possible that it doesn't like the Null part so suggest replacing with
Nz(tblBookings.AmountPaid,0) =0

also you are just reading a snapshot of your data so dbOpenDynaset isn't needed - use dbOpenSnapshot instead

So overall, I suggest this

Dim strSQL As String

strSQL = "SELECT tblParticipants.PName, tblParticipants.PEmail, tblBookings.EventID, tblEvents.EventName, tblEvents.EventDate, tblBookings.AmountPaid, tblBookings.EmailSent, tblBookings.EmailSentDate
FROM (tblBookings INNER JOIN tblEvents ON tblBookings.EventID = tblEvents.EventID) INNER JOIN tblParticipants ON tblBookings.ParticipantID = tblParticipants.ParticipantID
WHERE (((tblBookings.EventID)= " & [forms]![frmBookingReport]![EventID] & ") AND (Nz(tblBookings.AmountPaid,0) =0) AND ((tblBookings.EmailSent)=0));"

'Debug.Print strSQL

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

Hopefully that will work.
However if it gives an error, remove the single quote at the start of the Debug line, repeat & see what is shown in the VBE Immediate window

 

[mousemat]

Excellent, that works

However, I was using the dbopendynaset as I want to place a flag in the tbleBookings to show that an email has been sent and what date it was sent. I was using the .Edit to update the recordset with the the flags

 

[isladogs]

Glad its working for you.
Stick with dbOpenDynaset for the reason you gave

 

[mousemat]

Excellent, that works

However, I was using the dbopendynaset as I want to place a flag in the tbleBookings to show that an email has been sent and what date it was sent. I was using the .Edit to update the recordset with the the flags

 

[isladogs]

I think you are repeating yourself!