Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919

    VBA Can't find Public Function

    I have a form that uses a right-click popup menu to run a function. The function is Public within the code sheet of the form, yet VBA issues an error message indicating that it can't find the function. The popup menu properties specify =OneAudit() and the code clearly includes "Public Function OneAudit()".



    Any thoughts as to what might be going on here?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I think the function has to be in a standard module to be run from a menu, though I'm not positive.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    Hi Paul,
    No, I have many cases where the public function is coded in the code-sheet of the form that uses it. One of the reasons why I'm really mystified with the current caper.
    Bill

  4. #4
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    I did discover recently though that if invoked within a sub-form that the function needs to be coded AT LEAST in the parent form.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Okay. I haven't done a lot with menus, but when I did I always declared the functions in a standard module. Maybe I didn't have to. You've got other menus that call functions that way and the functions are in form modules?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    Yes. Other than the case I mentioned where the form was a sub-form and I had to move the function to the parent's form module, (which I refer to usually as the form's code sheet but I think "form module" makes more sense).

    Sometimes Access gets confused when the same name is used with two different objects, so I checked to be sure I didn't fall into that trap. The offending form open statement is a simple DoCmd in form sheet "up the chain" from where the problem occurs. I.e., DoCmd.OpenForm "frmAccounts" I'll try adding acDialog to see if that makes any difference but that's more of a fishing expedition than anything else.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Sorry Bill, not sure then. If you can attach or email the db I can play with it. I think I have 2003 on a VM somewhere.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    BTW, I'd just move the function to a general module were it not for the need the function has for clone access to the form's Recordsource and a bunch of "Me." references. If push comes to shove, I'll change the approach to use a double-click on the "Record Selector" column. Maybe someone else will see the post and have a thought.

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It would be simple enough to create a public function in a standard module, have it pop a message box, and see if calling that works. Maybe I'm all wet and it has nothing to do with where it is.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    Function in found when placed in a general module.

  11. #11
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    Finally! While I still do not know why A2003 can't find a Public Function contained in the form module of the form from which it is called via a right-click popup menu, it turns out in my case that I can capture the record ID of the record where the right-click action occurs in the Mouse_Down event and "through it over the wall" to the function via a global variable. The function now contained in a general module.

    (Paul, we had a similar issue back in March of 2013 in this forum, but I never hit on the idea of using the Mouse_Down to capture record ID's globally before the function got control. For me, I will use this technique as a standard method from now on and save myself a bunch of time.............. and also for everyone else that reads the posts.)

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad you got it working. I was able to grab the ID by referring to the form/subform from my function. Since you will have right-clicked on a record, that record has focus. So I pulled it instead of pushing it:

    VariableName = Forms!...
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    I tried the OnGotFocus event with my first attempt to capture the record ID "on my way to the function" but was unsuccessful.

    Thanks again for your thoughts,
    Bill

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If it wasn't clear, I didn't do it "on my way to the function", I grabbed it from within the function. Maybe your setup doesn't allow that.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  15. #15
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    You mean something like:

    Code:
    Dim AcID as Long
    AcID = Forms!frmAccounts.AcctID
    Where AcctID is the record ID of the record that has the focus?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Public variable not updating for each function
    By Ruegen in forum Programming
    Replies: 3
    Last Post: 08-28-2014, 06:26 PM
  2. Pass DAO.Recordset into public function
    By Ruegen in forum Programming
    Replies: 1
    Last Post: 07-07-2014, 12:31 AM
  3. Turning Private Function Into Public
    By g4tv4life in forum Programming
    Replies: 1
    Last Post: 02-04-2014, 05:31 PM
  4. Making a Public Function more flexible
    By sjs94704 in forum Programming
    Replies: 2
    Last Post: 12-10-2012, 02:21 PM
  5. Replies: 9
    Last Post: 12-20-2010, 08:05 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