422,727 Members | 2,138 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 422,727 IT Pros & Developers. It's quick & easy.

Recordset.Filter not working as MSDN says it does

Seth Schrock
Expert 2.5K+
P: 2,881
I'm attempting to filter a recordset. I had never done this before, so I looked to the MSDN website for help and found this website: Recordset.Filter Property. Here is the main excerpt from the help that I'm having trouble with:

Expand|Select|Wrap|Line Numbers
  1. rst.Filter = "City = '" & strCity & "'"
  2. Set rstFiltered = rst.OpenRecordset
Here is my usage of this help:

Expand|Select|Wrap|Line Numbers
  1. strQuestionFilter = "QLevel_fk = " & lngQuestionLevelID
  2. rstQuestionLevel.Filter = strQuestionFilter
  3. Set rstFiltered = rstQuestionLevel.OpenRecordset
  4.  
However, I get an error message on the third line that says "Too few parameters. Expected 2" (Error number 3061). As that is the same way the website told me to do it, I'm at a loss of what to try.
Feb 4 '13 #1

✓ answered by zmbd

As your code mirrors very close to what I've in my test DB and appears to work in the example Recordset.Filter Property (DAO) Office 2010

I'm suspect of your filter string - may be a red herring; hoewver, debug print the string value a 102 verify that the filter string is valid.

Share this Question
Share on Google+
17 Replies


NeoPa
Expert Mod 15k+
P: 30,736
Looks fine to me Seth. Maybe something wrong that we can't see.

We can't see the type of recordset nor can we see where it is instantiated (set up).
Feb 4 '13 #2

zmbd
Expert Mod 5K+
P: 5,056
Try "()" at end... provided the rest of the code is setup correctly for "rstQuestionLevel"
Set rstFiltered = rstQuestionLevel.OpenRecordset()

appears similar to the querydefs example given here: Database.OpenRecordset Method (DAO) Office 2010
Feb 4 '13 #3

Seth Schrock
Expert 2.5K+
P: 2,881
Just adding "()" at the end didn't change anything.

