Passing variable values between code modules.

[Steve S]

I have three variables defined in the dclarations section of a form as:

Public intNewFeeID As Long
Public strNewContestName As String
Public intNewContestID As Long
Option Compare Database

When I type any ao these variable names in ANOTHER form (to pass the value
fron one form to another) they are recognized. I know this because if I type
the the name in all lower case the correct letters are changed to upper case.
The problem is the values are not passed. I test this with a message box.
In for A they are what I expect but the first line the code for borm B they
show as nulls or blanks. How can I pass values from one form to another.

Thanks

Steve

 

[Jeff Boyce]

Steve

I may be remembering incorrectly, but I thought that only those variable
defined in code module (and not in forms & reports) were treated as
"share-able" within the .mdb file.

But rather than passing variable values from form to form, why not have
form2 "look at" form1 and get its values? Or even, if you MUST pass, use
the OpenArgs property and pass in values via OpenArgs.

Regards

Jeff Boyce
Microsoft Office/MVP
Microsoft IT Academy Program Mentor

 

[Ken Snell \(MVP\)]

Common error that causes this: you are "Dim"-ing the variable in the other
module as well. Don't do that.

If this isn't what you're doing, then you'll need to give us a lot more
detail about the code steps that are in the other modules, and details about
what you're doing with the forms.

 

[Ken Snell \(MVP\)]

Steve

I may be remembering incorrectly, but I thought that only those variable
defined in code module (and not in forms & reports) were treated as
"share-able" within the .mdb file.

