Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Nov 2009
    Posts
    223

    Unanswered: Buttons on a Sub-Form Menu footer don't work



    Hi,

    I have created a Menu footer I use as a sub-form in the Form Footer on most of my Forms.

    Currently, the only button on the Menu is an "Exit This Page" button which I created simply by using the standard Wizard. This works fine and closes the Form which it is attached to.

    I tried to add a button in the same way to "Add New Record" and "Delete Record" but when I click, none of them work !!!!

    If anybody could please guide me in the right direction it would be greatly appreciated.

    I am using Access 2003.

    Thanks,

    Paul

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    "none of them work" does not help a lot in understanding what the problem can be. Please post the code that is linked to both command buttons.
    Have a nice day!

  3. #3
    Join Date
    Nov 2009
    Posts
    223
    Hi Sinndho,

    Mmm, yes I suppose "does not work" is not very helpful!!! What I mean is when is Click the buttons....nothing happens....I mean nothing. But when I Click the Exit button, then it closes the Form as it should do.

    Here are the codes:-

    Private Sub Exit_This_Page_Click()
    On Error GoTo Err_Exit_This_Page_Click


    DoCmd.Close

    Exit_Exit_This_Page_Click:
    Exit Sub

    Err_Exit_This_Page_Click:
    MsgBox Err.Description
    Resume Exit_Exit_This_Page_Click

    End Sub


    Private Sub Command7_Click()
    On Error GoTo Err_Command7_Click


    DoCmd.GoToRecord , , acNewRec

    Exit_Command7_Click:
    Exit Sub

    Err_Command7_Click:
    MsgBox Err.Description
    Resume Exit_Command7_Click

    End Sub

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    As the DoCmd action does not concern the active object (i.e. the subform where the command button is), you must specify to which form the action must be applied:
    Code:
    DoCmd.GoToRecord acDataForm, "<FormName>", acNewRec
    Where <FormName> is the name of the parent (or main) form.
    Have a nice day!

  5. #5
    Join Date
    Nov 2009
    Posts
    223

    Red face

    Cheers, Sinndho.

    I am a bit confused. Because the "Close Page" button worked without reference to any Parent page, I just presumed the 'Delete' and 'Go To Last Record' Buttons would work in the same manner? No worries.

    Thanks for the code - I changed it with the name of the Form and got the following error message.

    Code:
    DoCmd.GoToRecord acDataForm, "<Contract Form>", acNewRec
    The object '<Contract Form>' isn't open.

    But my main reason for wanting a Menu in a SubForm is because I have the same Menu on most of my Forms - so any changes I make to the one Menu/Subform will automatically apply to all the Forms that include the Menu/Subform. So I only have to make the change once - have I explained that OK??

    So, I don't really want to specify the name of one particular Form to which the Menu/Subform is connected - I would like it to be 'general'. can I somehow specify a Parent Form which would close whichever Form the SubForm was attached to??

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Remove the '<' and '>' from the name of the form.

    For retrieving the name of the parent form in any circumstances, you can use:
    Code:
    Dim strParent As String
    strParent = Me.Parent.Name
    DoCmd.GoToRecord acDataForm, strParent, acNewRec
    or shorter:
    Code:
    DoCmd.GoToRecord acDataForm, Me.Parent.Name, acNewRec
    If you're interested in building a general (universal) buttons bar, have a look at the attached database: it provides a full solution.
    Attached Files Attached Files
    Have a nice day!

  7. #7
    Join Date
    Nov 2009
    Posts
    223
    YIPPEDEE DOO DAA, it works like a treat

    Thanks very much Sinndho for guiding me through this problem. My database is looking much better now.

    Cheers,

    Paul

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  9. #9
    Join Date
    Nov 2009
    Posts
    223
    I am getting into areas that I don't understand - scary!!

    The Exit button works without refernce to the Parent. I guess that si because I am closing a Form (not actioning a record?).

    I successfully changed the coding for the Add New Record button - thanks to Sinndho.

    Now I added a Delete This Record button which "does not work". How can I change the coding so that it refers to the Parent page again?

    This is the coding created by the wizard:-

    Private Sub Command9_Click()
    On Error GoTo Err_Command9_Click


    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70


    Exit_Command9_Click:
    Exit Sub

    Err_Command9_Click:
    MsgBox Err.Description
    Resume Exit_Command9_Click

    End Sub

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. According to the documentation of Access 2003 (MSDN): "In Microsoft Access 97, the DoMenuItem method was replaced by the RunCommand method. The DoMenuItem method is included in this version of Microsoft Access only for compatibility with previous versions."

    2. Again, this kind of command depends on the context and works with the active object (here a form). When you click on a button in a (sub)form, this (sub)form is the active object. Unfortunately, contrarily to the GotoRecord method, DoMenuItem does not allow you to optionally specify the object to which the command must be applied nor does RunCommand. You could try to set the focus on a control of the parent form, making the form the active one (not sure, I haven't tested this).

    3. There is another solution that consists in deleting the current record of the parent's recordset, then move to the next record:
    Code:
    Private Sub Command_Delete_Click()
    
        Dim rst As DAO.Recordset
        
        Set rst = Me.Parent.Recordset
        If Not rst.EOF Then 
            rst.Delete
            rst.MoveNext
        End If
        Set rst = Nothing
        
    End Sub
    Have a nice day!

  11. #11
    Join Date
    Nov 2009
    Posts
    223
    Hi Sinndho,

    It does not seem logical that buttons created using the wizard produce the following results when trying to close a parent for from a subform:-

    1. Close Form -works correctly
    2. Add New Record - can work with a simple modification.
    3. Delete Record - is difficult to resolve

    Why would a wizard produce code (DoMenuItem) if it has already been replaced in Access 97?

    I will try the code you mentioned and keep my fingers crossed but I don;t understand any of it

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I don't know why the wizard in Access 2000 and Access 2003 still uses the deprecated syntax (I can guess, though). All I did was repeat what's written in the documentation of Access 2003 and on Microsoft's MSDN site: DoMenuItem Method [Access 2003 VBA Language Reference]. I wanted to verify if there were additional parameters (like with GotoRecord) that could solve the problem.

    I tested the code before posting it and it works. The only problem you could encounter with it is that any possible states of the parent form and its recordset are not tested: no recordset (unbound form), no parent form, etc.

    If you really want to systematically work with a subform that contains the command buttons of its parent form, you should seriously consider examining the sample database I posted or create an equivalent mechanism, probably an event-driven one, which is the most logical way of doing it (that's how Access itself works), but that can be difficult to understand if you're not familiar with programming with events.
    Have a nice day!

  13. #13
    Join Date
    Nov 2009
    Posts
    223
    Sinndho,

    I made a wizard created 'Delete Record' button. then deleted all the code and pasted your code in the OnClick field.

    When i click the button - "nothing happens". No delete of record and no error message?

    Any ideas what I should do from here?

    Cheers.
    Last edited by reddevil1; 08-07-11 at 14:14.

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Is the code being executed when you click on the command button?
    Have a nice day!

  15. #15
    Join Date
    Nov 2009
    Posts
    223
    I am not sure how to check if the code is being executed?

    When i click the button - absolutely nothing happens, apart from a ding-dong sound from my computer. No error messages, no action, no movement, no nothing??

Posting Permissions

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