Results 1 to 5 of 5
  1. #1
    catluvr is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2018

    Trying to understand Public vs. Private Variables/Functions

    Hello All!

    I help manage a membership database. The membership varies quite a bit year to year. As a result, I have a folder for each membership year and, at the end of the year, I copy that year's file into the next year's folder and rename it. This works for me, and isn't my question.

    I have a few forms and reports that have a label at the top that says "Membership Year: 2018-2019" -- this is just an un-associated label. (the membership year is based on the school year)

    Anyway, I usually forget to go to each of the forms and update the membership year. I know it could be kept in a table, but I don't know if that's the best idea ... wouldn't I have to include that table in each query? And as part of each form's data?

    Since I include the year in the database file name, I decided I could create VBA code that would generate what I wanted and have that available to all the forms and reports.

    So, if the database file name is Membership1819.accdb, the code, through a series of string functions, would generate "2018 - 2019" This code works, except ... is it supposed to be a function? A subroutine? I assume I'd place the code in a Module if I want it accessible to all the forms and reports. But, then, how do I use it?

    I know, a lot of questions, but I've looked on-line for 3 hours and poured over three different VBA books and I'm not getting anywhere.

    So far, I have a module named Custom Functions, and in that I have:

    Public Function MembYear()

    The dim statements that are all defining strings

    The string functions that get the file name, do some string manipulation, and end with:

    YearString = BegString & " - " & EndString (which is "2018 - 2019")

    End Function

    Am I close?

    How would I use it in a label that is at the top of a form? How about on a report? Actually, I guess I'd need a text box, since I don't think you can build expressions in labels, even un-associated ones, right?

    I tried "Membership Year: " & MembYear() which returns: Membership Year:

    Thank you in advance for all your help!

    A grateful Kansan,


  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    The Great Land
    Correct, use a textbox instead of a label. Textbox ControlSource you show is correct.

    To return value, function must have line:

    MembYear = BegString & " - " & EndString

    (YearString variable appears totally unnecessary.)

    This will be required for every custom function. Must set function name to value to be returned to calling procedure. Otherwise it just uses default for whatever data type the function is declared as and if not explicitly declared defaults to Variant type.

    Or use a table and textbox expression can use DLookup().
    How to attach file: To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    You have to set the return value for the function:
    Public Function MembYear() as string
        dim this and that
       MembYear = BegString & " - " & EndString 
    End Function
    This should work now:
    I tried "Membership Year: " & MembYear()
    Last edited by davegri; 01-25-2019 at 05:27 PM. Reason: format

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Ontario, Canada
    Since a function can return a value (where a sub cannot) your function needs to result in a return value to something. Thus
    Function MyFunction () AS SOME TYPE or
    Public Function MembYear() As String
    If you need to pass parameters to the function, they go inside the ()
    In design view, you can "grab" every control that will call the function and enter = MembYear(), probably in any event you want, and do them en masse. I've only done this for command button click events so far, but can't imagine why you couldn't use the method. I see you trying this on the textbox control and assigning the function to its recordsource (control has to be unbound). I'd say you're correct - has to be a control.

    Thread title doesn't seem to reflect a general question as opposed to a specific one, but I'll post a link that may help understand scope if you need that.

    In your example, you've neglected to set the return value of the function so that the control can receive it:
    MembYear = BegString & " - " & EndString ' (which is "2018 - 2019")
    You don't necessarily need a variable as well.
    Last edited by Micron; 01-25-2019 at 05:29 PM. Reason: code correction
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  5. #5
    catluvr is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2018
    Thank you All!

    I added "as String" to my Public Function line and changed the YearString to be MembYear and it works just as I'd hoped!

    I'm excited to learn something new.


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

Similar Threads

  1. Public Functions and Private Subs
    By d9pierce1 in forum Programming
    Replies: 15
    Last Post: 11-28-2018, 09:25 AM
  2. Replies: 2
    Last Post: 07-16-2017, 05:48 PM
  3. Changing private sub dim to public sub dim
    By Stephenson in forum Programming
    Replies: 14
    Last Post: 10-05-2015, 02:59 PM
  4. Turning Private Function Into Public
    By g4tv4life in forum Programming
    Replies: 1
    Last Post: 02-04-2014, 05:31 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 - Senior Forums