Looping Through a Queries Results

E

Eka1618

Hello,

I am trying to loop through a predefined query to see if all records have a
"STATUS" = "COMPLETE" and return true (through a function) if they are all
completed. I am getting an error that my parameter (item) not found in my
collection.

Here is my code:

Public Function archival() As Boolean
Dim db As Database
Dim rs As dao.Recordset
Dim qdf As dao.QueryDef

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryReady4Archive")


'ERROR OCCURS HERE!
qdf.Parameters("REQUEST_NO") = Me.REQUEST_NO.Value

Set rs = qdf.OpenRecordset

archival = True

While Not rs.EOF
If rs(1).Value <> "COMPLETE" Then
archival = False
rs.MoveNext
End If
Wend

End Function


This is the SQL of my query:
SELECT tblQueue.STATUS, tblQueue.REQUEST_NO FROM tblQueue;


I am not sure where I am going wrong. If anyone has any suggestion, please
let me know. Thank You!

~Erica~
 
K

Klatuu

The message is telling you that you do not have a named parameter in your
query with the name REQUEST_NO.

You can either put a value in the field's Criteria row in brackets:
[REQUEST_NO] (should not be the field name, however)
or you can open the parameters dialog in the query designer and put it there.
 
E

Eka1618

Klatuu,


Is this what you mean? Here is my new SQL:

SELECT tblQueue.STATUS, tblQueue.REQUEST_NO
FROM tblQueue
WHERE (((tblQueue.REQUEST_NO) Like [forms].[frmTestReviewData].[REQUEST_NO]));


I am getting a new error that says too few parameters:

Public Function archival() As Boolean
Dim db As Database
Dim rs As dao.Recordset
Dim qdf As dao.QueryDef

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryReady4Archive")

'I commented this out for now, not sure if I was supposed to or not
'qdf.Parameters("REQUEST_NO") = Me.REQUEST_NO.Value

'ERROR OCCURS HERE
Set rs = qdf.OpenRecordset

archival = True

While Not rs.EOF
If rs(1).Value <> "COMPLETE" Then
archival = False
rs.MoveNext
End If
Wend

End Function

Klatuu said:
The message is telling you that you do not have a named parameter in your
query with the name REQUEST_NO.

You can either put a value in the field's Criteria row in brackets:
[REQUEST_NO] (should not be the field name, however)
or you can open the parameters dialog in the query designer and put it there.
--
Dave Hargis, Microsoft Access MVP


Eka1618 said:
Hello,

I am trying to loop through a predefined query to see if all records have a
"STATUS" = "COMPLETE" and return true (through a function) if they are all
completed. I am getting an error that my parameter (item) not found in my
collection.

Here is my code:

Public Function archival() As Boolean
Dim db As Database
Dim rs As dao.Recordset
Dim qdf As dao.QueryDef

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryReady4Archive")


'ERROR OCCURS HERE!
qdf.Parameters("REQUEST_NO") = Me.REQUEST_NO.Value

Set rs = qdf.OpenRecordset

archival = True

While Not rs.EOF
If rs(1).Value <> "COMPLETE" Then
archival = False
rs.MoveNext
End If
Wend

End Function


This is the SQL of my query:
SELECT tblQueue.STATUS, tblQueue.REQUEST_NO FROM tblQueue;


I am not sure where I am going wrong. If anyone has any suggestion, please
let me know. Thank You!

~Erica~
Click to expand...
Click to expand...
 
K

Klatuu

Here is a different approach. Leave the parameter in the query as you have
it now. Now, here is a modified version of your original cide that should
work:

Dim prm As DAO.Parameter

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryReady4Archive")

For each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset

--
Dave Hargis, Microsoft Access MVP


Eka1618 said:
Klatuu,


Is this what you mean? Here is my new SQL:

SELECT tblQueue.STATUS, tblQueue.REQUEST_NO
FROM tblQueue
WHERE (((tblQueue.REQUEST_NO) Like [forms].[frmTestReviewData].[REQUEST_NO]));


