Public variables not holding value between modules

[Gail]

The application I am building consists of SQL 7.0 data
tables and an Access 2000 project front end. The data is
accessed through unbound forms (since it is intended to be
a client/server application with multiple users). A stand-
alone VBA module houses the functions that control data
access, recordset creation, etc. The user starts with a
switchboard that can open one of three primary data
levels. Other forms can be opened from each other form. A
typical progression would be: Switchboard -- Customer
Master (unique customer number key) -- Customer Contacts
(multiple contacts possible for each customer number) --
Customer Items (multiple items possible for each customer
number). Since the working recordset is recreated with
each form progression, closing the most recent form does
not return you to the same data you had in the previous
form. In an effort to control this, I created a public
variable to collect the name of the "parent" form and pass
it to a text control on the child form.

My global variable ParentFormName correctly picks up the
name of the active form, but then is reset to null by the
time it gets to the called form. I have other global
variables that pass data from form to form and are working
correctly. This is the only one that passes "other than
control data". I don't know how it is being reset. Any
ideas? All global variables are declared in the stand-
alone module as public.

Thanks in Advance.
Gail

Code Sample:
On Click EP of Switchboard button, value of ParentFormName
becomes Switchboard:
stDocName = "ubfCustomerMaster"
ParentFormName = Screen.ActiveForm.Name
Me.ReturnFlag = 1
DoCmd.OpenForm stDocName

On Activate EP Code of the CustomerMaster Form, value of
ParentFormName is Null:
If Me.ReturnFlag = 1 Then 'returning to parent form
with customer number
Me.Filter = "custnum = 'CustomerNumber'"
Me.FilterOn = True
Me.tbxRecordCount = uf_DisplayRecord(Me, 1)
If Me.tbxRecordCount > 0 Then
Me.tbxRecordNumber = 1
Else 'returning to parent form with no value in
customer number
Me.Filter = ""
Me.FilterOn = False
Me.tbxRecordCount = Null
Me.tbxRecordNumber = Null
uf_NewRecord Me
Me.FlagEdited = 0
Me.FlagFind = 1
End If
Else 'direct opening of customer master form
Me.tbxRecordCount = uf_DisplayRecord(Me,
1) 'one of the unbound form functions
If Me.tbxRecordCount > 0 Then Me.tbxRecordNumber =
1
Me.FlagFind = 1
Me.ParentFormName = ParentFormName
End If

 

[solex]

Gail,

Your design sounds very confusing. You say that you are working with
Unbound forms but yet you have supplied code that suggest that the forms are
bound (e.g. Me.Filter ...). If your goal is to be able to return values
from children form then perhaps you should treat them like functions instead
of using Global variables which are difficult to debug case in point.

I have been using the following model for quite some time and it works but
it relies on the fact that if you need to have data updated based on the
values in a child form then the child form must be opened modally. My model
is simple and basically treats the children forms as function. For instance
in your CustomerMaster form say you want to call your CustomerContact and
want to determine if the user has decided to save a customer contact you
could create a function like this (I place this function in a module called
FormHandler, so it can be called from multiple places if need):

Public Function ShowCustomerContact(ByVal CustomerContactID as Long) as
Boolean
Dim formName As String
Dim where As String
Dim theForm As Form_CustomerContact
formName = "CustomerContact "

where = "[CustomerContactID]=" & CStr(CustomerContactID )
DoCmd.OpenForm formName, acNormal, , where, acFormEdit, acDialog, ""

Set theForm = Application.Forms(formName)

If Not (theForm Is Nothing) Then
ShowCustomerContact= theForm.Success
Else
ShowCustomerContact= False
End If

Set theForm = Nothing

DoCmd.Close acForm, formName
End Function

=========>And in your CustomerContact Form you would have the following
code:

Private mflgSuccess As Boolean

Public Property Get Success() As Boolean
Success = mflgSuccess
End Property

Private Sub cmdCancel_Click()
Me.Undo
mflgSuccess = False
Me.Visible = False
End Sub

Private Sub cmdSave_Click()
mflgSuccess = True
Me.Visible = False
End Sub

 

[Gail]

