Public Variables Do Not Work in Navigation Form

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.

tod

Answer 1

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

Answer 2

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. 

tod

Answer 3

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
FirstName=m_strFirstName
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

Answer 4

Hi Tod,

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

Just my 2 cents...

Answer 5

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

Answer 6

This looks like my solution. Thanx. 

Answer 7

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.

Answer 8

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.

tod

Answer 9

Hi Tod,

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