Here is the whole procedure:
Expand|Select|Wrap|Line Numbers
  1. Public Sub LoadDecisionTree(lngPenalCode As Long, lngCrimeID As Long)
  2. 'On Error GoTo LoadDecisionTree_Error
  3. Dim intCodeGroup As Integer
  4.  
  5. Dim db As DAO.Database
  6. Set db = CurrentDb
  7.  
  8. '*****************************************************
  9. intCodeGroup = 1
  10. '*****************************************************
  11. 'Delete any existing answers
  12. If DCount("ID", "tblAnswer", "CrimeID_fk =" & lngCrimeID) > 0 Then
  13.     Dim strDeleteAnswer As String
  14.     strDeleteAnswer = "DELETE * FROM tblAnswer WHERE CrimeID_fk = " & lngCrimeID
  15.  
  16.     DoCmd.SetWarnings False
  17.     db.Execute strDeleteAnswer, dbFailOnError
  18.     DoCmd.SetWarnings True
  19.  
  20. End If
  21.  
  22. '*****************************************************
  23. intCodeGroup = 2
  24. '*****************************************************
  25. 'Delete any existing answer levels
  26. If DCount("ID", "tblAnswerLevel", "CrimeID_fk = " & lngCrimeID) > 0 Then
  27.     Dim strDeleteAnswerLevel As String
  28.     strDeleteAnswerLevel = "DELETE * FROM tblAnswerLevel " & _
  29.                            "WHERE CrimeID_fk = " & lngCrimeID
  30.  
  31.     DoCmd.SetWarnings False
  32.     db.Execute strDeleteAnswerLevel, dbFailOnError
  33.     DoCmd.SetWarnings True
  34.  
  35. End If
  36.  
  37. '*****************************************************
  38. intCodeGroup = 3
  39. '*****************************************************
  40. 'Set query to get the decision tree info
  41. Dim strQuestionLevel As String
  42. strQuestionLevel = "SELECT * FROM tblQuestionLevel " & _
  43.                    "WHERE PenalCodeID_fk = " & lngPenalCode & _
  44.                    " ORDER BY [Order]"
  45.  
  46. Dim rstQuestionLevel As DAO.Recordset
  47. Set rstQuestionLevel = db.OpenRecordset(strQuestionLevel)
  48.  
  49. If rstQuestionLevel.RecordCount > 0 Then
  50.     'Set query for where to put the decision tree info
  51.     Dim strAnswerLevel As String
  52.     strAnswerLevel = "SELECT * FROM tblAnswerLevel " & _
  53.                      "WHERE CrimeID_fk = " & lngCrimeID
  54.  
  55.     Dim rstAnswerLevel As DAO.Recordset
  56.     Set rstAnswerLevel = db.OpenRecordset(strAnswerLevel)
  57.  
  58.  
  59.  
  60.     'Setup recordset for where to get the questions
  61.     Dim strQuestion As String
  62.     strQuestion = "SELECT * FROM tblQuestion ORDER BY QOrder"
  63.  
  64.     Dim rstQuestion As DAO.Recordset
  65.     Set rstQuestion = db.OpenRecordset(strQuestion)
  66.  
  67.  
  68.     'Setup recordset for where to put the questions
  69.     Dim strAnswer As String
  70.     strAnswer = "SELECT * FROM tblAnswer"
  71.  
  72.     Dim rstAnswer As DAO.Recordset
  73.     Set rstAnswer = db.OpenRecordset(strAnswer)
  74.  
  75.  
  76.  
  77.     'Loop through tblQuestionLevel and put the information in tblAnswerLevel
  78.     Dim strQuestionFilter As String
  79.     Dim rstFiltered As DAO.Recordset
  80.  
  81.     Dim lngAnswerLevelID As Long
  82.     Dim lngQuestionLevelID As Long
  83.  
  84.     '**********************************************************
  85.     intCodeGroup = 4
  86.     '**********************************************************
  87.     With rstAnswerLevel
  88.         rstQuestionLevel.MoveFirst
  89.  
  90.         Do While Not rstQuestionLevel.EOF
  91.             .AddNew
  92.             !CrimeID_fk = lngCrimeID
  93.             !QuestionLevelID_fk = rstQuestionLevel!LevelID
  94.             lngAnswerLevelID = !ID
  95.             .Update
  96.  
  97.             '*********************************************************************
  98.             intCodeGroup = 5
  99.             '*********************************************************************
  100.             'Loop through the questions under the current question level
  101.             lngQuestionLevelID = rstQuestionLevel!LevelID
  102.             strQuestionFilter = "QLevel_fk = " & lngQuestionLevelID
  103.             rstQuestionLevel.Filter = strQuestionFilter
  104.  
  105.             Set rstFiltered = rstQuestionLevel.OpenRecordset()
  106.  
  107.             Do While Not rstFiltered.EOF
  108.                 rstAnswer.AddNew
  109.                 rstAnswer!AnswerLevelID = lngAnswerLevelID
  110.                 rstAnswer!QuestionID_fk = rstFiltered!QuestionID
  111.                 rstAnswer!CrimeID_fk = lngCrimeID
  112.                 rstAnswer.Update
  113.  
  114.                 .MoveNext
  115.             Loop
  116.  
  117.  
  118.             .MoveNext
  119.         Loop
  120.     End With
  121.  
  122. End If
  123.  
  124.  
  125.  
  126. LoadDecisionTree_Exit:
  127.  
  128.     rstQuestionLevel.Close
  129.     Set rstQuestionLevel = Nothing
  130.  
  131.     If Not rstAnswerLevel Is Nothing Then
  132.         rstAnswerLevel.Close
  133.         Set rstAnswerLevel = Nothing
  134.     End If
  135.  
  136.     If Not rstQuestion Is Nothing Then
  137.         rstQuestion.Close
  138.         Set rstQuestion = Nothing
  139.     End If
  140.  
  141.     If Not rstAnswer Is Nothing Then
  142.         rstAnswer.Close
  143.         Set rstAnswer = Nothing
  144.     End If
  145.  
  146.     If Not rstFiltered Is Nothing Then
  147.         rstFiltered.Close
  148.         Set rstFiltered = Nothing
  149.     End If
  150.  
  151.     Set db = Nothing
  152.  
  153.  
  154.     Exit Sub
  155.  
  156.  
  157.  
  158. LoadDecisionTree_Error:
  159.     MsgBox "Error Number: " & Err.Number & vbCrLf & _
  160.            "Description: " & Err.Description & vbCrLf & _
  161.            "Module: modPublicSubs > LoadDecisionTree" & vbCrLf & _
  162.            "Code Group: " & intCodeGroup
  163.  
  164.     Resume LoadDecisionTree_Exit
  165.  
  166. End Sub
I have stepped through the code and verified the .RecordCount for each recordset is what it should be, so I know that my queries are correct and that the recordsets are opening properly.

