Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    17

    Question Unanswered: Requery goes to first record..help!!!

    Hi! I am still a newbie with Access VBA so this might sound real easy for you (hopefully)!

    Basically I have a form called "TRDX_Cal_frm" and on that form, a subform called "TRDXCalHistory_subform". I append certain data from the form to the subform with a control button and I requery the data on the subform to have an instant update so the user can see that it was added.
    The problem is when I requery, the subform goes back to the first record, which I do not want. I need it to stay at the same record, or loop back to that record.
    Is there a simple way to do this?

    Any help or suggestions would be great!

    Thanks!

    Owen

  2. #2
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244

    Re: Requery goes to first record..help!!!

    Include the key field in the subform (hidden if you like). Store the value of the key of the current record in a variable. Do the Requery. Do a "FindFirst" in the RecordSetClone of the subform to find the key value. Bookmark the record. Set the subform's .Bookmark property to the same bookmark.

    Hope this helps!!

    By the way, in case you're wondering why not simply bookmark the record, do the requery and then return to the bookmark, the problem is that the Requery erases any bookmarks you've set.
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

  3. #3
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    17

    Re: Requery goes to first record..help!!!

    That sounds exactly what I need to do, however I would need more details as to how to do it exactly, I am an idiot when it comes to writing code.

    Thanks!

    Owen

    Originally posted by andybriggs
    Include the key field in the subform (hidden if you like). Store the value of the key of the current record in a variable. Do the Requery. Do a "FindFirst" in the RecordSetClone of the subform to find the key value. Bookmark the record. Set the subform's .Bookmark property to the same bookmark.

    Hope this helps!!

    By the way, in case you're wondering why not simply bookmark the record, do the requery and then return to the bookmark, the problem is that the Requery erases any bookmarks you've set.

  4. #4
    Join Date
    Mar 2004
    Posts
    10

    Re: Requery goes to first record..help!!!

    Originally posted by Owen34
    That sounds exactly what I need to do, however I would need more details as to how to do it exactly, I am an idiot when it comes to writing code.

    Thanks!

    Owen
    OR YOU CAN JUST SIMIPLY SORT YOUR RECORDS DESC BY DATE OF UPDATE OR SORT BY ID DESC (IF YOU HAVE THE FIELD)

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Make sure you requery the subform and not the form or the subform control. I am assuming you are doing:

    Me.TRDXCalHistory_subform.Requery
    or
    TRDXCalHistory_subform.Requery


    However if you requery the subform's form like this:

    Me.TRDXCalHistory_subform.Form.Requery

    I think you should see the added records without it moving to the first record.

  6. #6
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    17
    THANKS!!

    This works perfectly!

    Originally posted by DCKunkle
    Make sure you requery the subform and not the form or the subform control. I am assuming you are doing:

    Me.TRDXCalHistory_subform.Requery
    or
    TRDXCalHistory_subform.Requery


    However if you requery the subform's form like this:

    Me.TRDXCalHistory_subform.Form.Requery

    I think you should see the added records without it moving to the first record.

  7. #7
    Join Date
    May 2002
    Posts
    395

    Re:Bookmark a form's recordset

    Originally posted by andybriggs
    Include the key field in the subform (hidden if you like). Store the value of the key of the current record in a variable. Do the Requery. Do a "FindFirst" in the RecordSetClone of the subform to find the key value. Bookmark the record. Set the subform's .Bookmark property to the same bookmark.

    Hope this helps!!

    By the way, in case you're wondering why not simply bookmark the record, do the requery and then return to the bookmark, the problem is that the Requery erases any bookmarks you've set.
    _________________
    I read your post on recordsetclone for the subform that is where I need the help on.
    I have a main form, a subform, and a nested subform. The subform’s a query based on a filter table.
    The subform has two navigation buttons to move records. I’m able to write the record when it first moved to it but when I moved back to it and edit the record an error displayed: “Either BOF or EOF is true, or the current record has beed deleted. Request operation requires a current record.” Both of the navigation buttons are longer working. I did a debug on recordset using recordcount property it showed there is one record which is incorrect. I had a feeling I need to bookmark the record. I am not sure why since I created a recordset base on the form. I hope you can help!
    Thank you!

    Private Sub cmdPrevious_Click()
    Dim rst As New ADODB.Recordset
    On Error GoTo Err_cmdPrevious_Click

    Set rst = Me.Recordset
    Debug.Print rst.RecordCount

    If Me!cmdNext.Enabled = False Then
    Me!cmdNext.Enabled = True
    End If

    rst.MovePrevious
    rst.MovePrevious

    If rst.BOF Then
    rst.MoveFirst
    Me!cmdNext.SetFocus
    Me!cmdPrevious.Enabled = False
    Else
    rst.MoveNext
    End If

    Exit_cmdPrevious_Click:
    Exit Sub

    Err_cmdPrevious_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrevious_Click

Posting Permissions

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