Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Jun 2004
    Posts
    24

    Arrow Unanswered: Me.Requery sends form to Record#1

    In Access 2000, I have a form AuctionForm, bound to a SQL table called AUCTION. A subform on the AuctionForm has an AfterUpdate event that connects to the underlying SQL datbase to effect some changes in various tables, and at the end of the event I want to refresh AuctionForm to ensure that any changes in the AUCTION table are reflected in the display. I initially used a Requery statment to do this, and I could swear that it worked... but lately I've found that the Requery statement bounces the AuctionForm display back to the first record in AUCTION. A Refresh statement will do the same thing. I need AuctionForm to update itself without moving to a different record.

    3 questions:
    1) Why is this happening?
    2) Aren't Refresh and Requery supposed to be used for updating Access form displays?
    3) If not, what am I supposed to use?

    Thanks.

  2. #2
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    you coul bookmark the record and recall after requery.
    ghozy.

  3. #3
    Join Date
    Jun 2004
    Posts
    24
    Quote Originally Posted by ghozy
    you coul bookmark the record and recall after requery.
    Here's the code I've currently got, which might be what you are suggesting. But when it runs the Requery statement, the form reverts to Record #1, and then the remaining code does NOT put it back to the record where it started, although it looks like it should:

    ' Refresh AuctionForm to send new values to AuctionForm controls
    Dim auPK As Long
    auPK = Forms.AuctionForm!EbayNum 'gets key of current record
    Forms.AuctionForm.Requery
    Forms.AuctionForm.RecordsetClone.FindFirst "[EbayNum]=" & auPK
    Forms.AuctionForm.Bookmark = Forms.AuctionForm.RecordsetClone.Bookmark

  4. #4
    Join Date
    Jun 2004
    Posts
    24
    One more thing I've discovered from Microsoft:
    "In an Access project (.adp), the refresh and requery actions both requery the records."

    So that explains why my form keeps bouncing back to record #1. So, I guess that's to be expected. So what I really need to know is why my bookmarking code (see previous post) isn't putting the form back to the record that I want?

  5. #5
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    is your project using DAO or ADO? I assume it is ADO?
    Last edited by ghozy; 10-08-04 at 17:30. Reason: typo
    ghozy.

  6. #6
    Join Date
    Jun 2004
    Posts
    24
    Duplicate post.
    Last edited by whill96205; 10-08-04 at 17:33. Reason: Duplicate post

  7. #7
    Join Date
    Jun 2004
    Posts
    24
    Quote Originally Posted by ghozy
    is your project using DAO or ADO? I assume it is ADO?
    I believe it is ADO.

  8. #8
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    please check your references to see if it is ADO or DAO. because ADO doesn't support FindFirst Method whihc I see in your code. bookmarking code is differs if it is ADO or DAO.
    ghozy.

  9. #9
    Join Date
    Jun 2004
    Posts
    24
    ghozy, My bad. I didn't copy/paste the code here, I retyped it -- and I put FindFirst when I meant to just put Find. FindFirst produces an error indicating it is not a supported method. My code is actually:

    ' Refresh AuctionForm to send new values to AuctionForm controls
    Dim auPK As Long
    auPK = Forms.AuctionForm!EbayNum 'gets key of current record
    Forms.AuctionForm.Requery
    Forms.AuctionForm.RecordsetClone.Find "[EbayNum]=" & auPK
    Forms.AuctionForm.Bookmark = Forms.AuctionForm.RecordsetClone.Bookmark

  10. #10
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    I tried to replicate your error but I couldn't. it seems Access using DAO recordsets behind the forms. maybe it is different with adp projects?
    ghozy.

  11. #11
    Join Date
    Jun 2004
    Posts
    24
    So when you tried the code in your Access database (MDB) it worked -- the form requeried and then went back to the designated record?

    If so... then perhaps the code needs to be different for my ADP. But how?

  12. #12
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    I do it perfectly with DAO. with following code.
    Code:
    Private Sub Command0_Click()
    	Dim MyBookmark As Variant
    	MyBookmark = Me.Recordset.Bookmark
    	Me.Requery
    	Me.Recordset.Bookmark = MyBookmark
    End Sub
    ghozy.

  13. #13
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    ghozy,

    This is weird: I've never tried to use the Bookmark property in this situation because of the following extract from Help.

    "You can use bookmarks in any form that is based entirely on Microsoft Access tables. However, other database products may not support bookmarks. For example, you can't use bookmarks in a form based on a linked table that has no primary index."

    "Requerying a form invalidates any bookmarks set on records in the form. However, clicking Refresh on the Records menu doesn't affect bookmarks."


    Postscript. Ah I've just realised that your saved value is not affected by the requery. Nice one, I must remember this in future.
    Last edited by Rod; 10-09-04 at 03:15.
    Rod

    fe_rod@hotmail.com

  14. #14
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    yes, this works in most cases. I think there are few query types that won't support bookmarks. but I can't remember the details.
    ghozy.

  15. #15
    Join Date
    Jun 2004
    Posts
    24
    Quote Originally Posted by ghozy
    I do it perfectly with DAO. with following code.
    Code:
    Private Sub Command0_Click()
    	Dim MyBookmark As Variant
    	MyBookmark = Me.Recordset.Bookmark
    	Me.Requery
    	Me.Recordset.Bookmark = MyBookmark
    End Sub
    I'll try this bookmark approach... But I'm pretty sure I've got ADO, not DAO. What's the difference, and can I "invoke" one in my code, or am I stuck with whichever is the default?

Posting Permissions

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