I couldn't agree more that it is very confusing. I've been
trying to learn the SQL/Access connection as I go, and the
book I've been reading uses the unbound form concept. But
my application of it may not be appropriate. Thanks for
the suggestions, I'll take a look at them and see what I
can can do.

Gail

-----Original Message-----
Gail,

Your design sounds very confusing. You say that you are working with
Unbound forms but yet you have supplied code that suggest that the forms are
bound (e.g. Me.Filter ...). If your goal is to be able to return values
from children form then perhaps you should treat them like functions instead
of using Global variables which are difficult to debug case in point.

I have been using the following model for quite some time and it works but
it relies on the fact that if you need to have data updated based on the
values in a child form then the child form must be opened modally. My model
is simple and basically treats the children forms as function. For instance
in your CustomerMaster form say you want to call your CustomerContact and
want to determine if the user has decided to save a customer contact you
could create a function like this (I place this function in a module called
FormHandler, so it can be called from multiple places if need):

Public Function ShowCustomerContact(ByVal CustomerContactID as Long) as
Boolean
Dim formName As String
Dim where As String
Dim theForm As Form_CustomerContact
formName = "CustomerContact "

where = "[CustomerContactID]=" & CStr (CustomerContactID )
DoCmd.OpenForm formName, acNormal, , where, acFormEdit, acDialog, ""

Set theForm = Application.Forms(formName)

If Not (theForm Is Nothing) Then
ShowCustomerContact= theForm.Success
Else
ShowCustomerContact= False
End If

Set theForm = Nothing

DoCmd.Close acForm, formName
End Function

=========>And in your CustomerContact Form you would have the following
code:

Private mflgSuccess As Boolean

Public Property Get Success() As Boolean
Success = mflgSuccess
End Property

Private Sub cmdCancel_Click()
Me.Undo
mflgSuccess = False
Me.Visible = False
End Sub

Private Sub cmdSave_Click()
mflgSuccess = True
Me.Visible = False
End Sub

The application I am building consists of SQL 7.0 data
tables and an Access 2000 project front end. The data is
accessed through unbound forms (since it is intended to be
a client/server application with multiple users). A stand-
alone VBA module houses the functions that control data
access, recordset creation, etc. The user starts with a
switchboard that can open one of three primary data
levels. Other forms can be opened from each other form. A
typical progression would be: Switchboard -- Customer
Master (unique customer number key) -- Customer Contacts
(multiple contacts possible for each customer number) --
Customer Items (multiple items possible for each customer
number). Since the working recordset is recreated with
each form progression, closing the most recent form does
not return you to the same data you had in the previous
form. In an effort to control this, I created a public
variable to collect the name of the "parent" form and pass
it to a text control on the child form.

My global variable ParentFormName correctly picks up the
name of the active form, but then is reset to null by the
time it gets to the called form. I have other global
variables that pass data from form to form and are working
correctly. This is the only one that passes "other than
control data". I don't know how it is being reset. Any
ideas? All global variables are declared in the stand-
alone module as public.

Thanks in Advance.
Gail

Code Sample:
On Click EP of Switchboard button, value of ParentFormName
becomes Switchboard:
stDocName = "ubfCustomerMaster"
ParentFormName = Screen.ActiveForm.Name
Me.ReturnFlag = 1
DoCmd.OpenForm stDocName

On Activate EP Code of the CustomerMaster Form, value of
ParentFormName is Null:
If Me.ReturnFlag = 1 Then 'returning to parent form
with customer number
Me.Filter = "custnum = 'CustomerNumber'"
Me.FilterOn = True
Me.tbxRecordCount = uf_DisplayRecord(Me, 1)
If Me.tbxRecordCount > 0 Then
Me.tbxRecordNumber = 1
Else 'returning to parent form with no value in
customer number
Me.Filter = ""
Me.FilterOn = False
Me.tbxRecordCount = Null
Me.tbxRecordNumber = Null
uf_NewRecord Me
Me.FlagEdited = 0
Me.FlagFind = 1
End If
Else 'direct opening of customer master form
Me.tbxRecordCount = uf_DisplayRecord(Me,
1) 'one of the unbound form functions
If Me.tbxRecordCount > 0 Then Me.tbxRecordNumber =
1
Me.FlagFind = 1
Me.ParentFormName = ParentFormName
End If

.