Results 1 to 10 of 10
  1. #1
    Join Date
    May 2004
    Posts
    159

    Unanswered: Recordset puzzle

    I am puzzled by the behavior of this code I am trying to udpate.
    I wanted to split the database and have a backend with all the tables.
    This code will not run after I moved the table to the BE. I get an "invalid operation" error
    I worked on it and thought I had it and changed the recordset to dynaset instead of table but then I get a new error "the operation is not supported"
    so how do I fix this to work on a BE? Help appreciated. Using Access 2003

    Dim strReqName As String
    Dim strContName As String
    Dim strReqType As String
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim rstReqName As DAO.Recordset

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblRequestor", dbOpenTable)

    With rst

    .Index = "primarykey"
    .MoveLast
    .MoveFirst
    .Seek "=", cboReqName.Value

    End With

    Me.Refresh

    strReqName = Me!cboReqName.Column(0)
    strContName = Me!cboReqName.Column(1)
    strReqType = Me!cboReqName.Column(2)
    gstrReqName = strReqName
    gstrReqType = strReqType
    gstrContName = strContName

    Me.Recalc
    Me.Refresh

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    From the help file:

    You can't use the Seek method on a linked table because you can't open linked tables as table-type Recordset objects. However, if you use the OpenDatabase method to directly open an installable ISAM (non-ODBC) database, you can use Seek on tables in that database.


    tc
    Last edited by tcace; 03-22-07 at 17:12.

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I would go further and drop that method altogether. Why open a recordset on an entire table when you just want one record? Open it on an SQL string instead, with a WHERE clause that pulls that one record.
    Paul

  4. #4
    Join Date
    May 2004
    Posts
    159
    Well in the next step it does pull data from several fields after that record is found. I will look into how to use the opendatabase method in this instance

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    How many fields you access is irrelevant. You only want one record. Try it; you'll like it.
    Paul

  6. #6
    Join Date
    May 2004
    Posts
    159
    Quote Originally Posted by pbaldy
    How many fields you access is irrelevant. You only want one record. Try it; you'll like it.
    sure .. could you give me an example of how it should be formatted to get me started? My SQL is not so good.

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Set rst = dbs.OpenRecordset("SELECT * FROM tblRequestor WHERE FieldName =" & Me.cboReqName, dbOpenDynaset))
    Paul

  8. #8
    Join Date
    May 2004
    Posts
    159
    Thanks! here is what I got to work.
    ***********************************************
    Set dbs = CurrentDb

    Set rst = dbs.OpenRecordset("SELECT * FROM tblRequestor", dbOpenDynaset)
    With rst

    .MoveFirst
    Me!cboReqName = .Fields(0)

    End With

    Me.Refresh
    ******************************
    I used it in several other places with some modification and did not have to use the invalid "seek" function. Thanks again

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I can't see how that would work to pull one record, given that the SELECT statement has no WHERE clause, thus will pull all records. It's really no different than what you had before.
    Paul

  10. #10
    Join Date
    May 2004
    Posts
    159
    I tried using this SQL per your example but I get an error saying "too few parameters, expected 1"
    strreqname is a string with the cbo selection and reqname is the fieldname

    Set rst = dbs.OpenRecordset("SELECT * FROM tblRequestor WHERE REQName = strReqName", dbOpenDynaset)
    Last edited by WilliamS; 04-05-07 at 12:13.

Posting Permissions

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