You are right Z, they do the same thing for the querydefs. However, the explanation for the parameters says that the Name parameter is required. For some reason my error message is saying that it expected 2 parameters, but only 1 parameter is required according to your link. Strange. I looked at Recordset.OpenRecordset and the syntax only lists Type and Options parameters, but the parameter list lists Name, Type, Options, and LockEdit (here is the link: Recordset.OpenRecordset). I ran into this awhile back, but I can't remember which way was correct.

By the way, my excerpt came from lines 102 - 105
Feb 4 '13 #4

zmbd
Expert Mod 5K+
P: 5,056
As your code mirrors very close to what I've in my test DB and appears to work in the example Recordset.Filter Property (DAO) Office 2010

I'm suspect of your filter string - may be a red herring; hoewver, debug print the string value a 102 verify that the filter string is valid.
Feb 4 '13 #5

Seth Schrock
Expert 2.5K+
P: 2,881
I will check that when I get home this evening (I don't have access to my database right now), but I'm almost positive that it is correct.
Feb 4 '13 #6

ADezii
Expert 5K+
P: 8,584
The MSDN Example appears to have a Typo, replace:
Expand|Select|Wrap|Line Numbers
  1. Set rest = dbs.OpenRecordset(_ 
  2.     "SELECT * FROM Customers WHERE LastVisitDate BETWEEN Date()-60 " & _
  3.     "AND Date()-30 ORDER BY LastVisitDate DESC")
  4.  
with:
Expand|Select|Wrap|Line Numbers
  1. Set rst = dbs.OpenRecordset(_ 
  2.     "SELECT * FROM Customers WHERE LastVisitDate BETWEEN Date()-60 " & _
  3.     "AND Date()-30 ORDER BY LastVisitDate DESC")
  4.  
and see what happens.
Feb 4 '13 #7

Seth Schrock
Expert 2.5K+
P: 2,881
I had already seen that. I didn't use rest anywhere in my code.
Feb 4 '13 #8

ADezii
Expert 5K+
P: 8,584
I ran comparable Code against the Northwind Sample Database and had no problems:
Expand|Select|Wrap|Line Numbers
  1. Dim dbs As DAO.Database
  2. Dim rst As DAO.Recordset
  3. Dim rstFiltered As DAO.Recordset
  4. Dim strCity As String
  5.  
  6. Set dbs = CurrentDb
  7.  
  8. 'Create the first filtered Recordset
  9. Set rst = dbs.OpenRecordset("SELECT * FROM Employees WHERE [Region] = 'WA'")
  10.  
  11. Debug.Print "**************************"
  12.  
  13. 'Begin row processing
  14. Do While Not rst.EOF
  15.   'Retrieve the name of the first city in the selected rows
  16.   strCity = rst!City
  17.  
  18.   'Now Filter the Recordset to return only the Employees from that City
  19.     rst.Filter = "City = '" & strCity & "'"
  20.     Set rstFiltered = rst.OpenRecordset
  21.  
  22.     'Process the Rows
  23.     With rstFiltered
  24.       Do While Not .EOF
  25.         Debug.Print ![City]
  26.         Debug.Print ![LastName]
  27.         Debug.Print ![FirstName]
  28.         Debug.Print ![Title]
  29.         Debug.Print "**************************"
  30.           rstFiltered.MoveNext
  31.       Loop
  32.     End With
  33.  
  34.     'We've done what was needed. Now exit
  35.     Exit Do
  36.     rst.MoveNext
  37. Loop
  38.  
  39. 'Cleanup
  40. rstFiltered.Close
  41. rst.Close
  42.  
  43. Set rstFiltered = Nothing
  44. Set rst = Nothing
  45.  
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. **************************
  2. Seattle
  3. Davolio
  4. Nancy
  5. Sales Representative
  6. **************************
  7. Seattle
  8. Callahan
  9. Laura
  10. Inside Sales Coordinator
  11. **************************
Feb 4 '13 #9

Seth Schrock
Expert 2.5K+
P: 2,881
Well then, the problem must not be with rst.OpenRecordset not having enough parameters and must be with one of my recordsets or with the filter string. If it is with my recordset, then it would probably be with rstQuestionLevel. If it is with the filter string, I must have typed the field name wrong. If it is something else, I'm lost.
Feb 4 '13 #10

ADezii
Expert 5K+
P: 8,584
Why not Post your Code in its entirety?
Feb 4 '13 #11

Rabbit
Expert Mod 10K+
P: 12,195
@Adezii, I think they did in post #4.
Feb 4 '13 #12

Seth Schrock
Expert 2.5K+
P: 2,881
Your correct Rabbit, the whole procedure is in post #4.
Feb 4 '13 #13

ADezii
Expert 5K+
P: 8,584
@Rabbit
Thanks Rabbit, just another Senior Moment!
Feb 4 '13 #14

Seth Schrock
Expert 2.5K+
P: 2,881
Duh... I filtered the wrong recordset. I don't think that I would have ever looked there if you hadn't pointed it out as a possible point of trouble Z. Classic example of the reason for good naming. The filter worked on the correct recordset, but I just entered the wrong one.

There were also a few errors because of the With statement, so for anyone who looks at this in the future, here is the tested version that works:
Expand|Select|Wrap|Line Numbers
  1. Public Sub LoadDecisionTree(lngPenalCode As Long, lngCrimeID As Long)
  2. On Error GoTo LoadDecisionTree_Error
  3. Dim intCodeGroup As Integer
  4.  
  5. Dim db As DAO.Database
  6. Set db = CurrentDb
  7.  
  8. '*****************************************************
  9. intCodeGroup = 1
  10. '*****************************************************
  11. 'Delete any existing answers
  12. If DCount("ID", "tblAnswer", "CrimeID_fk =" & lngCrimeID) > 0 Then
  13.     Dim strDeleteAnswer As String
  14.     strDeleteAnswer = "DELETE * FROM tblAnswer WHERE CrimeID_fk = " & lngCrimeID
  15.  
  16.     DoCmd.SetWarnings False
  17.     db.Execute strDeleteAnswer, dbFailOnError
  18.     DoCmd.SetWarnings True
  19.  
  20. End If
  21.  
  22. '*****************************************************
  23. intCodeGroup = 2
  24. '*****************************************************
  25. 'Delete any existing answer levels
  26. If DCount("ID", "tblAnswerLevel", "CrimeID_fk = " & lngCrimeID) > 0 Then
  27.     Dim strDeleteAnswerLevel As String
  28.     strDeleteAnswerLevel = "DELETE * FROM tblAnswerLevel " & _
  29.                            "WHERE CrimeID_fk = " & lngCrimeID
  30.  
  31.     DoCmd.SetWarnings False
  32.     db.Execute strDeleteAnswerLevel, dbFailOnError
  33.     DoCmd.SetWarnings True
  34.  
  35. End If
  36.  
  37. '*****************************************************
  38. intCodeGroup = 3
  39. '*****************************************************
  40. 'Set query to get the decision tree info
  41. Dim strQuestionLevel As String
  42. strQuestionLevel = "SELECT * FROM tblQuestionLevel " & _
  43.                    "WHERE PenalCodeID_fk = " & lngPenalCode & _
  44.                    " ORDER BY [Order]"
  45.  
  46. Dim rstQuestionLevel As DAO.Recordset
  47. Set rstQuestionLevel = db.OpenRecordset(strQuestionLevel)
  48.  
  49. If rstQuestionLevel.RecordCount > 0 Then
  50.     'Set query for where to put the decision tree info
  51.     Dim strAnswerLevel As String
  52.     strAnswerLevel = "SELECT * FROM tblAnswerLevel " & _
  53.                      "WHERE CrimeID_fk = " & lngCrimeID
  54.  
  55.     Dim rstAnswerLevel As DAO.Recordset
  56.     Set rstAnswerLevel = db.OpenRecordset(strAnswerLevel)
  57.  
  58.  
  59.  
  60.     'Setup recordset for where to get the questions
  61.     Dim strQuestion As String
  62.     strQuestion = "SELECT * FROM tblQuestion ORDER BY QOrder"
  63.  
  64.     Dim rstQuestion As DAO.Recordset
  65.     Set rstQuestion = db.OpenRecordset(strQuestion)
  66.  
  67.  
  68.     'Setup recordset for where to put the questions
  69.     Dim strAnswer As String
  70.     strAnswer = "SELECT * FROM tblAnswer"
  71.  
  72.     Dim rstAnswer As DAO.Recordset
  73.     Set rstAnswer = db.OpenRecordset(strAnswer)
  74.  
  75.  
  76.  
  77.     'Loop through tblQuestionLevel and put the information in tblAnswerLevel
  78.     Dim strQuestionFilter As String
  79.     Dim rstFiltered As DAO.Recordset
  80.  
  81.     Dim lngAnswerLevelID As Long
  82.     Dim lngQuestionLevelID As Long
  83.  
  84.     '**********************************************************
  85.     intCodeGroup = 4
  86.     '**********************************************************
  87.     With rstAnswerLevel
  88.         rstQuestionLevel.MoveFirst
  89.  
  90.         Do While Not rstQuestionLevel.EOF
  91.             .AddNew
  92.             !CrimeID_fk = lngCrimeID
  93.             !QuestionLevelID_fk = rstQuestionLevel!LevelID
  94.             lngAnswerLevelID = !ID
  95.             .Update
  96.  
  97.             '*********************************************************************
  98.             intCodeGroup = 5
  99.             '*********************************************************************
  100.             'Loop through the questions under the current question level
  101.             lngQuestionLevelID = rstQuestionLevel!LevelID
  102.             strQuestionFilter = "QLevel_fk = " & lngQuestionLevelID
  103.             rstQuestion.Filter = strQuestionFilter
  104.  
  105.             Set rstFiltered = rstQuestion.OpenRecordset()
  106.  
  107.             Do While Not rstFiltered.EOF
  108.                 rstAnswer.AddNew
  109.                 rstAnswer!AnswerLevelID_fk = lngAnswerLevelID
  110.                 rstAnswer!QuestionID_fk = rstFiltered!QuestionID
  111.                 rstAnswer!CrimeID_fk = lngCrimeID
  112.                 rstAnswer.Update
  113.  
  114.                 rstFiltered.MoveNext
  115.             Loop
  116.  
  117.  
  118.             rstQuestionLevel.MoveNext
  119.         Loop
  120.     End With
  121.  
  122. End If
  123.  
  124.  
  125.  
  126. LoadDecisionTree_Exit:
  127.  
  128.     rstQuestionLevel.Close
  129.     Set rstQuestionLevel = Nothing
  130.  
  131.     If Not rstAnswerLevel Is Nothing Then
  132.         rstAnswerLevel.Close
  133.         Set rstAnswerLevel = Nothing
  134.     End If
  135.  
  136.     If Not rstQuestion Is Nothing Then
  137.         rstQuestion.Close
  138.         Set rstQuestion = Nothing
  139.     End If
  140.  
  141.     If Not rstAnswer Is Nothing Then
  142.         rstAnswer.Close
  143.         Set rstAnswer = Nothing
  144.     End If
  145.  
  146.     If Not rstFiltered Is Nothing Then
  147.         rstFiltered.Close
  148.         Set rstFiltered = Nothing
  149.     End If
  150.  
  151.     Set db = Nothing
  152.  
  153.  
  154.     Exit Sub
  155.  
  156.  
  157.  
  158. LoadDecisionTree_Error:
  159.     MsgBox "Error Number: " & Err.Number & vbCrLf & _
  160.            "Description: " & Err.Description & vbCrLf & _
  161.            "Module: modPublicSubs > LoadDecisionTree" & vbCrLf & _
  162.            "Code Group: " & intCodeGroup
  163.  
  164.     Resume LoadDecisionTree_Exit
  165.  
  166. End Sub
Feb 4 '13 #15

NeoPa
Expert Mod 15k+
P: 30,736
Seth,

May I suggest that you design your code in smaller, discrete, chunks. That is one of the principles of procedural programming. Another highly recommended approach is to declare all variables at the top of a procedure whether or not the language forces such discipline. I believe many languages do, but Basics, of all varieties, come from a pedigree of toy languages pitched at the most basic of levels, and deliberately avoided imposing disciplines due to the intended audience. It was only later that it started to be pitched at a more professional audience and options like insisting on variable declaration were bolted on. It is important because when trying to read or maintain existing code, hunting through acres of code for these important declarative lines makes a hard job much harder.

These two suggestions should do much to help with your code, and particularly the maintenance of it afterwards.

Clearly, when asking questions on it, it will also prove invaluable. Very few will look at such an enormous piece of code just to get to a starting point wherefrom one may be able to help. As it's you, and you have already contributed so much yourself already, I'm sure one or two of us will on this occasion, but it's never going to be fun.
Feb 4 '13 #16

Seth Schrock
Expert 2.5K+
P: 2,881
I actually used to put all of my variable declarations at the top of my procedures, but I then I thought it would be easier to find if it was next to the first time the variable was used. I will however, stop doing that and put it at the top as you suggest.

I was also just reading about coupling and cohesion last night. At the time, I thought was doing okay in that department, but evidently not. I see I could break out the checking for existing records/deleting them into their own procedure as that is working on a different thing than the actual loading of the decision tree (which is what the name of the sub says is happening).

Thank-you for pointing this out NeoPa. I want to do things the correct way and you are helping me achieve that :)
Feb 4 '13 #17

NeoPa
Expert Mod 15k+
P: 30,736
Seth:
I want to do things the correct way and you are helping me achieve that :)
I know you do Seth ;-) Good for you.

It was the main reason I decided to post.
Feb 5 '13 #18

Post your reply

Sign in to post your reply or Sign up for a free account.