Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Sep 2003
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Going to last record in subform (Access 2000)

    Hi,

    I am trying to workout how to go to the last record in a subform.
    I have used a macro to do this in a form but it appears not to work in a subform.
    I would appreciate if you could tell me if what l am doing is possible.

    This is done in Access 2000

    Regards
    Justin

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Going to last record in subform (Access 2000)

    Here is the technique I use. I'm assuming your subform control is named subMisc (the actual formname it contains is irrelevant). This code executes from the mainform.
    me.subMisc.form.recordsetclone.movelast
    me.subMisc.form.bookmark = me.subMisc.form.RecordsetClone.bookmark
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    New Lounger
    Join Date
    Sep 2003
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Going to last record in subform (Access 2000)

    Edited by HansV to reduce huge screenshot in size. It caused horizontal scrolling. Please keep attached images small! Thanks.

    Hi Mark,

    I tried to place this code in the form but had a few problems in doing so.
    See attachment for my queries.

    Justin
    Attached Images Attached Images

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

    Re: Going to last record in subform (Access 2000)

    If you want to use a command button on the main form to go to the last record in the subform, the code goes into the On Click event of the command button.

    By the way, do yourself a BIG favor and give your controls more meaningful names than Command37, Text19 and List8. If you or somebody else has to perform maintenance on the database in the future, you will save a lot of time if the name of a control says something about its purpose. A convention used by many developers is to use a three letter prefix, such as cmd for command button, txt for text box, lst for list box etc., followed by a descriptive name without spaces in which each word starts with an upper case letter. The command button in question could be named cmdGoToLast, for example.

    The On Click code could look like this, if the name of the subform as a control on the main form is sbfSomething:

    Private Sub cmdGoToLast_Click()
    Me.sbfSomething.SetFocus
    DoCmd.GoToRecord , , acLast
    End Sub

  5. #5
    New Lounger
    Join Date
    Sep 2003
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Going to last record in subform (Access 2000)

    Edited by HansV to reduce huge screenshot in size. It caused horizontal scrolling. I repeat: please keep attached images small! Thanks.

    I might not have explained myself properly.
    What l would like is to go the last record automatically and not on a command button.
    I have used the code you specified but still have problems.
    If l used a control then the user would have to click on it every time he enters the form.
    Is is possible to make this happen automatically?

    See attachment.

    Regards
    Justin
    Attached Images Attached Images

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

    Re: Going to last record in subform (Access 2000)

    The screenshot you posted (please don't post 1024 by 768 images, they cause horizontal scrolling) shows that you have just created an On Click event for the form as a whole. That is probably not what you want.

    If you want to activate the last record in the subform automatically, you can do it in the On Current event of the main form. When the user opens the main form, or moves to another record, this event will be fired.
    <UL><LI>Open the main form in design view.
    <LI>Activate the Event tab of the Properties window.
    <LI>Click in the On Current event.
    <LI>Select Event Procedure from the dropdown list.
    <LI>Click the builder button, i.e. the three dots ... to the right of the dropdown arrow.
    <LI>Make the code look like this:

    Private Sub Form_Current()
    Me.subformname.SetFocus
    RunCommand acCmdRecordsGoToLast
    End Sub

    where subformname must be replaced by the name of the subform as a control on the main form. You can find this name by clicking once on the subform in the main form (in design view), and looking at the caption of the Properties window. This name can be different from the name that the subform has in the database window.
    <LI>Close and save the form.[/list]

  7. #7
    New Lounger
    Join Date
    Sep 2003
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Going to last record in subform (Access 2000)

    I tried this but got an error, see attachment.

    Regards
    Justin
    Attached Images Attached Images

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

    Re: Going to last record in subform (Access 2000)

    You seem to be writing the code in a general module, not in the module behind the form. And there is no space after Sub.

    Please read my post again. I provided very detailed instructions.

  9. #9
    New Lounger
    Join Date
    Sep 2003
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Going to last record in subform (Access 2000)

    I am sorry l don't understand what you mean.

    I followed the instructions several times over and over again but everytime appear to be in general mode.
    I am a bit confused as to what l am doing wrong.
    Please can you explain where l am going wrong?

    Many Thanks
    Justin

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

    Re: Going to last record in subform (Access 2000)

    The code in your last screenshot shows

    Private SubcmdGotoLast_Click()

    Even with the missing space inserted, this has nothing to do with the instructions I posted. The instructions are very detailed, I would not know how to make it still clearer than that. Sorry.

Posting Permissions

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