QueryDefs / Parameters

[Kevin Bruce]

A problem with QueryDefs...

I send out email reminders on a monthly basis to school districts. To this
end, I have created a form (called 'frmSDMonthlyReminder') with a button
(cmdButton) and a combo box (cboSD). The button, when clicked, opens the
email client and adds all the necessary addresses and infomation drawn from
a variety of queries (eg. 'qryBookingEmailSDContact'). The combo box
contains a list of the specific school districts that are intended to limit
the query.

When I click the button I get this message:

Item not found in this collection. (Error 3265)

I am uncertain as to how to correct this error. Below is the first part of
my code with the line where it stops running marked with asterisks (***).

Thanks in advance for any help.

_Kevin

==============================

Option Compare Database


Private Sub cmdButton_Click()

'create variables

Dim strTo As String
Dim strCC As String
Dim strBCC As String
Dim strSubjectLine As String
Dim strMessageText As String
Dim strEvents As String
Dim strQuery As String
Dim db As Database
Dim recEvents As Recordset
Dim qdf As QueryDef

'assign a value to the database variable
Set db = CurrentDb()

'create the addresses for the 'To' line of the email
strQuery = "qryBookingEmailSDContact"
'assign a value to the query parameters
Set qdf = db.QueryDefs(strQuery)
*** qdf.Parameters(0) = Forms!frmSDMonthlyReminder!cboSD ***
Set recEvents = db.OpenRecordset(strQuery)
While Not recEvents.EOF
strTo = recEvents("email") & ";" & strTo
recEvents.MoveNext
Wend

 

[Dirk Goldgar]

A problem with QueryDefs...

I send out email reminders on a monthly basis to school districts. To
this end, I have created a form (called 'frmSDMonthlyReminder') with
a button (cmdButton) and a combo box (cboSD). The button, when
clicked, opens the email client and adds all the necessary addresses
and infomation drawn from a variety of queries (eg.
'qryBookingEmailSDContact'). The combo box contains a list of the
specific school districts that are intended to limit the query.

When I click the button I get this message:

Item not found in this collection. (Error 3265)

I am uncertain as to how to correct this error. Below is the first
part of my code with the line where it stops running marked with
asterisks (***).

Thanks in advance for any help.

_Kevin

==============================

Option Compare Database


Private Sub cmdButton_Click()

'create variables

Dim strTo As String
Dim strCC As String
Dim strBCC As String
Dim strSubjectLine As String
Dim strMessageText As String
Dim strEvents As String
Dim strQuery As String
Dim db As Database
Dim recEvents As Recordset
Dim qdf As QueryDef

'assign a value to the database variable
Set db = CurrentDb()

'create the addresses for the 'To' line of the email
strQuery = "qryBookingEmailSDContact"
'assign a value to the query parameters
Set qdf = db.QueryDefs(strQuery)
*** qdf.Parameters(0) = Forms!frmSDMonthlyReminder!cboSD ***
Set recEvents = db.OpenRecordset(strQuery)
While Not recEvents.EOF
strTo = recEvents("email") & ";" & strTo
recEvents.MoveNext
Wend

It sounds like the query "qryBookingEmailSDContact" has no parameters.
If you think it does, maybe you shoupld post the SQL here so we can see
what may be wrong.

Also, note that this:

Set recEvents = db.OpenRecordset(strQuery)

is going to completely ignore the querydef object you've been
manipulating. Do this instead:

Set recEvents = qdf.OpenRecordset

 

[Kevin Bruce]

Hi Dirk_

Your advice to rewrite the one line to "Set recEvents = qdf.OpenRecordset"
solved the problem.

Many thanks.

_Kevin

 

[Dirk Goldgar]

Hi Dirk_

Your advice to rewrite the one line to "Set recEvents =
qdf.OpenRecordset" solved the problem.

Huh. I don't see why, if the error was being raised on the line you
said i was. But I'm glad it's fixed.

 

[Kevin Bruce]

It was both my not having parameters set in the query and the Set recEvents
line. I had tried the query with and without parameters to no avail, but
once I re-wrote the Set recEvents line and added back the query parameters
everything went swimmingly.

Hope this explains the mystery.

Cheers.

_Kevin