Results 1 to 8 of 8
  1. #1
    MTM is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    20

    DoCmd.RunCommand acCmdRecordsGoToLast not working after Form.Requery

    I am using a button located in Subform "Lot_EntryForm"

    Private Sub Command57_Click()
    Dim objActiveForm As Form
    Set objActiveForm = Forms!NavTab_Form_NU_2!Admin_Tabbed_Form_Nu2!Lot_E ntryForm!Lot_No_subform5.Form
    objActiveForm.Requery


    DoCmd.RunCommand acCmdRecordsGoToLast
    End Sub

    Requery works fine, but I can not get the cursor to go to the last record

    Form Lot_No_subform5 is in datasheet format if that makes a difference

    In Immediate mode, if I click anywhere on the "Lot_No_subform5" form and execute
    DoCmd.RunCommand acCmdRecordsGoToLast the cursor moves to the last record

    I can not get it to do this from the button... any ideas?

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If this is a form on a navigation form or a subform on a form on a navigation form, you should make that clear. The clue would seem to be that it works when the said form has the focus, so I'd try objActiveForm.SetFocus before goto (if that's the right form, but as I'm saying, it's not clear). If it isn't, then perhaps refer to the correct form. However, I don't see the need for a requery at all, and that makes the cursor go to the first record in a continuous or datasheet form. Nor do we know where the button is in relation to the form you want to goto a record on; that might be relevant. I suppose there is a noticeable difference and that it works as expected if, as you say, you select a record on the form, so it should be safe to say that the fact that it's a datasheet has no bearing.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    MTM is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    20
    Yes, this is a Navigation Form at the top level, with a tab form, next level, etc as my form names indicate (Forms!NavTab_Form_NU_2!Admin_Tabbed_Form_Nu2!Lot_E ntryForm!Lot_No_subform5.Form). I need to requery form "Lot_No_subform5" after data entry in another form that is open at the same time, hence the need for requery.
    When I tried your suggestion of "
    objActiveForm.SetFocus", I get an error "There is an invalid method in an expression"... strange. I replaced your suggestion with "Debug.Print objActiveForm.Name" and the immediate window prints the name of the form I want to "GoToLast","Lot_No_subform5". So I think the active form is correct.
    A further observation... in immediate mode, after Requery, the cursor highlights the first field of the first record as expected. If I do the immediate mode command "DoCmd.RunCommand acCmdRecordsGoToLast", the cursor does not move to the last record. However, if I click on any field, I do not highlight, then the immediate mode command runs to the last record. I do not know how to get VBA to put the form in this mode so the GoToLast works. Help?

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The error suggests that of the methods that the object may have, setfocus is not one of them.
    I need to requery form "Lot_No_subform5" after data entry in another form that is open at the same time
    This may be your issue if both forms are on a nav form. A navigation control on a nav form can only load one form at a time. When you switch tabs, the form you see closes and a different form is opened. If you're trying to do anything (e.g.go to a record, filter etc) based on some other form and that other form is part of the nav form then it isn't open at all.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    think you need to setfocus to the actual form, not an alias

    Forms!NavTab_Form_NU_2!Admin_Tabbed_Form_Nu2!Lot_E ntryForm!Lot_No_subform5.Form.setfocus

  6. #6
    MTM is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    20
    Thank you Micron and Ajax...
    I had already tried Ajax's Forms!NavTab_Form_NU_2!Admin_Tabbed_Form_Nu2!Lot_E ntryForm!Lot_No_subform5.Form.setfocus and this did not work, However, I have another Tab'd control that needs to update in the same way and I tried Ajax's setfocus and it worked. . The difference is there is only one Form associated with the Tab control and the Tab control that does not work has a form and then two subforms within it.
    I found some reference that you can't set focus on subforms.... seems like this is my problem.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If the form you want to get to is a subform on a navigation subform what you tried doesn't look right. The syntax for a navigation subform is
    Code:
    [Forms]![NavigationForm]![NavigationSubform].[Form]![controlName]
    Unless you changed the default names, then the first two are usually called exactly what I've posted. Your posted example that you say doesn't work does not include the reference to [Form] and that would not be optional. If you remain stuck, post back but you must make it clear what you are trying to reference (subform on a form / subform on a navigation subform / subform on a subform on a navigation subform, etc). You must also provide the exact names of the forms and the subform controls that might be on them. The subform control is sometimes, but not always, the same name as the form that it is on.

    EDIT - or you could post a compacted and zipped copy of your db.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I found some reference that you can't set focus on subforms
    I believe that's correct, but you don't need to do that, no? You need to set the focus (or perform some other action) to a control on that form, yes? That you can do:
    Code:
    forms!formname.subformcontrolname.form.controlName.setfocus
    Of course, the control must be able to receive the focus, and you cannot do this in the subform load or open event.

    EDIT- at the risk of repeating myself, that is not the correct syntax for navigation forms, but setting the focus to a control should be possible regardless.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 02-07-2019, 11:02 AM
  2. DoCmd.RunCommand acCmdSaveRecord Problem
    By musicopr in forum Programming
    Replies: 3
    Last Post: 06-23-2017, 06:45 PM
  3. Replies: 2
    Last Post: 09-27-2016, 09:10 PM
  4. Replies: 1
    Last Post: 03-30-2016, 12:49 PM
  5. DoCmd.RunCommand acCmdSaveRecord
    By Rick West in forum Programming
    Replies: 3
    Last Post: 04-22-2010, 02:52 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums