UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Recordser Filter Error 3061, Access 2010    
 
   
APJordaan
post Aug 22 2019, 05:50 AM
Post#1



Posts: 143
Joined: 2-January 13
From: Paarl, South Africa


QUOTE
Hi there
I keep an getting error 3061 (Too few parameters: Expected 2) with the following code. I'm not sure if the problem is the filter itself, or if it's something else. Please provide some help and lessen the stress levels dazed.gif
CODE
    Dim Db As DAO.Database
    Dim rstJcard As DAO.Recordset
    Dim strPos As String, strReason As String, lJcID As Long
    Set Db = CurrentDb()
    Set rstJcard = Db.OpenRecordset("JobcardDetailsV1", dbOpenDynaset)
    lJcID = Me.Parent.Form![ID_JobCard]
    strPos = InputBox("Which wheel position do you want to fit/remove?", _
            "Wheel Position Input")
    rstJcard.Filter = "WheelPos=" & Val(strPos) & " And JobcardID =" & lJcID & ""
    Set rstJcard = rstJcard.OpenRecordset
    rstJcard.MoveFirst
    rstJcard.MoveLast

The other option is that my approach might be wrong, so some more background:
I am trying to search the recordset for records matching the filter criteria in the code. There should never be more than two records that match the criteria.
So I'm trying to filter the records and then do a recordcount on the filtered result. My approach seemed the easiest, until I bumped into the error.
Thanks in advance!
Go to the top of the page
 
June7
post Aug 22 2019, 06:42 AM
Post#2



Posts: 795
Joined: 25-January 16



Don't need Form in reference to ID_JobCard. Even if you get that error resolved, will get another on the second Set rstJcard line. The recordset is already open and that is not correct syntax. I've never seen Filter property used like this. Build an SQL statement instead. Are both fields number type?
CODE
    Dim Db As DAO.Database
    Dim rstJcard As DAO.Recordset
    Dim strPos As String, strWHERE As String
    Set Db = CurrentDb()
    strPos = InputBox("Which wheel position do you want to fit/remove?", _
            "Wheel Position Input")
    strWHERE = "WheelPos=" & Val(strPos) & " And JobcardID =" & Me.Parent![ID_JobCard]
    Set rstJcard = Db.OpenRecordset("SELECT * FROM JobcardDetailsV1 WHERE " & strWHERE, dbOpenDynaset)
    rstJcard.MoveFirst
    rstJcard.MoveLast


Or use DCount() domain aggregate function and eliminate all that recordset stuff if you have no other reason for recordset than to get count.

x = DCount("*", "JobcardDetailsV1", strWHERE)

This post has been edited by June7: Aug 22 2019, 07:11 AM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
moke123
post Aug 22 2019, 08:09 AM
Post#3



Posts: 1,363
Joined: 26-December 12
From: Berkshire Mtns.


if your trying to use the Recordset.Filter property I think you need to declare a second recordset.

CODE
    Dim Db As DAO.Database

    Dim rstJcard As DAO.Recordset

    Dim rstFiltered As DAO.Recordset  
                                        
    Dim strPos As String, strReason As String, lJcID As Long

    Set Db = CurrentDb()
    Set rstJcard = Db.OpenRecordset("JobcardDetailsV1", dbOpenDynaset)

    lJcID = Me.Parent.Form![ID_JobCard]

    strPos = InputBox("Which wheel position do you want to fit/remove?", _
            "Wheel Position Input")


    rstJcard.Filter = "WheelPos=" & Val(strPos) & " And JobcardID =" & lJcID & ""

    Set rstFiltered = rstJcard.OpenRecordset    

    rstFiltered.MoveFirst
    rstFiltered.MoveLast



Go to the top of the page
 
ADezii
post Aug 22 2019, 10:14 AM
Post#4



Posts: 2,625
Joined: 4-February 07
From: USA, Florida, Delray Beach


If you are only interested in obtaining a Count, then June7 already gave you an answer in Post# 2. If you want to apply the Filter Property of a Recordset, then the following approach should work well for you:
CODE
Dim Db As DAO.Database
Dim rstJcard As DAO.Recordset
Dim rstFiltered As DAO.Recordset
Dim strPos As String, strReason As String, lJcID As Long
Dim strWhere As String

Set Db = CurrentDb()
Set rstJcard = Db.OpenRecordset("JobcardDetailsV1", dbOpenDynaset)

lJcID = Me.Parent.Form![ID_JobCard]

strPos = InputBox$("Which wheel position do you want to fit/remove?", _
                   "Wheel Position Input")
                  
strWhere = "[JobcardID] = " & lJcID & " AND [WheelPos] = " & Val(strPos)
  
If strPos = "" Or DCount("*", "JobcardDetailsV1", strWhere) = 0 Then
  rstJcard.Close: Set rstJcard = Nothing
    Exit Sub
End If

rstJcard.Filter = strWhere

Set rstFiltered = rstJcard.OpenRecordset

rstFiltered.MoveLast: rstFiltered.MoveFirst

MsgBox "Number of Filtered Records: " & rstFiltered.RecordCount

rstJcard.Close
rstFiltered.Close
Set rstJcard = Nothing
Set rstFiltered = Nothing
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th September 2019 - 05:47 AM