Looping Through a Queries Results

  • Thread starter Thread starter Eka1618
  • Start date Start date
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~
 
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.
 
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...
 
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...
 
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...
 
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...
 
Back
Top