I am getting a new error that says too few parameters:

Public Function archival() As Boolean
Dim db As Database
Dim rs As dao.Recordset
Dim qdf As dao.QueryDef

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryReady4Archive")

'I commented this out for now, not sure if I was supposed to or not
'qdf.Parameters("REQUEST_NO") = Me.REQUEST_NO.Value

'ERROR OCCURS HERE
Set rs = qdf.OpenRecordset

archival = True

While Not rs.EOF
If rs(1).Value <> "COMPLETE" Then
archival = False
rs.MoveNext
End If
Wend

End Function

Klatuu said:
The message is telling you that you do not have a named parameter in your
query with the name REQUEST_NO.

You can either put a value in the field's Criteria row in brackets:
[REQUEST_NO] (should not be the field name, however)
or you can open the parameters dialog in the query designer and put it there.
--
Dave Hargis, Microsoft Access MVP


Eka1618 said:
Hello,

I am trying to loop through a predefined query to see if all records have a
"STATUS" = "COMPLETE" and return true (through a function) if they are all
completed. I am getting an error that my parameter (item) not found in my
collection.

Here is my code:

Public Function archival() As Boolean
Dim db As Database
Dim rs As dao.Recordset
Dim qdf As dao.QueryDef

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryReady4Archive")


'ERROR OCCURS HERE!
qdf.Parameters("REQUEST_NO") = Me.REQUEST_NO.Value

Set rs = qdf.OpenRecordset

archival = True

While Not rs.EOF
If rs(1).Value <> "COMPLETE" Then
archival = False
rs.MoveNext
End If
Wend

End Function


This is the SQL of my query:
SELECT tblQueue.STATUS, tblQueue.REQUEST_NO FROM tblQueue;


I am not sure where I am going wrong. If anyone has any suggestion, please
let me know. Thank You!

~Erica~
Click to expand...
Click to expand...
Click to expand...
 
E

Eka1618

Klatuu,

Thank you for the help, it seems to be looping through the records now.
However, I have an endless loop, lol...

The query results are correct if I run it seperate from this code, but when
I call my function, the loop is endless.

Here is the loop in the function:

Set rs = qdf.OpenRecordset

archival = True

While Not rs.EOF
If rs(0).Value <> "COMPLETE" Then
archival = False
rs.MoveNext
End If
Wend

This is how I call it:

If archival() = True Then
DoCmd.SendObject acSendNoObject, , , emName2, , , emailSubject2,
emailBody2, False, False
End If

If you have anymore suggestions please let me know. Thanks again for the help!

~Erica~










Klatuu said:
Here is a different approach. Leave the parameter in the query as you have
it now. Now, here is a modified version of your original cide that should
work:

Dim prm As DAO.Parameter

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryReady4Archive")

For each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset

--
Dave Hargis, Microsoft Access MVP


Eka1618 said:
Klatuu,


Is this what you mean? Here is my new SQL:

SELECT tblQueue.STATUS, tblQueue.REQUEST_NO
FROM tblQueue
WHERE (((tblQueue.REQUEST_NO) Like [forms].[frmTestReviewData].[REQUEST_NO]));


I am getting a new error that says too few parameters:

Public Function archival() As Boolean
Dim db As Database
Dim rs As dao.Recordset
Dim qdf As dao.QueryDef

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryReady4Archive")

'I commented this out for now, not sure if I was supposed to or not
'qdf.Parameters("REQUEST_NO") = Me.REQUEST_NO.Value

'ERROR OCCURS HERE
Set rs = qdf.OpenRecordset

archival = True

While Not rs.EOF
If rs(1).Value <> "COMPLETE" Then
archival = False
rs.MoveNext
End If
Wend

End Function

Klatuu said:
The message is telling you that you do not have a named parameter in your
query with the name REQUEST_NO.

You can either put a value in the field's Criteria row in brackets:
[REQUEST_NO] (should not be the field name, however)
or you can open the parameters dialog in the query designer and put it there.
--
Dave Hargis, Microsoft Access MVP


