Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Aug 2002
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Rec Filter (Access 2000)

    I have a table with field intSequenceNum. I'd like to create a form button to increase the Sequence Number. I am using the following code:

    Private Sub cmdPlus1_Click()

    Dim db As Database
    Dim rec As Recordset

    Set db = CurrentDb()
    Set rec = db.OpenRecordset("tblAdminInfo")

    Dim strCriteria As String
    strCriteria = "[AdHeaderID]=" & "'" & Me![AdHeaderID] & "'"

    rec.Filter = strCriteria

    Do While Not rec.EOF

    rec.Edit
    rec!intSequenceNUM = rec!intSequenceNUM + 1
    rec.Update
    rec.MoveNext

    Loop

    End Sub

    When I try to run this I get error "Operation not supported for this object"

    Any clues what I'm doing wrong?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rec Filter (Access 2000)

    For the future: if you report an error, it is useful to tell us on which line it occurs. Now we have to guess.

    The code you posted is DAO code.
    <UL><LI>Check that you have a reference to DAO: select Tools | References... (in the Visual Basic Editor) and make sure that Microsoft DAO 3.6 Object Library is ticked.
    <LI>To avoid confusion, make DAO declarations explicit:

    Dim db As DAO.Database
    Dim rec As DAO.Recordset

    <LI>Specify how you want to open the recordset:

    Set rec = db.OpenRecordset("tblAdminInfo", dbOpenDynaset)

    <LI>Is AdHeaderID a text field? If so, you should be OK, but if it is numeric, you shouldn't put quotes around the value, but use this:

    strCriteria = "[AdHeaderID]=" & Me![AdHeaderID]

    <LI>Do you really have multiple records with the same AdHeaderID? If there is only one, you don't need the Do While ... Loop.[/list]HTH

  3. #3
    New Lounger
    Join Date
    Aug 2002
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rec Filter (Access 2000)

    Thanks for response. AdHeaderID is numeric so I removed the quotes but still get the error. It happens on :

    rec.Filter=strCriteria

    I do have the DAO 3.6 library clicked and I'm using the Do While Loop because each Header ID relates to many records in my tblAdminInfo

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rec Filter (Access 2000)

    Have you specified dbOpenDynaset as option in OpenRecordset? That works for me.
    If you omit it, the code will try to open the recordset with the dbOpenTable option, and that doesn't support the Filter method.
    If you still have problems:
    - Is tblAdminInfo a linked table, and if so, where does the original live?
    - Insert a line Debug.Print strCriteria immediately above the problem line; copy the result from the Immediate window and paste it into a reply.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Rec Filter (Access 2000)

    Do you realize that you aren't actually applying a filter to the recordset? You're trying to do the same thing you do with a form, and that doesn't work on a recordset. To get a filtered recordset, you do something like this:

    Dim recFilter as DAO.Recordset
    Dim db As DAO.Database
    Dim rec As DAO.Recordset
    Dim strCriteria As String

    Set db = CurrentDb()
    Set rec = db.OpenRecordset("tblAdminInfo")

    strCriteria = "[AdHeaderID]=" & "'" & Me![AdHeaderID] & "'"

    rec.Filter = strCriteria
    Set recFilter = rec.OpenRecordset(dbDynaset)

    This gives you a filter recordset, recFilter, which only contains the records specified in strCriteria
    Charlotte

  6. #6
    New Lounger
    Join Date
    Aug 2002
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rec Filter (Access 2000)

    Hans, Charlotte,

    Thank you so very much....ONLY

    When I add "dbOpenDynaset" to my original code like Hans suggest, the code runs but It doesn't filter. I'm increasing all the sequence numbers in my table.

    And When I use Charlottes' code, the line

    Set rec.Filter = rec.OpenRecordset(dbDynaset)

    I now get an error that says "invalid argument"


    Larry

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rec Filter (Access 2000)

    Try this then, it opens a recordset based on an SQL statement with the WHERE condition built in, so that you don't need to set a filter separately:

    Set rec = db.OpenRecordset("SELECT * FROM tblAdminInfo WHERE [AdHeaderID]=" & _
    Me![AdHeaderID], dbOpenDynaset)

    Do While Not rec.EOF
    ...
    Loop

    Note: the underscore _ indicates that the instruction is continued on the following line; there must be a space between & and _

  8. #8
    New Lounger
    Join Date
    Aug 2002
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rec Filter (Access 2000)

    Yay!!! That does it Hans...Thanks a bunch.

    Larry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •