Answered by:
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
- Edited by TodAtWork Saturday, October 15, 2016 8:57 PM fixed spelling
Saturday, October 15, 2016 8:55 PM
Answers
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.
tod
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 stringpublic property Get FirstName() as String
FirstName=m_strFirstName
end propertypublic 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
- Proposed as answer by Edward8520Microsoft contingent staff Monday, October 17, 2016 5:31 AM
Saturday, October 15, 2016 9:22 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.
tod
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