Public Variables Do Not Work in Navigation Form RRS feed

  • Question

  • I have three unbound forms (Home, Client, Contract) that use ADO to connect to an Azure SQL database. The Home form allows users to query the database for client names. If the name does not exist the user is prompted to create a new client. The Client form allows the user to enter client info and when they click submit the values are inserted to the database. The user can then choose to enter some contract information. A Contract form is provided for entering the info and it's also inserted when they click submit. 

    Okay, so...

    The Home form has some Public, module-level variables to capture the name entered by the user. Then the code for the Client form can reference those public variables. 

    So at the top of the module for Home is:

    Option Compare Database

    Public ClientFirstName

    Public ClientMiddleName

    Public ClientLastName

    Then in the module for the Client form, procedures access the values in these variables like this:

    Me.txtFirstName.Value = Form_Home.ClientFirstName

    Me.txtMiddleName.Value = Form_Home.ClientMiddleName

    Me.txtLastName = Form_Home.ClientLastName

    This all works just fine, UNTIL..I decided to create a Navigation form and pull the Home form into it. The other two forms (Client and Contract) are set as modal pop up forms. Now my public variables do not populate. I can have a statement like:

    Form_Home.ClientFirstName = "Earl"

    and the ClientFirstName public variable never gets the value. I didn't change ANYTHING about the code. I only pulled the Home form into the navigation. 

    I hope this is enough information for someone to help me.


    • Edited by TodAtWork Saturday, October 15, 2016 8:57 PM fixed spelling
    Saturday, October 15, 2016 8:55 PM


All replies

  • I don't work with navigation form for that very reason. I think it creates another level of subforms, so the reference would change to something like myNavigationForm.mySubform.ClientFirstName

    By the way, you don't have Option Explicit in this module. This is must for all modules. Nothing good can come from not having it. Also set it to be the default for new forms (VBA window > Tools > Options > Require variable declaration).

    -Tom. Microsoft Access MVP

    Saturday, October 15, 2016 9:01 PM
  • I use Option Explicit, I just didn't put it in my example.

    I guess I can pass those values instead of relying on public variables. I want to control the user experience as much as possible, and navigation seems like a good way to do it. 


    Saturday, October 15, 2016 9:05 PM
  • If you're looking for alternatives, here is one:

    (in a standard module)
    public g_Client as clsClient

    (in a class module named clsClient)
    private m_strFirstName as string

    public property Get FirstName() as String
    end property

    public property Let FIrstName(byval strFirstName as string)
    m_strFirstName = strFirstName
    end property

    (populate this class when user selects a client)
    g_Client.FirstName = Me.txtFirstName

    (use anywhere)
    Msgbox "Client=" & g_Client.FirstName

    -Tom. Microsoft Access MVP

    Saturday, October 15, 2016 9:22 PM
  • Hi Tod,

    If you're using Access 2010, another alternative might be is to use TempVars.

    Just my 2 cents...

    • Marked as answer by TodAtWork Monday, October 17, 2016 5:47 PM
    Sunday, October 16, 2016 4:53 PM
  • TempVars work fine for simple data types like String and Integer, which is all we need in this case. They do not work for complex ones like custom class objects, collections, recordset objects, etc.

    -Tom. Microsoft Access MVP

    Sunday, October 16, 2016 4:57 PM
  • This looks like my solution. Thanx. 
    Monday, October 17, 2016 5:46 PM
  • This looks like my solution. Thanx. 

    Hi Tod,

    You're welcome! Tom and I were happy to assist. When your public variable requirements get a little more complex, don't forget to revisit Tom's suggestion.

    Good luck with your project.

    Monday, October 17, 2016 5:56 PM
  • Thanx for turning me on to TempVars. I'm only now getting back around to working on this project, but TempVars did the trick. I also like the greater control I have vs module level variables or passing arguments.


    Sunday, November 20, 2016 5:00 PM
  • Hi Tod,

    Glad to hear you're finding more uses with TempVars. Cheers!

    Sunday, November 20, 2016 11:19 PM