They can be shared, but they must be qualified by the form's module
reference (just as one would do with calling a public procedure inside a
form's module), and of course the form must be open at the time that another
module wants to read the values. (The public variable essentially is a
read/write property of the form.)

But rather than passing variable values from form to form, why not have
form2 "look at" form1 and get its values? Or even, if you MUST pass, use
the OpenArgs property and pass in values via OpenArgs.

Or write the value into a hidden textbox on the form and read the value from
that textbox.

 

[Steve S]

OK Ken here is the code behind the two modules. As you can see I an not
"re-DIMing the variables. I read your responce to Jeff but did not
understand what you meant by "qualified"

Please help.
-----------------------------------------------------------------------------
Public strNewContestName As String
Public intNewContestID As Long
Option Compare Database
-------------------------------------------------------------------------------
Private Sub Command143_Click()
On Error GoTo Err_Command143_Click

' Test to see if this event is offered in the other contest
intNewContestID = DLookup("[LinkTo ID]", "[Contests]", _
"[Contest ID] = Forms![Team Entry]![Contest Ident]")

intNewFeeID = Nz(DLookup("[Fee ID]", "[Team Fees]", _
"([Contest ID] = intNewContestID) and " _
& "([Category ID]= Forms![Team Entry]![Category ID])"), 0)
strNewContestName = DLookup("[Contest Name]", "[Contests]", _
"[Contest ID] = intNewContestID")

' If not send message and exit
If intNewFeeID = 0 Then
MsgBox ("You can not move this team deffnition to the " &
strNewContestName _
& " contest because " & [Combo42].[Column](2) & " is not offered in
that contest")
Exit Sub
End If
MsgBox (intNewContestID & ", " & intNewFeeID & ", " & strNewContestName)
'Open the switch team form
DoCmd.OpenForm "Team Switch", , , "[ID]=" & Me![ID]

Exit_Command143_Click:
Exit Sub
------------------------------------------------------------------------------------
MSGBOX shows 12, 243, Open 2006

Form B is opened and click on the SAVE button runs this code.

MSGBOX shows , ,
------------------------------------------------------------------------------------------------

Private Sub Command6_Click()
On Error GoTo Command6_Click_Err

MsgBox (intNewContestID & ", " & intNewFeeID & ", " & strNewContestName)

' Zero to Fee ID
Forms![Team Switch]![Fee ID] = intNewFeeID

 

[Steve S]

OK Ken here is the code behind the two modules. As you can see I an not
"re-DIMing the variables. I read your responce to Jeff but did not
understand what you meant by "qualified"

Please help.
-----------------------------------------------------------------------------
Public strNewContestName As String
Public intNewContestID As Long
Option Compare Database

-------------------------------------------------------------------------------
Private Sub Command143_Click()
On Error GoTo Err_Command143_Click

' Test to see if this event is offered in the other contest
intNewContestID = DLookup("[LinkTo ID]", "[Contests]", _
"[Contest ID] = Forms![Team Entry]![Contest Ident]")

intNewFeeID = Nz(DLookup("[Fee ID]", "[Team Fees]", _
"([Contest ID] = intNewContestID) and " _
& "([Category ID]= Forms![Team Entry]![Category ID])"), 0)
strNewContestName = DLookup("[Contest Name]", "[Contests]", _
"[Contest ID] = intNewContestID")

' If not send message and exit
If intNewFeeID = 0 Then
MsgBox ("You can not move this team deffnition to the " &
strNewContestName _
& " contest because " & [Combo42].[Column](2) & " is not offered in
that contest")
Exit Sub
End If
MsgBox (intNewContestID & ", " & intNewFeeID & ", " &
strNewContestName)
'Open the switch team form
DoCmd.OpenForm "Team Switch", , , "[ID]=" & Me![ID]

Exit_Command143_Click:
Exit Sub

------------------------------------------------------------------------------------
MSGBOX shows 12, 243, Open 2006

Form B is opened and click on the SAVE button runs this code.

MSGBOX shows , ,

-----------------------------------------------------------------------------------------------

Private Sub Command6_Click()
On Error GoTo Command6_Click_Err

MsgBox (intNewContestID & ", " & intNewFeeID & ", " & strNewContestName)

' Zero to Fee ID
Forms![Team Switch]![Fee ID] = intNewFeeID

More code follows but the msgbox is what is important now

 

[Ken Snell \(MVP\)]

When you reference the "public" variable that is another form's module, you
must qualify it by including the reference to the module in which it's
located. Assuming that the form that "Dim"'s the variable is named "FormA",
you would use this syntax in FormB to get the value of the variable:
Form_FormA.intNewContestID

Another way to do the same thing is to declare in FormB a form object
variable that will represent FormA:
Dim frmFormA As Form
Set frmFormA = Forms("FormA")
TheValue = FormA.intNewContestID
Set frmFormA = Nothing

However, as Jeff correctly noted, it's much more common to put a public
variable in a "regular" module, not a form module, so that you then do not
need the "form" qualifier to reference it, and you don't need to worry about
whether FormA is open or not. Of course, you then must reset the variable to
a default value when you're done with it so that it doesn't "carry over"
unexpected values the next time you use it in, say, a "count" step:
intNewContestID = intNewContestID + 1

--

Ken Snell
<MS ACCESS MVP>

OK Ken here is the code behind the two modules. As you can see I an not
"re-DIMing the variables. I read your responce to Jeff but did not
understand what you meant by "qualified"

Please help.
-----------------------------------------------------------------------------
Public strNewContestName As String
Public intNewContestID As Long
Option Compare Database
-------------------------------------------------------------------------------
Private Sub Command143_Click()
On Error GoTo Err_Command143_Click

' Test to see if this event is offered in the other contest
intNewContestID = DLookup("[LinkTo ID]", "[Contests]", _
"[Contest ID] = Forms![Team Entry]![Contest Ident]")

intNewFeeID = Nz(DLookup("[Fee ID]", "[Team Fees]", _
"([Contest ID] = intNewContestID) and " _
& "([Category ID]= Forms![Team Entry]![Category ID])"), 0)
strNewContestName = DLookup("[Contest Name]", "[Contests]", _
"[Contest ID] = intNewContestID")

' If not send message and exit
If intNewFeeID = 0 Then
MsgBox ("You can not move this team deffnition to the " &
strNewContestName _
& " contest because " & [Combo42].[Column](2) & " is not offered in
that contest")
Exit Sub
End If
MsgBox (intNewContestID & ", " & intNewFeeID & ", " &
strNewContestName)
'Open the switch team form
DoCmd.OpenForm "Team Switch", , , "[ID]=" & Me![ID]

Exit_Command143_Click:
Exit Sub
------------------------------------------------------------------------------------
MSGBOX shows 12, 243, Open 2006

Form B is opened and click on the SAVE button runs this code.

MSGBOX shows , ,
------------------------------------------------------------------------------------------------

Private Sub Command6_Click()
On Error GoTo Command6_Click_Err

MsgBox (intNewContestID & ", " & intNewFeeID & ", " & strNewContestName)

' Zero to Fee ID
Forms![Team Switch]![Fee ID] = intNewFeeID

They can be shared, but they must be qualified by the form's module
reference (just as one would do with calling a public procedure inside a
form's module), and of course the form must be open at the time that
another
module wants to read the values. (The public variable essentially is a
read/write property of the form.)


Or write the value into a hidden textbox on the form and read the value
from
that textbox.

 

[Steve S]

Thanks. Looks like the simplest method is to just create a "regular" module
that just contains the definitions of the variables I want to share among
various forms. The other two options work as advertised but I need to
remember KISS.

Steve

When you reference the "public" variable that is another form's module, you
must qualify it by including the reference to the module in which it's
located. Assuming that the form that "Dim"'s the variable is named "FormA",
you would use this syntax in FormB to get the value of the variable:
Form_FormA.intNewContestID

Another way to do the same thing is to declare in FormB a form object
variable that will represent FormA:
Dim frmFormA As Form
Set frmFormA = Forms("FormA")
TheValue = FormA.intNewContestID
Set frmFormA = Nothing

However, as Jeff correctly noted, it's much more common to put a public
variable in a "regular" module, not a form module, so that you then do not
need the "form" qualifier to reference it, and you don't need to worry about
whether FormA is open or not. Of course, you then must reset the variable to
a default value when you're done with it so that it doesn't "carry over"
unexpected values the next time you use it in, say, a "count" step:
intNewContestID = intNewContestID + 1

--

Ken Snell
<MS ACCESS MVP>

OK Ken here is the code behind the two modules. As you can see I an not
"re-DIMing the variables. I read your responce to Jeff but did not
understand what you meant by "qualified"

Please help.
-----------------------------------------------------------------------------
Public strNewContestName As String
Public intNewContestID As Long
Option Compare Database
-------------------------------------------------------------------------------
Private Sub Command143_Click()
On Error GoTo Err_Command143_Click

' Test to see if this event is offered in the other contest
intNewContestID = DLookup("[LinkTo ID]", "[Contests]", _
"[Contest ID] = Forms![Team Entry]![Contest Ident]")

intNewFeeID = Nz(DLookup("[Fee ID]", "[Team Fees]", _
"([Contest ID] = intNewContestID) and " _
& "([Category ID]= Forms![Team Entry]![Category ID])"), 0)
strNewContestName = DLookup("[Contest Name]", "[Contests]", _
"[Contest ID] = intNewContestID")

' If not send message and exit
If intNewFeeID = 0 Then
MsgBox ("You can not move this team deffnition to the " &
strNewContestName _
& " contest because " & [Combo42].[Column](2) & " is not offered in
that contest")
Exit Sub
End If
MsgBox (intNewContestID & ", " & intNewFeeID & ", " &
strNewContestName)
'Open the switch team form
DoCmd.OpenForm "Team Switch", , , "[ID]=" & Me![ID]

Exit_Command143_Click:
Exit Sub
------------------------------------------------------------------------------------
MSGBOX shows 12, 243, Open 2006

Form B is opened and click on the SAVE button runs this code.

MSGBOX shows , ,
------------------------------------------------------------------------------------------------

Private Sub Command6_Click()
On Error GoTo Command6_Click_Err

MsgBox (intNewContestID & ", " & intNewFeeID & ", " & strNewContestName)

' Zero to Fee ID
Forms![Team Switch]![Fee ID] = intNewFeeID

Steve

I may be remembering incorrectly, but I thought that only those
variable
defined in code module (and not in forms & reports) were treated as
"share-able" within the .mdb file.

They can be shared, but they must be qualified by the form's module
reference (just as one would do with calling a public procedure inside a
form's module), and of course the form must be open at the time that
another
module wants to read the values. (The public variable essentially is a
read/write property of the form.)


But rather than passing variable values from form to form, why not have
form2 "look at" form1 and get its values? Or even, if you MUST pass,
use
the OpenArgs property and pass in values via OpenArgs.

Or write the value into a hidden textbox on the form and read the value
from
that textbox.

 

[Steve S]

Maybe I got ahead of myself but I removed the 3 variable definition
statements from the declaratives section of FormA and placed them in the
public module shown below.
---------------------------------------------------------------------------
Public Sub MyVariables()
'These variables are used to share or pass values between Forms or Reports

Public intNewFeeID As Long
Public strNewContestName As String
Public intNewContestID As Long
End Sub
----------------------------------------------------------------------------

I made no other changes to the code that I sent you earlier. Now when I
execute the code in FormA I get the following error on the first occurrence
of one of the variable names.

“The expression you entered as a query parameter produced this error: The
object doesn’t contain the Automation object intNewContestIDâ€

Do I have to qualify these variables? If so, How?

Steve

When you reference the "public" variable that is another form's module, you
must qualify it by including the reference to the module in which it's
located. Assuming that the form that "Dim"'s the variable is named "FormA",
you would use this syntax in FormB to get the value of the variable:
Form_FormA.intNewContestID

Another way to do the same thing is to declare in FormB a form object
variable that will represent FormA:
Dim frmFormA As Form
Set frmFormA = Forms("FormA")
TheValue = FormA.intNewContestID
Set frmFormA = Nothing

However, as Jeff correctly noted, it's much more common to put a public
variable in a "regular" module, not a form module, so that you then do not
need the "form" qualifier to reference it, and you don't need to worry about
whether FormA is open or not. Of course, you then must reset the variable to
a default value when you're done with it so that it doesn't "carry over"
unexpected values the next time you use it in, say, a "count" step:
intNewContestID = intNewContestID + 1

--

Ken Snell
<MS ACCESS MVP>

OK Ken here is the code behind the two modules. As you can see I an not
"re-DIMing the variables. I read your responce to Jeff but did not
understand what you meant by "qualified"

Please help.
-----------------------------------------------------------------------------
Public strNewContestName As String
Public intNewContestID As Long
Option Comp are Database
-------------------------------------------------------------------------------
Private Sub Command143_Click()
On Error GoTo Err_Command143_Click

' Test to see if this event is offered in the other contest
intNewContestID = DLookup("[LinkTo ID]", "[Contests]", _
"[Contest ID] = Forms![Team Entry]![Contest Ident]")

intNewFeeID = Nz(DLookup("[Fee ID]", "[Team Fees]", _
"([Contest ID] = intNewContestID) and " _
& "([Category ID]= Forms![Team Entry]![Category ID])"), 0)
strNewContestName = DLookup("[Contest Name]", "[Contests]", _
"[Contest ID] = intNewContestID")

' If not send message and exit
If intNewFeeID = 0 Then
MsgBox ("You can not move this team deffnition to the " &
strNewContestName _
& " contest because " & [Combo42].[Column](2) & " is not offered in
that contest")
Exit Sub
End If
MsgBox (intNewContestID & ", " & intNewFeeID & ", " &
strNewContestName)
'Open the switch team form
DoCmd.OpenForm "Team Switch", , , "[ID]=" & Me![ID]

Exit_Command143_Click:
Exit Sub
------------------------------------------------------------------------------------
MSGBOX shows 12, 243, Open 2006

Form B is opened and click on the SAVE button runs this code.

MSGBOX shows , ,
------------------------------------------------------------------------------------------------

Private Sub Command6_Click()
On Error GoTo Command6_Click_Err

MsgBox (intNewContestID & ", " & intNewFeeID & ", " & strNewContestName)

' Zero to Fee ID
Forms![Team Switch]![Fee ID] = intNewFeeID

Steve

I may be remembering incorrectly, but I thought that only those
variable
defined in code module (and not in forms & reports) were treated as
"share-able" within the .mdb file.

They can be shared, but they must be qualified by the form's module
reference (just as one would do with calling a public procedure inside a
form's module), and of course the form must be open at the time that
another
module wants to read the values. (The public variable essentially is a
read/write property of the form.)


But rather than passing variable values from form to form, why not have
form2 "look at" form1 and get its values? Or even, if you MUST pass,
use
the OpenArgs property and pass in values via OpenArgs.

Or write the value into a hidden textbox on the form and read the value
from
that textbox.

 

[Ken Snell \(MVP\)]

Do not put the Dim statements inside a sub or function. They go at the top
of the module before any procedures. This is the "Declarations" section of a
module.


Option Compare Database
Option Explicit

Public intNewFeeID As Long
Public strNewContestName As String
Public intNewContestID As Long


Public Sub Procedure1()
MsgBox "This is a test."
End Sub

--

Ken Snell
<MS ACCESS MVP>

Maybe I got ahead of myself but I removed the 3 variable definition
statements from the declaratives section of FormA and placed them in the
public module shown below.
---------------------------------------------------------------------------
Public Sub MyVariables()
'These variables are used to share or pass values between Forms or Reports

Public intNewFeeID As Long
Public strNewContestName As String
Public intNewContestID As Long
End Sub
----------------------------------------------------------------------------

I made no other changes to the code that I sent you earlier. Now when I
execute the code in FormA I get the following error on the first
occurrence
of one of the variable names.

"The expression you entered as a query parameter produced this error: The
object doesn't contain the Automation object intNewContestID"

Do I have to qualify these variables? If so, How?

Steve

When you reference the "public" variable that is another form's module,
you
must qualify it by including the reference to the module in which it's
located. Assuming that the form that "Dim"'s the variable is named
"FormA",
you would use this syntax in FormB to get the value of the variable:
Form_FormA.intNewContestID

Another way to do the same thing is to declare in FormB a form object
variable that will represent FormA:
Dim frmFormA As Form
Set frmFormA = Forms("FormA")
TheValue = FormA.intNewContestID
Set frmFormA = Nothing

However, as Jeff correctly noted, it's much more common to put a public
variable in a "regular" module, not a form module, so that you then do
not
need the "form" qualifier to reference it, and you don't need to worry
about
whether FormA is open or not. Of course, you then must reset the variable
to
a default value when you're done with it so that it doesn't "carry over"
unexpected values the next time you use it in, say, a "count" step:
intNewContestID = intNewContestID + 1

--

Ken Snell
<MS ACCESS MVP>

OK Ken here is the code behind the two modules. As you can see I an
not
"re-DIMing the variables. I read your responce to Jeff but did not
understand what you meant by "qualified"

Please help.
-----------------------------------------------------------------------------
Public strNewContestName As String
Public intNewContestID As Long
Option Comp are Database
-------------------------------------------------------------------------------
Private Sub Command143_Click()
On Error GoTo Err_Command143_Click

' Test to see if this event is offered in the other contest
intNewContestID = DLookup("[LinkTo ID]", "[Contests]", _
"[Contest ID] = Forms![Team Entry]![Contest Ident]")

intNewFeeID = Nz(DLookup("[Fee ID]", "[Team Fees]", _
"([Contest ID] = intNewContestID) and " _
& "([Category ID]= Forms![Team Entry]![Category ID])"), 0)
strNewContestName = DLookup("[Contest Name]", "[Contests]", _
"[Contest ID] = intNewContestID")

' If not send message and exit
If intNewFeeID = 0 Then
MsgBox ("You can not move this team deffnition to the " &
strNewContestName _
& " contest because " & [Combo42].[Column](2) & " is not offered
in
that contest")
Exit Sub
End If
MsgBox (intNewContestID & ", " & intNewFeeID & ", " &
strNewContestName)
'Open the switch team form
DoCmd.OpenForm "Team Switch", , , "[ID]=" & Me![ID]

Exit_Command143_Click:
Exit Sub
------------------------------------------------------------------------------------
MSGBOX shows 12, 243, Open 2006

Form B is opened and click on the SAVE button runs this code.

MSGBOX shows , ,
------------------------------------------------------------------------------------------------

Private Sub Command6_Click()
On Error GoTo Command6_Click_Err

MsgBox (intNewContestID & ", " & intNewFeeID & ", " &
strNewContestName)

' Zero to Fee ID
Forms![Team Switch]![Fee ID] = intNewFeeID


:

Steve

I may be remembering incorrectly, but I thought that only those
variable
defined in code module (and not in forms & reports) were treated as
"share-able" within the .mdb file.

They can be shared, but they must be qualified by the form's module
reference (just as one would do with calling a public procedure inside
a
form's module), and of course the form must be open at the time that
another
module wants to read the values. (The public variable essentially is a
read/write property of the form.)


But rather than passing variable values from form to form, why not
have
form2 "look at" form1 and get its values? Or even, if you MUST
pass,
use
the OpenArgs property and pass in values via OpenArgs.

Or write the value into a hidden textbox on the form and read the
value
from
that textbox.

 

[Steve S]

Still get the same error. Here is the code in the module. The Function
MyNoData is just one of several procedures and functions in this module:
--------------------------------------------------------------------
Option Compare Database
Option Explicit

Public intNewFeeID As Long
Public strNewContestName As String
Public intNewContestID As Long

Public Function MyNoData(strReport As String)

On Error GoTo MyNoData_Err

MsgBox "There are no " & strReport & " to be printed", vbOKOnly, "No Data"
DoCmd.CancelEvent

MyNoData_Exit:
Exit Function

MyNoData_Err:
MsgBox Error$
Resume MyNoData_Exit

End Function
------------------------------------------------------------------

Here is the code where I use the variables and where the error is generated:

---------------------------------------------------
Private Sub Command143_Click()
On Error GoTo Err_Command143_Click

' Test to see if this event is offered in the other contest
intNewContestID = DLookup("[LinkTo ID]", "[Contests]", _
"[Contest ID] = Forms![Team Entry]![Contest Ident]")

intNewFeeID = Nz(DLookup("[Fee ID]", "[Team Fees]", _
"([Contest ID] = intNewContestID) and " _
& "([Category ID]= Forms![Team Entry]![Category ID])"), 0)

strNewContestName = DLookup("[Contest Name]", "[Contests]", _
"[Contest ID] = intNewContestID")

' If not send message and exit
If intNewFeeID = 0 Then
MsgBox ("You can not move this team deffnition to the " &
strNewContestName _
& " contest because " & [Combo42].[Column](2) & " is not offered in
that contest")
Exit Sub
End If
MsgBox (intNewContestID & ", " & intNewFeeID & ", " & strNewContestName)
'Open the switch team form
DoCmd.OpenForm "Team Switch", , , "[ID]=" & Me![ID]

Exit_Command143_Click:
Exit Sub

Err_Command143_Click:
MsgBox Err.Description
Resume Exit_Command143_Click

End Sub
-----------------------------------------------

Do not put the Dim statements inside a sub or function. They go at the top
of the module before any procedures. This is the "Declarations" section of a
module.


Option Compare Database
Option Explicit

Public intNewFeeID As Long
Public strNewContestName As String
Public intNewContestID As Long


Public Sub Procedure1()
MsgBox "This is a test."
End Sub

--

Ken Snell
<MS ACCESS MVP>

Maybe I got ahead of myself but I removed the 3 variable definition
statements from the declaratives section of FormA and placed them in the
public module shown below.
---------------------------------------------------------------------------
Public Sub MyVariables()
'These variables are used to share or pass values between Forms or Reports

Public intNewFeeID As Long
Public strNewContestName As String
Public intNewContestID As Long
End Sub
----------------------------------------------------------------------------

I made no other changes to the code that I sent you earlier. Now when I
execute the code in FormA I get the following error on the first
occurrence
of one of the variable names.

"The expression you entered as a query parameter produced this error: The
object doesn't contain the Automation object intNewContestID"

Do I have to qualify these variables? If so, How?

Steve

When you reference the "public" variable that is another form's module,
you
must qualify it by including the reference to the module in which it's
located. Assuming that the form that "Dim"'s the variable is named
"FormA",
you would use this syntax in FormB to get the value of the variable:
Form_FormA.intNewContestID

Another way to do the same thing is to declare in FormB a form object
variable that will represent FormA:
Dim frmFormA As Form
Set frmFormA = Forms("FormA")
TheValue = FormA.intNewContestID
Set frmFormA = Nothing

However, as Jeff correctly noted, it's much more common to put a public
variable in a "regular" module, not a form module, so that you then do
not
need the "form" qualifier to reference it, and you don't need to worry
about
whether FormA is open or not. Of course, you then must reset the variable
to
a default value when you're done with it so that it doesn't "carry over"
unexpected values the next time you use it in, say, a "count" step:
intNewContestID = intNewContestID + 1

--

Ken Snell
<MS ACCESS MVP>


OK Ken here is the code behind the two modules. As you can see I an
not
"re-DIMing the variables. I read your responce to Jeff but did not
understand what you meant by "qualified"

Please help.
-----------------------------------------------------------------------------
Public strNewContestName As String
Public intNewContestID As Long
Option Comp are Database
-------------------------------------------------------------------------------
Private Sub Command143_Click()
On Error GoTo Err_Command143_Click

' Test to see if this event is offered in the other contest
intNewContestID = DLookup("[LinkTo ID]", "[Contests]", _
"[Contest ID] = Forms![Team Entry]![Contest Ident]")

intNewFeeID = Nz(DLookup("[Fee ID]", "[Team Fees]", _
"([Contest ID] = intNewContestID) and " _
& "([Category ID]= Forms![Team Entry]![Category ID])"), 0)
strNewContestName = DLookup("[Contest Name]", "[Contests]", _
"[Contest ID] = intNewContestID")

' If not send message and exit
If intNewFeeID = 0 Then
MsgBox ("You can not move this team deffnition to the " &
strNewContestName _
& " contest because " & [Combo42].[Column](2) & " is not offered
in
that contest")
Exit Sub
End If
MsgBox (intNewContestID & ", " & intNewFeeID & ", " &
strNewContestName)
'Open the switch team form
DoCmd.OpenForm "Team Switch", , , "[ID]=" & Me![ID]

Exit_Command143_Click:
Exit Sub
------------------------------------------------------------------------------------
MSGBOX shows 12, 243, Open 2006

Form B is opened and click on the SAVE button runs this code.

MSGBOX shows , ,
------------------------------------------------------------------------------------------------

Private Sub Command6_Click()
On Error GoTo Command6_Click_Err

MsgBox (intNewContestID & ", " & intNewFeeID & ", " &
strNewContestName)

' Zero to Fee ID
Forms![Team Switch]![Fee ID] = intNewFeeID


:

Steve

I may be remembering incorrectly, but I thought that only those
variable
defined in code module (and not in forms & reports) were treated as
"share-able" within the .mdb file.

They can be shared, but they must be qualified by the form's module
reference (just as one would do with calling a public procedure inside
a
form's module), and of course the form must be open at the time that
another
module wants to read the values. (The public variable essentially is a
read/write property of the form.)


But rather than passing variable values from form to form, why not
have
form2 "look at" form1 and get its values? Or even, if you MUST
pass,
use
the OpenArgs property and pass in values via OpenArgs.

Or write the value into a hidden textbox on the form and read the
value
from
that textbox.

 

[Ken Snell \(MVP\)]

Did you put these public declarations in a 'regular' module (one that can be
seen in the list of modules in the Modules window of the database window)?
Is the name of the 'regular' module different from any of the procedures and
variables that are in the module?

Did you remove the Dim statements from the form modules for these variables?

Try compiling the code (in the VBE window, use Debug | Compile) and see if
you get any errors.

--

Ken Snell
<MS ACCESS MVP>

Still get the same error. Here is the code in the module. The Function
MyNoData is just one of several procedures and functions in this module:
--------------------------------------------------------------------
Option Compare Database
Option Explicit

Public intNewFeeID As Long
Public strNewContestName As String
Public intNewContestID As Long

Public Function MyNoData(strReport As String)

On Error GoTo MyNoData_Err

MsgBox "There are no " & strReport & " to be printed", vbOKOnly, "No
Data"
DoCmd.CancelEvent

MyNoData_Exit:
Exit Function

MyNoData_Err:
MsgBox Error$
Resume MyNoData_Exit

End Function
------------------------------------------------------------------

Here is the code where I use the variables and where the error is
generated:

---------------------------------------------------
Private Sub Command143_Click()
On Error GoTo Err_Command143_Click

' Test to see if this event is offered in the other contest
intNewContestID = DLookup("[LinkTo ID]", "[Contests]", _
"[Contest ID] = Forms![Team Entry]![Contest Ident]")

intNewFeeID = Nz(DLookup("[Fee ID]", "[Team Fees]", _
"([Contest ID] = intNewContestID) and " _
& "([Category ID]= Forms![Team Entry]![Category ID])"), 0)

strNewContestName = DLookup("[Contest Name]", "[Contests]", _
"[Contest ID] = intNewContestID")

' If not send message and exit
If intNewFeeID = 0 Then
MsgBox ("You can not move this team deffnition to the " &
strNewContestName _
& " contest because " & [Combo42].[Column](2) & " is not offered in
that contest")
Exit Sub
End If
MsgBox (intNewContestID & ", " & intNewFeeID & ", " &
strNewContestName)
'Open the switch team form
DoCmd.OpenForm "Team Switch", , , "[ID]=" & Me![ID]

Exit_Command143_Click:
Exit Sub

Err_Command143_Click:
MsgBox Err.Description
Resume Exit_Command143_Click

End Sub
-----------------------------------------------

Do not put the Dim statements inside a sub or function. They go at the
top
of the module before any procedures. This is the "Declarations" section
of a
module.


Option Compare Database
Option Explicit

Public intNewFeeID As Long
Public strNewContestName As String
Public intNewContestID As Long


Public Sub Procedure1()
MsgBox "This is a test."
End Sub

--

Ken Snell
<MS ACCESS MVP>

Maybe I got ahead of myself but I removed the 3 variable definition
statements from the declaratives section of FormA and placed them in
the
public module shown below.
---------------------------------------------------------------------------
Public Sub MyVariables()
'These variables are used to share or pass values between Forms or
Reports

Public intNewFeeID As Long
Public strNewContestName As String
Public intNewContestID As Long
End Sub
----------------------------------------------------------------------------

I made no other changes to the code that I sent you earlier. Now when
I
execute the code in FormA I get the following error on the first
occurrence
of one of the variable names.

"The expression you entered as a query parameter produced this error:
The
object doesn't contain the Automation object intNewContestID"

Do I have to qualify these variables? If so, How?

Steve


:

When you reference the "public" variable that is another form's
module,
you
must qualify it by including the reference to the module in which it's
located. Assuming that the form that "Dim"'s the variable is named
"FormA",
you would use this syntax in FormB to get the value of the variable:
Form_FormA.intNewContestID

Another way to do the same thing is to declare in FormB a form object
variable that will represent FormA:
Dim frmFormA As Form
Set frmFormA = Forms("FormA")
TheValue = FormA.intNewContestID
Set frmFormA = Nothing

However, as Jeff correctly noted, it's much more common to put a
public
variable in a "regular" module, not a form module, so that you then do
not
need the "form" qualifier to reference it, and you don't need to worry
about
whether FormA is open or not. Of course, you then must reset the
variable
to
a default value when you're done with it so that it doesn't "carry
over"
unexpected values the next time you use it in, say, a "count" step:
intNewContestID = intNewContestID + 1

--

Ken Snell
<MS ACCESS MVP>


OK Ken here is the code behind the two modules. As you can see I an
not
"re-DIMing the variables. I read your responce to Jeff but did not
understand what you meant by "qualified"

Please help.
-----------------------------------------------------------------------------
Public strNewContestName As String
Public intNewContestID As Long
Option Comp
are Database
-------------------------------------------------------------------------------
Private Sub Command143_Click()
On Error GoTo Err_Command143_Click

' Test to see if this event is offered in the other contest
intNewContestID = DLookup("[LinkTo ID]", "[Contests]", _
"[Contest ID] = Forms![Team Entry]![Contest Ident]")

intNewFeeID = Nz(DLookup("[Fee ID]", "[Team Fees]", _
"([Contest ID] = intNewContestID) and " _
& "([Category ID]= Forms![Team Entry]![Category ID])"),
0)
strNewContestName = DLookup("[Contest Name]", "[Contests]", _
"[Contest ID] = intNewContestID")

' If not send message and exit
If intNewFeeID = 0 Then
MsgBox ("You can not move this team deffnition to the " &
strNewContestName _
& " contest because " & [Combo42].[Column](2) & " is not
offered
in
that contest")
Exit Sub
End If
MsgBox (intNewContestID & ", " & intNewFeeID & ", " &
strNewContestName)
'Open the switch team form
DoCmd.OpenForm "Team Switch", , , "[ID]=" & Me![ID]

Exit_Command143_Click:
Exit Sub
------------------------------------------------------------------------------------
MSGBOX shows 12, 243, Open 2006

Form B is opened and click on the SAVE button runs this code.

MSGBOX shows , ,
------------------------------------------------------------------------------------------------

Private Sub Command6_Click()
On Error GoTo Command6_Click_Err

MsgBox (intNewContestID & ", " & intNewFeeID & ", " &
strNewContestName)

' Zero to Fee ID
Forms![Team Switch]![Fee ID] = intNewFeeID


:

Steve

I may be remembering incorrectly, but I thought that only those
variable
defined in code module (and not in forms & reports) were treated
as
"share-able" within the .mdb file.

They can be shared, but they must be qualified by the form's module
reference (just as one would do with calling a public procedure
inside
a
form's module), and of course the form must be open at the time
that
another
module wants to read the values. (The public variable essentially
is a
read/write property of the form.)


But rather than passing variable values from form to form, why
not
have
form2 "look at" form1 and get its values? Or even, if you MUST
pass,
use
the OpenArgs property and pass in values via OpenArgs.

Or write the value into a hidden textbox on the form and read the
value
from
that textbox.

 

[Steve S]

Yes, the dim statements are in a regular module that shows in the database
window and I used the "find" on the current project to verify they are not
Dim ed anywhere else. Compiling shows no errors. I give up - sounded like a
good idea but I need to move ahead.

I am going to go back to the tried and true method of defining text fields
in FormA to save the values I need and have FormB refer to these fields. I
have to get this project done ASAP so I will trouble shoot this issue with a
new clean DB in a few days. Appreciate your help. Maybe you will be 'on
duty' when I repost this issue later in the week.

Steve

Did you put these public declarations in a 'regular' module (one that can be
seen in the list of modules in the Modules window of the database window)?
Is the name of the 'regular' module different from any of the procedures and
variables that are in the module?

Did you remove the Dim statements from the form modules for these variables?

Try compiling the code (in the VBE window, use Debug | Compile) and see if
you get any errors.

--

Ken Snell
<MS ACCESS MVP>

Still get the same error. Here is the code in the module. The Function
MyNoData is just one of several procedures and functions in this module:
--------------------------------------------------------------------
Option Compare Database
Option Explicit

Public intNewFeeID As Long
Public strNewContestName As String
Public intNewContestID As Long

Public Function MyNoData(strReport As String)

On Error GoTo MyNoData_Err

MsgBox "There are no " & strReport & " to be printed", vbOKOnly, "No
Data"
DoCmd.CancelEvent

MyNoData_Exit:
Exit Function

MyNoData_Err:
MsgBox Error$
Resume MyNoData_Exit

End Function
------------------------------------------------------------------

Here is the code where I use the variables and where the error is
generated:

---------------------------------------------------
Private Sub Command143_Click()
On Error GoTo Err_Command143_Click

' Test to see if this event is offered in the other contest
intNewContestID = DLookup("[LinkTo ID]", "[Contests]", _
"[Contest ID] = Forms![Team Entry]![Contest Ident]")

intNewFeeID = Nz(DLookup("[Fee ID]", "[Team Fees]", _
"([Contest ID] = intNewContestID) and " _
& "([Category ID]= Forms![Team Entry]![Category ID])"), 0)

strNewContestName = DLookup("[Contest Name]", "[Contests]", _
"[Contest ID] = intNewContestID")

' If not send message and exit
If intNewFeeID = 0 Then
MsgBox ("You can not move this team deffnition to the " &
strNewContestName _
& " contest because " & [Combo42].[Column](2) & " is not offered in
that contest")
Exit Sub
End If
MsgBox (intNewContestID & ", " & intNewFeeID & ", " &
strNewContestName)
'Open the switch team form
DoCmd.OpenForm "Team Switch", , , "[ID]=" & Me![ID]

Exit_Command143_Click:
Exit Sub

Err_Command143_Click:
MsgBox Err.Description
Resume Exit_Command143_Click

End Sub
-----------------------------------------------

Do not put the Dim statements inside a sub or function. They go at the
top
of the module before any procedures. This is the "Declarations" section
of a
module.


Option Compare Database
Option Explicit

Public intNewFeeID As Long
Public strNewContestName As String
Public intNewContestID As Long


Public Sub Procedure1()
MsgBox "This is a test."
End Sub

--

Ken Snell
<MS ACCESS MVP>



Maybe I got ahead of myself but I removed the 3 variable definition
statements from the declaratives section of FormA and placed them in
the
public module shown below.
---------------------------------------------------------------------------
Public Sub MyVariables()
'These variables are used to share or pass values between Forms or
Reports

Public intNewFeeID As Long
Public strNewContestName As String
Public intNewContestID As Long
End Sub
----------------------------------------------------------------------------

I made no other changes to the code that I sent you earlier. Now when
I
execute the code in FormA I get the following error on the first
occurrence
of one of the variable names.

"The expression you entered as a query parameter produced this error:
The
object doesn't contain the Automation object intNewContestID"

Do I have to qualify these variables? If so, How?

Steve


:

When you reference the "public" variable that is another form's
module,
you
must qualify it by including the reference to the module in which it's
located. Assuming that the form that "Dim"'s the variable is named
"FormA",
you would use this syntax in FormB to get the value of the variable:
Form_FormA.intNewContestID

Another way to do the same thing is to declare in FormB a form object
variable that will represent FormA:
Dim frmFormA As Form
Set frmFormA = Forms("FormA")
TheValue = FormA.intNewContestID
Set frmFormA = Nothing

However, as Jeff correctly noted, it's much more common to put a
public
variable in a "regular" module, not a form module, so that you then do
not
need the "form" qualifier to reference it, and you don't need to worry
about
whether FormA is open or not. Of course, you then must reset the
variable
to
a default value when you're done with it so that it doesn't "carry
over"
unexpected values the next time you use it in, say, a "count" step:
intNewContestID = intNewContestID + 1

--

Ken Snell
<MS ACCESS MVP>


OK Ken here is the code behind the two modules. As you can see I an
not
"re-DIMing the variables. I read your responce to Jeff but did not
understand what you meant by "qualified"

Please help.
-----------------------------------------------------------------------------
Public strNewContestName As String
Public intNewContestID As Long
Option Comp
are Database
-------------------------------------------------------------------------------
Private Sub Command143_Click()
On Error GoTo Err_Command143_Click

' Test to see if this event is offered in the other contest
intNewContestID = DLookup("[LinkTo ID]", "[Contests]", _
"[Contest ID] = Forms![Team Entry]![Contest Ident]")

intNewFeeID = Nz(DLookup("[Fee ID]", "[Team Fees]", _
"([Contest ID] = intNewContestID) and " _
& "([Category ID]= Forms![Team Entry]![Category ID])"),
0)
strNewContestName = DLookup("[Contest Name]", "[Contests]", _
"[Contest ID] = intNewContestID")

' If not send message and exit
If intNewFeeID = 0 Then
MsgBox ("You can not move this team deffnition to the " &
strNewContestName _
& " contest because " & [Combo42].[Column](2) & " is not
offered
in
that contest")
Exit Sub
End If
MsgBox (intNewContestID & ", " & intNewFeeID & ", " &
strNewContestName)
'Open the switch team form
DoCmd.OpenForm "Team Switch", , , "[ID]=" & Me![ID]

Exit_Command143_Click:
Exit Sub
------------------------------------------------------------------------------------
MSGBOX shows 12, 243, Open 2006

Form B is opened and click on the SAVE button runs this code.

MSGBOX shows , ,
------------------------------------------------------------------------------------------------

Private Sub Command6_Click()
On Error GoTo Command6_Click_Err

MsgBox (intNewContestID & ", " & intNewFeeID & ", " &
strNewContestName)

' Zero to Fee ID
Forms![Team Switch]![Fee ID] = intNewFeeID


:

Steve

I may be remembering incorrectly, but I thought that only those
variable
defined in code module (and not in forms & reports) were treated
as
"share-able" within the .mdb file.

They can be shared, but they must be qualified by the form's module
reference (just as one would do with calling a public procedure
inside
a
form's module), and of course the form must be open at the time
that
another
module wants to read the values. (The public variable essentially
is a
read/write property of the form.)


But rather than passing variable values from form to form, why
not
have
form2 "look at" form1 and get its values? Or even, if you MUST
pass,
use
the OpenArgs property and pass in values via OpenArgs.

Or write the value into a hidden textbox on the form and read the
value
from
that textbox.

 

[Ken Snell \(MVP\)]

Oh, I see the problem. Sorry that I missed it before.

You must concatenate the value of the variable into the expressions. You're
using the name of the variable instead.

Change this code step:

intNewFeeID = Nz(DLookup("[Fee ID]", "[Team Fees]", _
"([Contest ID] = intNewContestID) and " _
& "([Category ID]= Forms![Team Entry]![Category ID])"), 0)


to this:

intNewFeeID = Nz(DLookup("[Fee ID]", "[Team Fees]", _
"([Contest ID] = " & intNewContestID) & " and " _
& "([Category ID]= Forms![Team Entry]![Category ID])"), 0)


Make similar changes to the other code steps where you use the global
variables.

--

Ken Snell
<MS ACCESS MVP>

Yes, the dim statements are in a regular module that shows in the database
window and I used the "find" on the current project to verify they are not
Dim ed anywhere else. Compiling shows no errors. I give up - sounded
like a
good idea but I need to move ahead.

I am going to go back to the tried and true method of defining text fields
in FormA to save the values I need and have FormB refer to these fields.
I
have to get this project done ASAP so I will trouble shoot this issue with
a
new clean DB in a few days. Appreciate your help. Maybe you will be 'on
duty' when I repost this issue later in the week.

Steve

Did you put these public declarations in a 'regular' module (one that can
be
seen in the list of modules in the Modules window of the database
window)?
Is the name of the 'regular' module different from any of the procedures
and
variables that are in the module?

Did you remove the Dim statements from the form modules for these
variables?

Try compiling the code (in the VBE window, use Debug | Compile) and see
if
you get any errors.

--

Ken Snell
<MS ACCESS MVP>

Still get the same error. Here is the code in the module. The
Function
MyNoData is just one of several procedures and functions in this
module:
--------------------------------------------------------------------
Option Compare Database
Option Explicit

Public intNewFeeID As Long
Public strNewContestName As String
Public intNewContestID As Long

Public Function MyNoData(strReport As String)

On Error GoTo MyNoData_Err

MsgBox "There are no " & strReport & " to be printed", vbOKOnly, "No
Data"
DoCmd.CancelEvent

MyNoData_Exit:
Exit Function

MyNoData_Err:
MsgBox Error$
Resume MyNoData_Exit

End Function
------------------------------------------------------------------

Here is the code where I use the variables and where the error is
generated:

---------------------------------------------------
Private Sub Command143_Click()
On Error GoTo Err_Command143_Click

' Test to see if this event is offered in the other contest
intNewContestID = DLookup("[LinkTo ID]", "[Contests]", _
"[Contest ID] = Forms![Team Entry]![Contest Ident]")

intNewFeeID = Nz(DLookup("[Fee ID]", "[Team Fees]", _
"([Contest ID] = intNewContestID) and " _
& "([Category ID]= Forms![Team Entry]![Category ID])"), 0)

strNewContestName = DLookup("[Contest Name]", "[Contests]", _
"[Contest ID] = intNewContestID")

' If not send message and exit
If intNewFeeID = 0 Then
MsgBox ("You can not move this team deffnition to the " &
strNewContestName _
& " contest because " & [Combo42].[Column](2) & " is not offered
in
that contest")
Exit Sub
End If
MsgBox (intNewContestID & ", " & intNewFeeID & ", " &
strNewContestName)
'Open the switch team form
DoCmd.OpenForm "Team Switch", , , "[ID]=" & Me![ID]

Exit_Command143_Click:
Exit Sub

Err_Command143_Click:
MsgBox Err.Description
Resume Exit_Command143_Click

End Sub
-----------------------------------------------
:

Do not put the Dim statements inside a sub or function. They go at the
top
of the module before any procedures. This is the "Declarations"
section
of a
module.


Option Compare Database
Option Explicit

Public intNewFeeID As Long
Public strNewContestName As String
Public intNewContestID As Long


Public Sub Procedure1()
MsgBox "This is a test."
End Sub

--

Ken Snell
<MS ACCESS MVP>



Maybe I got ahead of myself but I removed the 3 variable definition
statements from the declaratives section of FormA and placed them
in
the
public module shown below.
---------------------------------------------------------------------------
Public Sub MyVariables()
'These variables are used to share or pass values between Forms or
Reports

Public intNewFeeID As Long
Public strNewContestName As String
Public intNewContestID As Long
End Sub
----------------------------------------------------------------------------

I made no other changes to the code that I sent you earlier. Now
when
I
execute the code in FormA I get the following error on the first
occurrence
of one of the variable names.

"The expression you entered as a query parameter produced this
error:
The
object doesn't contain the Automation object intNewContestID"

Do I have to qualify these variables? If so, How?

Steve


:

When you reference the "public" variable that is another form's
module,
you
must qualify it by including the reference to the module in which
it's
located. Assuming that the form that "Dim"'s the variable is named
"FormA",
you would use this syntax in FormB to get the value of the
variable:
Form_FormA.intNewContestID

Another way to do the same thing is to declare in FormB a form
object
variable that will represent FormA:
Dim frmFormA As Form
Set frmFormA = Forms("FormA")
TheValue = FormA.intNewContestID
Set frmFormA = Nothing

However, as Jeff correctly noted, it's much more common to put a
public
variable in a "regular" module, not a form module, so that you then
do
not
need the "form" qualifier to reference it, and you don't need to
worry
about
whether FormA is open or not. Of course, you then must reset the
variable
to
a default value when you're done with it so that it doesn't "carry
over"
unexpected values the next time you use it in, say, a "count" step:
intNewContestID = intNewContestID + 1

--

Ken Snell
<MS ACCESS MVP>


OK Ken here is the code behind the two modules. As you can see I
an
not
"re-DIMing the variables. I read your responce to Jeff but did
not
understand what you meant by "qualified"

Please help.
-----------------------------------------------------------------------------
Public strNewContestName As String
Public intNewContestID As Long
Option Comp
are Database
-------------------------------------------------------------------------------
Private Sub Command143_Click()
On Error GoTo Err_Command143_Click

' Test to see if this event is offered in the other contest
intNewContestID = DLookup("[LinkTo ID]", "[Contests]", _
"[Contest ID] = Forms![Team Entry]![Contest Ident]")

intNewFeeID = Nz(DLookup("[Fee ID]", "[Team Fees]", _
"([Contest ID] = intNewContestID) and " _
& "([Category ID]= Forms![Team Entry]![Category
ID])"),
0)
strNewContestName = DLookup("[Contest Name]", "[Contests]", _
"[Contest ID] = intNewContestID")

' If not send message and exit
If intNewFeeID = 0 Then
MsgBox ("You can not move this team deffnition to the " &
strNewContestName _
& " contest because " & [Combo42].[Column](2) & " is not
offered
in
that contest")
Exit Sub
End If
MsgBox (intNewContestID & ", " & intNewFeeID & ", " &
strNewContestName)
'Open the switch team form
DoCmd.OpenForm "Team Switch", , , "[ID]=" & Me![ID]

Exit_Command143_Click:
Exit Sub
------------------------------------------------------------------------------------
MSGBOX shows 12, 243, Open 2006

Form B is opened and click on the SAVE button runs this code.

MSGBOX shows , ,
------------------------------------------------------------------------------------------------

Private Sub Command6_Click()
On Error GoTo Command6_Click_Err

MsgBox (intNewContestID & ", " & intNewFeeID & ", " &
strNewContestName)

' Zero to Fee ID
Forms![Team Switch]![Fee ID] = intNewFeeID


:

Steve

I may be remembering incorrectly, but I thought that only
those
variable
defined in code module (and not in forms & reports) were
treated
as
"share-able" within the .mdb file.

They can be shared, but they must be qualified by the form's
module
reference (just as one would do with calling a public procedure
inside
a
form's module), and of course the form must be open at the time
that
another
module wants to read the values. (The public variable
essentially
is a
read/write property of the form.)


But rather than passing variable values from form to form, why
not
have
form2 "look at" form1 and get its values? Or even, if you
MUST
pass,
use
the OpenArgs property and pass in values via OpenArgs.

Or write the value into a hidden textbox on the form and read
the
value
from
that textbox.

 

[Ken Snell \(MVP\)]

Typo in my post. Here is corrected version:
-----------
Oh, I see the problem. Sorry that I missed it before.

You must concatenate the value of the variable into the expressions. You're
using the name of the variable instead.

Change this code step:

intNewFeeID = Nz(DLookup("[Fee ID]", "[Team Fees]", _
"([Contest ID] = intNewContestID) and " _
& "([Category ID]= Forms![Team Entry]![Category ID])"), 0)


to this:

intNewFeeID = Nz(DLookup("[Fee ID]", "[Team Fees]", _
"([Contest ID] = " & intNewContestID & ") and " _
& "([Category ID]= Forms![Team Entry]![Category ID])"), 0)


Make similar changes to the other code steps where you use the global
variables.

--

Ken Snell
<MS ACCESS MVP>

Oh, I see the problem. Sorry that I missed it before.

You must concatenate the value of the variable into the expressions.
You're using the name of the variable instead.

Change this code step:

intNewFeeID = Nz(DLookup("[Fee ID]", "[Team Fees]", _
"([Contest ID] = intNewContestID) and " _
& "([Category ID]= Forms![Team Entry]![Category ID])"), 0)


to this:

intNewFeeID = Nz(DLookup("[Fee ID]", "[Team Fees]", _
"([Contest ID] = " & intNewContestID) & " and " _
& "([Category ID]= Forms![Team Entry]![Category ID])"), 0)


Make similar changes to the other code steps where you use the global
variables.

--

Ken Snell
<MS ACCESS MVP>

Yes, the dim statements are in a regular module that shows in the
database
window and I used the "find" on the current project to verify they are
not
Dim ed anywhere else. Compiling shows no errors. I give up - sounded
like a
good idea but I need to move ahead.

I am going to go back to the tried and true method of defining text
fields
in FormA to save the values I need and have FormB refer to these fields.
I
have to get this project done ASAP so I will trouble shoot this issue
with a
new clean DB in a few days. Appreciate your help. Maybe you will be 'on
duty' when I repost this issue later in the week.

Steve

Did you put these public declarations in a 'regular' module (one that
can be
seen in the list of modules in the Modules window of the database
window)?
Is the name of the 'regular' module different from any of the procedures
and
variables that are in the module?

Did you remove the Dim statements from the form modules for these
variables?

Try compiling the code (in the VBE window, use Debug | Compile) and see
if
you get any errors.

--

Ken Snell
<MS ACCESS MVP>


Still get the same error. Here is the code in the module. The
Function
MyNoData is just one of several procedures and functions in this
module:
--------------------------------------------------------------------
Option Compare Database
Option Explicit

Public intNewFeeID As Long
Public strNewContestName As String
Public intNewContestID As Long

Public Function MyNoData(strReport As String)

On Error GoTo MyNoData_Err

MsgBox "There are no " & strReport & " to be printed", vbOKOnly,
"No
Data"
DoCmd.CancelEvent

MyNoData_Exit:
Exit Function

MyNoData_Err:
MsgBox Error$
Resume MyNoData_Exit

End Function
------------------------------------------------------------------

Here is the code where I use the variables and where the error is
generated:

---------------------------------------------------
Private Sub Command143_Click()
On Error GoTo Err_Command143_Click

' Test to see if this event is offered in the other contest
intNewContestID = DLookup("[LinkTo ID]", "[Contests]", _
"[Contest ID] = Forms![Team Entry]![Contest Ident]")

intNewFeeID = Nz(DLookup("[Fee ID]", "[Team Fees]", _
"([Contest ID] = intNewContestID) and " _
& "([Category ID]= Forms![Team Entry]![Category ID])"), 0)

strNewContestName = DLookup("[Contest Name]", "[Contests]", _
"[Contest ID] = intNewContestID")

' If not send message and exit
If intNewFeeID = 0 Then
MsgBox ("You can not move this team deffnition to the " &
strNewContestName _
& " contest because " & [Combo42].[Column](2) & " is not
offered in
that contest")
Exit Sub
End If
MsgBox (intNewContestID & ", " & intNewFeeID & ", " &
strNewContestName)
'Open the switch team form
DoCmd.OpenForm "Team Switch", , , "[ID]=" & Me![ID]

Exit_Command143_Click:
Exit Sub

Err_Command143_Click:
MsgBox Err.Description
Resume Exit_Command143_Click

End Sub
-----------------------------------------------
:

Do not put the Dim statements inside a sub or function. They go at
the
top
of the module before any procedures. This is the "Declarations"
section
of a
module.


Option Compare Database
Option Explicit

Public intNewFeeID As Long
Public strNewContestName As String
Public intNewContestID As Long


Public Sub Procedure1()
MsgBox "This is a test."
End Sub

--

Ken Snell
<MS ACCESS MVP>



Maybe I got ahead of myself but I removed the 3 variable definition
statements from the declaratives section of FormA and placed them
in
the
public module shown below.
---------------------------------------------------------------------------
Public Sub MyVariables()
'These variables are used to share or pass values between Forms or
Reports

Public intNewFeeID As Long
Public strNewContestName As String
Public intNewContestID As Long
End Sub
----------------------------------------------------------------------------

I made no other changes to the code that I sent you earlier. Now
when
I
execute the code in FormA I get the following error on the first
occurrence
of one of the variable names.

"The expression you entered as a query parameter produced this
error:
The
object doesn't contain the Automation object intNewContestID"

Do I have to qualify these variables? If so, How?

Steve


:

When you reference the "public" variable that is another form's
module,
you
must qualify it by including the reference to the module in which
it's
located. Assuming that the form that "Dim"'s the variable is named
"FormA",
you would use this syntax in FormB to get the value of the
variable:
Form_FormA.intNewContestID

Another way to do the same thing is to declare in FormB a form
object
variable that will represent FormA:
Dim frmFormA As Form
Set frmFormA = Forms("FormA")
TheValue = FormA.intNewContestID
Set frmFormA = Nothing

However, as Jeff correctly noted, it's much more common to put a
public
variable in a "regular" module, not a form module, so that you
then do
not
need the "form" qualifier to reference it, and you don't need to
worry
about
whether FormA is open or not. Of course, you then must reset the
variable
to
a default value when you're done with it so that it doesn't "carry
over"
unexpected values the next time you use it in, say, a "count"
step:
intNewContestID = intNewContestID + 1

--

Ken Snell
<MS ACCESS MVP>


OK Ken here is the code behind the two modules. As you can see
I an
not
"re-DIMing the variables. I read your responce to Jeff but did
not
understand what you meant by "qualified"

Please help.
-----------------------------------------------------------------------------
Public strNewContestName As String
Public intNewContestID As Long
Option Comp
are Database
-------------------------------------------------------------------------------
Private Sub Command143_Click()
On Error GoTo Err_Command143_Click

' Test to see if this event is offered in the other contest
intNewContestID = DLookup("[LinkTo ID]", "[Contests]", _
"[Contest ID] = Forms![Team Entry]![Contest Ident]")

intNewFeeID = Nz(DLookup("[Fee ID]", "[Team Fees]", _
"([Contest ID] = intNewContestID) and " _
& "([Category ID]= Forms![Team Entry]![Category
ID])"),
0)
strNewContestName = DLookup("[Contest Name]", "[Contests]", _
"[Contest ID] = intNewContestID")

' If not send message and exit
If intNewFeeID = 0 Then
MsgBox ("You can not move this team deffnition to the " &
strNewContestName _
& " contest because " & [Combo42].[Column](2) & " is not
offered
in
that contest")
Exit Sub
End If
MsgBox (intNewContestID & ", " & intNewFeeID & ", " &
strNewContestName)
'Open the switch team form
DoCmd.OpenForm "Team Switch", , , "[ID]=" & Me![ID]

Exit_Command143_Click:
Exit Sub
------------------------------------------------------------------------------------
MSGBOX shows 12, 243, Open 2006

Form B is opened and click on the SAVE button runs this code.

MSGBOX shows , ,
------------------------------------------------------------------------------------------------

Private Sub Command6_Click()
On Error GoTo Command6_Click_Err

MsgBox (intNewContestID & ", " & intNewFeeID & ", " &
strNewContestName)

' Zero to Fee ID
Forms![Team Switch]![Fee ID] = intNewFeeID


:

Steve

I may be remembering incorrectly, but I thought that only
those
variable
defined in code module (and not in forms & reports) were
treated
as
"share-able" within the .mdb file.

They can be shared, but they must be qualified by the form's
module
reference (just as one would do with calling a public procedure
inside
a
form's module), and of course the form must be open at the time
that
another
module wants to read the values. (The public variable
essentially
is a
read/write property of the form.)


But rather than passing variable values from form to form,
why
not
have
form2 "look at" form1 and get its values? Or even, if you
MUST
pass,
use
the OpenArgs property and pass in values via OpenArgs.

Or write the value into a hidden textbox on the form and read
the
value
from
that textbox.

 

[Steve S]

BINGO!!! Got it. The problem was in the syntax all the time, not in the
definitions or logic. I figured it out after your previous post. That post
got me to look at the syntax.

Thank again.

Steve

Typo in my post. Here is corrected version:
-----------
Oh, I see the problem. Sorry that I missed it before.

You must concatenate the value of the variable into the expressions. You're
using the name of the variable instead.

Change this code step:

intNewFeeID = Nz(DLookup("[Fee ID]", "[Team Fees]", _
"([Contest ID] = intNewContestID) and " _
& "([Category ID]= Forms![Team Entry]![Category ID])"), 0)


to this:

intNewFeeID = Nz(DLookup("[Fee ID]", "[Team Fees]", _
"([Contest ID] = " & intNewContestID & ") and " _
& "([Category ID]= Forms![Team Entry]![Category ID])"), 0)


Make similar changes to the other code steps where you use the global
variables.

--

Ken Snell
<MS ACCESS MVP>

Oh, I see the problem. Sorry that I missed it before.

You must concatenate the value of the variable into the expressions.
You're using the name of the variable instead.

Change this code step:

intNewFeeID = Nz(DLookup("[Fee ID]", "[Team Fees]", _
"([Contest ID] = intNewContestID) and " _
& "([Category ID]= Forms![Team Entry]![Category ID])"), 0)


to this:

intNewFeeID = Nz(DLookup("[Fee ID]", "[Team Fees]", _
"([Contest ID] = " & intNewContestID) & " and " _
& "([Category ID]= Forms![Team Entry]![Category ID])"), 0)


Make similar changes to the other code steps where you use the global
variables.

--

Ken Snell
<MS ACCESS MVP>

Yes, the dim statements are in a regular module that shows in the
database
window and I used the "find" on the current project to verify they are
not
Dim ed anywhere else. Compiling shows no errors. I give up - sounded
like a
good idea but I need to move ahead.

I am going to go back to the tried and true method of defining text
fields
in FormA to save the values I need and have FormB refer to these fields.
I
have to get this project done ASAP so I will trouble shoot this issue
with a
new clean DB in a few days. Appreciate your help. Maybe you will be 'on
duty' when I repost this issue later in the week.

Steve



:

Did you put these public declarations in a 'regular' module (one that
can be
seen in the list of modules in the Modules window of the database
window)?
Is the name of the 'regular' module different from any of the procedures
and
variables that are in the module?

Did you remove the Dim statements from the form modules for these
variables?

Try compiling the code (in the VBE window, use Debug | Compile) and see
if
you get any errors.

--

Ken Snell
<MS ACCESS MVP>


Still get the same error. Here is the code in the module. The
Function
MyNoData is just one of several procedures and functions in this
module:
--------------------------------------------------------------------
Option Compare Database
Option Explicit

Public intNewFeeID As Long
Public strNewContestName As String
Public intNewContestID As Long

Public Function MyNoData(strReport As String)

On Error GoTo MyNoData_Err

MsgBox "There are no " & strReport & " to be printed", vbOKOnly,
"No
Data"
DoCmd.CancelEvent

MyNoData_Exit:
Exit Function

MyNoData_Err:
MsgBox Error$
Resume MyNoData_Exit

End Function
------------------------------------------------------------------

Here is the code where I use the variables and where the error is
generated:

---------------------------------------------------
Private Sub Command143_Click()
On Error GoTo Err_Command143_Click

' Test to see if this event is offered in the other contest
intNewContestID = DLookup("[LinkTo ID]", "[Contests]", _
"[Contest ID] = Forms![Team Entry]![Contest Ident]")

intNewFeeID = Nz(DLookup("[Fee ID]", "[Team Fees]", _
"([Contest ID] = intNewContestID) and " _
& "([Category ID]= Forms![Team Entry]![Category ID])"), 0)

strNewContestName = DLookup("[Contest Name]", "[Contests]", _
"[Contest ID] = intNewContestID")

' If not send message and exit
If intNewFeeID = 0 Then
MsgBox ("You can not move this team deffnition to the " &
strNewContestName _
& " contest because " & [Combo42].[Column](2) & " is not
offered in
that contest")
Exit Sub
End If
MsgBox (intNewContestID & ", " & intNewFeeID & ", " &
strNewContestName)
'Open the switch team form
DoCmd.OpenForm "Team Switch", , , "[ID]=" & Me![ID]

Exit_Command143_Click:
Exit Sub

Err_Command143_Click:
MsgBox Err.Description
Resume Exit_Command143_Click

End Sub
-----------------------------------------------
:

Do not put the Dim statements inside a sub or function. They go at
the
top
of the module before any procedures. This is the "Declarations"
section
of a
module.


Option Compare Database
Option Explicit

Public intNewFeeID As Long
Public strNewContestName As String
Public intNewContestID As Long


Public Sub Procedure1()
MsgBox "This is a test."
End Sub

--

Ken Snell
<MS ACCESS MVP>



Maybe I got ahead of myself but I removed the 3 variable definition
statements from the declaratives section of FormA and placed them
in
the
public module shown below.
---------------------------------------------------------------------------
Public Sub MyVariables()
'These variables are used to share or pass values between Forms or
Reports

Public intNewFeeID As Long
Public strNewContestName As String
Public intNewContestID As Long
End Sub
----------------------------------------------------------------------------

I made no other changes to the code that I sent you earlier. Now
when
I
execute the code in FormA I get the following error on the first
occurrence
of one of the variable names.

"The expression you entered as a query parameter produced this
error:
The
object doesn't contain the Automation object intNewContestID"

Do I have to qualify these variables? If so, How?

Steve


:

When you reference the "public" variable that is another form's
module,
you
must qualify it by including the reference to the module in which
it's
located. Assuming that the form that "Dim"'s the variable is named
"FormA",
you would use this syntax in FormB to get the value of the
variable:
Form_FormA.intNewContestID

Another way to do the same thing is to declare in FormB a form
object
variable that will represent FormA:
Dim frmFormA As Form
Set frmFormA = Forms("FormA")
TheValue = FormA.intNewContestID
Set frmFormA = Nothing

However, as Jeff correctly noted, it's much more common to put a
public
variable in a "regular" module, not a form module, so that you
then do
not
need the "form" qualifier to reference it, and you don't need to
worry
about
whether FormA is open or not. Of course, you then must reset the
variable
to
a default value when you're done with it so that it doesn't "carry
over"
unexpected values the next time you use it in, say, a "count"
step:
intNewContestID = intNewContestID + 1

--

Ken Snell
<MS ACCESS MVP>


OK Ken here is the code behind the two modules. As you can see
I an
not
"re-DIMing the variables. I read your responce to Jeff but did
not
understand what you meant by "qualified"