How can remove old data from apublic string?

[slmckenzie]

I have 2 forms that use the same functions and created a stand alone module. the first time I press a button on the form I pull the right data say "1z". I go to the next record on the form and press the button again and it adds the new data and still has the old data say "1z Fed" and and each time I do this it adds more old and new data, until I close the Access database and reopen it. I have set it to nothing and tried placing it in diferent places in the code and it clears the data till it the function that populates the string and the old data is back.

[jforbes]

It sounds like the variable that is being used by the Function has been declared as a Global Variable instead of creating a Variable each time the Function is called.

There is a remote possibility that the Variable is being passed ByRef which would also do this, but most people don't use this.

Things to check or do:

• Lots of times in a situation like this a parameter (typically a Boolean) is included in the function that lets the Function know to either clear the Variable or not to clear the Variable. First look to see if something like this is built into the Function.
• See if the ByRef keyword is used in the declaration. The following is an example of three ways to identify a parameter. The first two are the same and create a copy of the supplied variable. The last sends a reference to the Variable, so if the Variable is changed in the Function, it is changed in the code that called the Function:
  

  Public Function someFunction(sString As String) As String
  Public Function someFunction(ByVal sString As String) As String
  Public Function someFunction(ByRef sString As String) As String

  In this case you can clear the Variable before calling the Function.
• If you can't do the above, but you know the Global Variable name, which I'm guessing you do by your Question, you could set the Global Variable to a blank string before calling the Function.
  

  sString=""

• If you are still stumped, you can post the code to the thread (both calling the Function and the Function itself) and we can help you out.

One last thing to note. This type of behavior is more likely to happen if your code modules don't have Option Explicit at the top of them as Option Explicit will make sure all variables are declared.

[Seth Schrock]

So you have two forms pulling the same function, I'll call them Form_1 and Form_2. Do you want the second time that Form_1 calls the function to remember the first string?

[slmckenzie]

Sub btnSendtoEmail_Click()
Dim MyHandDeliver As Boolean
Receiving = True
    
    MyHandDeliver = Nz(Forms("_frmReceivablesToEmail").Controls("qryToEmailsubform").Form.Controls("ynHandDeilver").Value)

    If MyHandDeliver = True Then
        MsgBox "This package can be hand delivered."
    End If

''''Calls the GetCount function on the Form Module

    GetCounts

    'Set myOlApp = Nothing
    PackageCount = 0
    
End Sub
''2
Private Function GetCounts()
Dim tmprs As DAO.Recordset
Dim fld As DAO.Field
Dim Sender As String
Dim MyNameIs As String
Dim MyValueIs As String
'Dim Sender As String
Dim SendersCount As Long
Dim Courier As Long
Dim CourierCount As Long


Set tmprs = qryToEmailsubform.Form.RecordsetClone

tmprs.MoveFirst

While Not tmprs.EOF
    For Each fld In tmprs.Fields
        MyNameIs = fld.Name
        MyValueIs = Nz(fld.Value)
        
            If MyNameIs = "strPONumber" Then
                If IsNull(MyValueIs) = True Then
                    PO = False
                ElseIf (MyValueIs) = "" Then
                    PO = False
                Else
                    'MsgBox "Value " & fld3.Value
                    PO = True
                End If
            End If
        
            If MyNameIs = "Package_Count" Then
                If Not IsNull(MyValueIs) = True Then
                PackageCount = PackageCount + 1
                Else
                End If
            End If
        
            If MyNameIs = "blnPerishable" Then
                If MyValueIs = True Then
                    PerishableCount = PerishableCount + 1
                Else
                End If
            End If
            
            If MyNameIs = "Sender" Then
                If Not IsNull(MyValueIs) = True Then
                SendersName = MyValueIs
                    SendersCount = SendersCount + 1
                Else
                End If
            End If

            If MyNameIs = "Courier" Then
                If Not IsNull(MyValueIs) = True Then
                    CouriersName = MyValueIs
                    CouriersCount = CouriersCount + 1
                Else
                End If
            End If
        
    Next
    tmprs.MoveNext

Wend
    
'Debug.Print SendersName & SendersCount & CouriersName & CouriersCount

'''Goes to the Standalone Module

AddaTrackingNumber

End Function

''' We are at the standalone module  where i manipulate the data.


Option Explicate

Public ConvertedTrackingNumber          As Variant


Function SevenSpacePunctuation()

''Data looks like this "4567 0213 1111 4567 0213 2222 4567 0213 3333 4567 0213 4444"

pInput = TrackingfNonDelimitedStrSorter

astrPieces = Split(pInput)
lngUBound = UBound(astrPieces)
i = 1
Do While i <= lngUBound
    If (i + 6) < lngUBound Then
        strOutput = strOutput & ", "
    Else
        strOutput = strOutput & " and "
    End If
    strOutput = strOutput & astrPieces(i) & " " & astrPieces(i + 1) & " " & astrPieces(i + 2) & " " & astrPieces(i + 3) & " " & astrPieces(i + 4) & " " & astrPieces(i + 5) & " " & astrPieces(i + 6)
    i = i + 7
Loop
If Len(strOutput) > 0 Then
    ' discard leading ", "
    strOutput = Mid(strOutput, 3)

End If


ConvertedTrackingNumber = strOutput 'data looks like this  "4567 0213 1111, 4567 0213 2222, 4567 0213 3333 and 4567 0213 4444"

'' Goes back to the form module.

End Function

Private Function MultiplePackages()

    ' Create the Outlook session.
    Set objOutlook = CreateObject("Outlook.Application")

    ' Create the message.
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
        'TheAddress = "Shawn"

    With objOutlookMsg
        .To = Full_Name

    End With
    
    If PackageCount = 1 Then
    
        IndividualPackage
    
    Else
    
        MultiplePackages
    
    End If
           
        'Send email from team mailbox
        '.SentOnBehalfOfName = "shawn mckenzie"

    With objOutlookMsg
            If PO = True Then
                .Subject = "Delivery concerning Purchase Order " & ConvertedPONumbers
            ElseIf PO = False Then
                .Subject = "Delivery"
            Else
            End If

            .Body = "You have received " & PackageCount & " packages from " & SendersName & ", tracking number " & ConvertedTrackingNumber & ", you can pick them up in the RFQ Room. " & vbCrLf & vbCrLf & "Thank You" & vbCrLf & vbCrLf & _
   .display
    End With

End Function

[Seth Schrock]

If you want the strings to build up within each form, then you could move your module into a class module or you could create a module level variable within your form and have your function actually return the value instead of passing it off to the global variable.

If you don't want the strings to build up at all, then do just the second half of the second option.

[slmckenzie]

I'm not using Byref or Byval. I have tried to setting it to empty and sString="". The value goes a way until I add values to the string and the old data comes back.

I fixed the problem by putting a Dim statement for the string I was feeding to the public string.