:

Hello,

I am trying to loop through a predefined query to see if all records have a
"STATUS" = "COMPLETE" and return true (through a function) if they are all
completed. I am getting an error that my parameter (item) not found in my
collection.

Here is my code:

Public Function archival() As Boolean
Dim db As Database
Dim rs As dao.Recordset
Dim qdf As dao.QueryDef

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryReady4Archive")


'ERROR OCCURS HERE!
qdf.Parameters("REQUEST_NO") = Me.REQUEST_NO.Value

Set rs = qdf.OpenRecordset

archival = True

While Not rs.EOF
If rs(1).Value <> "COMPLETE" Then
archival = False
rs.MoveNext
End If
Wend

End Function


This is the SQL of my query:
SELECT tblQueue.STATUS, tblQueue.REQUEST_NO FROM tblQueue;


I am not sure where I am going wrong. If anyone has any suggestion, please
let me know. Thank You!

~Erica~
Click to expand...
Click to expand...
Click to expand...
 
E

Eka1618

Nevermind the last post.

I figured out what I did to make the loop endless....

BAD:
If rs(0).Value <> "COMPLETE" Then
archival = False
rs.MoveNext
End If


GOOD:

If rs(0).Value <> "COMPLETE" Then
archival = False
End If
rs.MoveNext


Klatuu said:
Here is a different approach. Leave the parameter in the query as you have
it now. Now, here is a modified version of your original cide that should
work:

Dim prm As DAO.Parameter

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryReady4Archive")

For each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset

--
Dave Hargis, Microsoft Access MVP


Eka1618 said:
Klatuu,


Is this what you mean? Here is my new SQL:

SELECT tblQueue.STATUS, tblQueue.REQUEST_NO
FROM tblQueue
WHERE (((tblQueue.REQUEST_NO) Like [forms].[frmTestReviewData].[REQUEST_NO]));


I am getting a new error that says too few parameters:

Public Function archival() As Boolean
Dim db As Database
Dim rs As dao.Recordset
Dim qdf As dao.QueryDef

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryReady4Archive")

'I commented this out for now, not sure if I was supposed to or not
'qdf.Parameters("REQUEST_NO") = Me.REQUEST_NO.Value

'ERROR OCCURS HERE
Set rs = qdf.OpenRecordset

archival = True

While Not rs.EOF
If rs(1).Value <> "COMPLETE" Then
archival = False
rs.MoveNext
End If
Wend

End Function

Klatuu said:
The message is telling you that you do not have a named parameter in your
query with the name REQUEST_NO.

You can either put a value in the field's Criteria row in brackets:
[REQUEST_NO] (should not be the field name, however)
or you can open the parameters dialog in the query designer and put it there.
--
Dave Hargis, Microsoft Access MVP


:

Hello,

I am trying to loop through a predefined query to see if all records have a
"STATUS" = "COMPLETE" and return true (through a function) if they are all
completed. I am getting an error that my parameter (item) not found in my
collection.

Here is my code:

Public Function archival() As Boolean
Dim db As Database
Dim rs As dao.Recordset
Dim qdf As dao.QueryDef

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryReady4Archive")


'ERROR OCCURS HERE!
qdf.Parameters("REQUEST_NO") = Me.REQUEST_NO.Value

Set rs = qdf.OpenRecordset

archival = True

While Not rs.EOF
If rs(1).Value <> "COMPLETE" Then
archival = False
rs.MoveNext
End If
Wend

End Function


This is the SQL of my query:
SELECT tblQueue.STATUS, tblQueue.REQUEST_NO FROM tblQueue;


I am not sure where I am going wrong. If anyone has any suggestion, please
let me know. Thank You!

~Erica~
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Referencing Field names in Parameters 14
Criteria Range for generating Recordset (syntax help) 9
record count of table while looping through all tables 1
Duplicating records in a form and subform (Allen Browne) 2
Need help with Loop that is not repeating the function 11
build list for eMail 1
excel automation through parameter queries 3
fldName 4

Top