Public variable not passing to private sub 1

[jpindi]

This is a problem that is a real puzzler and I've got a similar thing working in the same module so I'm not sure why it is not working in this particular instance. I'm posting what I think are the salient points, not the entire code (although I can do that if the solution doesn't seem obvious).

The problem is this. I've declared a variable public as a string. There is a public sub which collects information from a form and feeds into the string. That part works, because I create a message box that shows me information which is correct.

The variable seems to dump the information once it reaches the private sub, though. I know this because the message box in the private sub shows no information for that variable.

I need the information from that string because I use it to filter a query (I didn't post that part because it happens further down the code and is really not relevant to my problem...again I can post everything if need be).

Here are the snippets of code giving me problems:

Option Compare Database
Public strMastAdd As String '-----THIS IS THE VARIABLE TO BE PASSED FROM THE PUBLIC FUNCTION TO THE PRIVATE FUNCTION
Option Explicit

Public Sub Create_strMastAdd()
'On Error GoTo errorhandler
'-----------------VARIABLES FROM THE "MASTER" FORM FIELDS--------
Dim strFrmCom As String '----Company
strMastAdd = "" '------CLEANS UP

'-------------SEE IF VARIABLES ARE ENTERED IN THE MASTER FORM--------
'---------IF THEY ARE, ADD THEM TO MASTER SQL STATEMENT------------
strFrmCom = Me.MasterCompany & ""
If Len(strFrmCom) > 0 Then
strFrmCom = Replace(strFrmCom, "'", "''")
strMastAdd = "AllCompanyNames LIKE '%" & strFrmCom & "%'"
End If

MsgBox strMastAdd '------------THIS PART SHOWS SOME INFORMATION OK.
End Sub

Private Sub btnReport_Click()

If MsgBox("This report could take a few minutes to generate depending on the number of records available." & _
vbNewLine & vbNewLine & "Anywhere from a few minutes to half an hour (apologies in advance for the wait)." & _
vbNewLine & vbNewLine & "It will run using the criteria you have chosen in the fields at left. Please make sure they are what you want." & _
vbNewLine & vbNewLine & "Continue?", vbYesNo, "Run Report") = vbNo Then
Exit Sub
End If

'-------CALLS THE PUBLIC SUB---------------------
Create_strMastAdd
MsgBox "strMastAdd: " & strMastAdd
'-------NO INFORMATION APPEARS.

'-------LATER ON THERE IS SOME CODE WHICH USES strMastAdd
End Sub

 

[dhookom]

I would enable all the error handling. There seem to be times when an error occurs that memory variables are lost.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

I do not see the problem, but it is hard to read what you have. You can block your code to read easier

Option Compare Database
Option Explicit
'
Public strMastAdd As String '-----THIS IS THE VARIABLE TO BE PASSED FROM THE PUBLIC FUNCTION TO THE PRIVATE FUNCTION

I did notice that option explicit was after your public variable. That was probably a typo because it should not compile.

Private Sub btnReport_Click()

If MsgBox("This report could take a few minutes to generate depending on the number of records available." & _
vbNewLine & vbNewLine & "Anywhere from a few minutes to half an hour (apologies in advance for the wait)." & _
vbNewLine & vbNewLine & "It will run using the criteria you have chosen in the fields at left. Please make sure they are what you want." & _
vbNewLine & vbNewLine & "Continue?", vbYesNo, "Run Report") = vbNo Then
    Exit Sub
End If

'-------CALLS THE PUBLIC SUB---------------------
Create_strMastAdd
MsgBox "strMastAdd: " & strMastAdd
'-------NO INFORMATION APPEARS.
'-------LATER ON THERE IS SOME CODE WHICH USES strMastAdd
End Sub

Public Sub Create_strMastAdd()
'On Error GoTo errorhandler
'-----------------VARIABLES FROM THE "MASTER" FORM FIELDS--------
Dim strFrmCom As String     '----Company
strMastAdd = "" '------CLEANS UP

'-------------SEE IF VARIABLES ARE ENTERED IN THE MASTER FORM--------
'---------IF THEY ARE, ADD THEM TO MASTER SQL STATEMENT------------
strFrmCom = Me.MasterCompany & ""
If Len(strFrmCom) > 0 Then
    strFrmCom = Replace(strFrmCom, "'", "''")
    strMastAdd = "AllCompanyNames LIKE '%" & strFrmCom & "%'"
End If

MsgBox strMastAdd '------------THIS PART SHOWS SOME INFORMATION OK.
End Sub

My first guess is that you have a private variable called strMastAdd in the routine strMastAdd. It does not appear that way, but your code is edited. If you have a private variable in a procedure with the same name as the public variable, the module compiles and the procedure runs and ignores the public variable. So although the public strMastAdd has a value the local strMasAdd = "". That could give you these symptoms.

However this should not be a public variable instead make it a function.

Public function Create_strMastAdd()as string
  do all of your code
  create_StrMastAdd = ....
end function

 

[jpindi]

Hi Ok - your first guess was right MajP I had accidentally left the variable in the private function (arg). Ok, it works fine, now. Thanks!

 

[MajP]

I have done it before, that why I guessed correctly. However, do consider using a function. Public variables have a purpose, but always try to limit the scope and lifetime of all variables.

 

[dhookom]

jpindi,
It's appropriate to award a star to a post that answers your question. This recognizes the member who helped and also marks the thread as answered.

Duane
Hook'D on Access
MS Access MVP

 

[JoeAtWork]

This is why I use naming conventions to show the scope of variables. For example:

Public g_strMastAdd As String

Private Sub MySub()
   Dim strMastAdd As String
End Sub