Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2007
    Posts
    61

    Unanswered: Return form to same record after Requery

    Access 2010. My skills: learning.

    I have inherited a system and am trying to improve it. On the Home screen are various buttons which select the same data depending on different criteria (eg All outstanding Jobs, Today's outstanding Jobs, Tomorrow's outstanding Jobs). This takes you to a Datasheet View and then you double click on a particular cell to get that record open in a Form View where you can update the Job and do various things. So on that Form View there is a button to Create Invoice which opens a Subform and once you click on the Create Invoice button on the Subform it returns you to the Job's form that you were looking at previously. On the Jobs form there is a box to display the Invoice Number, but it remains empty and I need it to show the newly created invoice number (which it will do if I go back to the Main Menu and start over and go back to the same Job, so I know the Invoicing bit works fine).

    So I tried Refresh, but that doesn't work. So then I tried Requery and that works, BUT it moves the Form back to Record 1 of howevermany. Google has found me a bunch of potential solutions, but none of them have worked so far.

    My favourite attempt so far has been something like:

    Dim varBookmark As Variant
    varBookmark = Me.Bookmark
    Me.Requery
    Me.Bookmark = varBookmark

    The above code works right up to the line Me.Bookmark = varBookmark. I have set Watch on both Bookmark and varBookmark and they both contain apparently valid entries, however at the line Me.Bookmark = varBookmark I get "Method 'Bookmark' of object '_Form_Jobs' failed". I am running this on my laptop with no other users and no data is deleted. I have tried varieties of the above and at one point I even got something that did not throw any error, but the form stubbornly remained on record 1 of howevermany (which is always more than one).

    I also tried a different approach:

    Dim CurrentRecord As Integer
    CurrentRecord = Me.CurrentRecord
    Me.Requery
    DoCmd.GoToRecord , , acGoTo, CurrentRecord

    With the above I get: "Run-time error '2015': You can't go to the specified record."

    I even tried:

    DoCmd.GoToRecord , , acGoTo, 4

    and got the same error.

    I have also tried:

    DoCmd.GoToRecord acActiveDataObject, , acGoTo, acNext

    and whilst this does not error, it still stubbornly stays on Record 1 after the Refresh.

    I feel like this ought to be simple and straightforward, but having spent several days chasing my own tail, can anyone suggest what the blindingly obvious thing is that I am missing, please?

    TIA,
    Caspar

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    630
    Provided Answers: 35
    open the form on the 1 id,

    docmd.openform "myform",,,"[id]=" & txtID

    then when you go back, docmd.openform "myform"
    it will still be on that 1 record.

  3. #3
    Join Date
    Jul 2007
    Posts
    61
    Quote Originally Posted by ranman256 View Post
    open the form on the 1 id,

    docmd.openform "myform",,,"[id]=" & txtID

    then when you go back, docmd.openform "myform"
    it will still be on that 1 record.
    I regret that I don't understand the above suggestion. The main Jobs form is already open, then you click on Create Invoice to open the subform and when you click on Print Invoice on that subform it closes the subform, saves and prints the invoice and returns you to the main Jobs form which has remained open underneath, so you cannot open it again. What am I missing?

    Many thanks,
    Caspar

Posting Permissions

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