Solved Pass Variable (1 Viewer)

RevJeff

Registered User.
Local time
Today, 08:26
Joined
Sep 18, 2002
Messages
125
Let me see if I can explain this.....So I have a database that sends out emails as text messages to drivers and allows for attachments, which works fine. Currently all of the code in on the "Send" button, but I am trying to separate the code so when you click the Attachment check box you get the option to select the files and then you hit the "Send" button to send the email. Below is the code I have so far .

"Attachment" Check Box
Code:
Public Sub Check82_Click()

Dim FD As Object
Dim strName As String

If Check82 = True Then
Set FD = Application.FileDialog(3)
End If

Dim colFiles As New Collection
Dim vFile

If Check82 = True Then
FD.AllowMultiSelect = True
FD.Filters.Clear
FD.Filters.Add "All Files", "*.jpg, *.gif, *.tif, *.png"
    FD.InitialFileName = strName

    If FD.Show = True Then
FD.InitialView = 6
For Each vrtSelectedItem In FD.SelectedItems
colFiles.Add vrtSelectedItem
Next
    End If

End If

End Sub


And I want to pass the "colFiles" to the "Send" button below.

"Send" Button
Code:
Private Sub btnMsgYard_Click()

DoCmd.SetWarnings False
DoCmd.OpenQuery "delDriverMessage", acViewNormal, acEdit

If Check82 = True Then
    DoCmd.OpenQuery "apdDriverMessageMMS", acViewNormal, acEdit
Else

DoCmd.OpenQuery "apdDriverMessage", acViewNormal, acEdit

End If

DoCmd.SetWarnings True

Dim rs As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb

strSQL = "SELECT Driver, CellExt FROM tblDriverMessage"
Set rs = db.OpenRecordset(strSQL)

    rs.MoveFirst
   
    Do Until rs.EOF
           
        Dim MyRs As DAO.Recordset
        Dim strAddr
        Dim strSubject
        Dim strBody
        Dim oOutlook As Object
        Dim oEmailItem As Object
       
        With MyRs
   
            strAddr = rs.Fields("CellExt")
            strSubject = "Sysco"
            If IsNull(Text31) Then
                strBody = ""
            Else
                strBody = Text31
            End If
   
            Set oOutlook = GetObject(, "Outlook.Application")
            If oOutlook Is Nothing Then Set oOutlook = CreateObject("Outlook.Application")
           
            Set oEmailItem = oOutlook.CreateItem(olMailItem)
           
            With oEmailItem
                .To = strAddr
                .Subject = strSubject
                .Body = strBody
               
'************ Pass colFiles here
                If Check82 = True Then
                    For Each vFile In colFiles
                        .Attachments.Add vFile, olByValue, 1
                    Next
                End If
'************ Pass colFiles Here
               
                .send
            End With
           
            Set oEmailItem = Nothing
            Set oOutlook = Nothing
           
        End With
        rs.MoveNext
   
    Loop

Set rs = Nothing
Set db = Nothing

Image6.Visible = True
Text31 = Null

Check82 = False

End Sub

Thanks in advance!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:26
Joined
May 21, 2018
Messages
8,463
Not sure How you want to do it, but you cannot send it to the click method.

You can have the Send procedure go get the collection
Code:
Public Function GetDocuments() as collection
  Dim FD As Object
Dim strName As String

  If Check82 = True Then
   Set FD = Application.FileDialog(3)
  End If

Dim colFiles As New Collection
Dim vFile

If Check82 = True Then
FD.AllowMultiSelect = True
FD.Filters.Clear
FD.Filters.Add "All Files", "*.jpg, *.gif, *.tif, *.png"
    FD.InitialFileName = strName

    If FD.Show = True Then
FD.InitialView = 6
For Each vrtSelectedItem In FD.SelectedItems
colFiles.Add vrtSelectedItem
Next
    End If

  Sel GetDocuments = ColFiles
End If
Then
Code:
Private sub MsgYard_Click()
  dim msgcollection as collection
  set msgcollection = GetDocuments
end sub
 

vba_php

Forum Troll
Local time
Today, 07:26
Joined
Oct 6, 2019
Messages
2,884
well I'm not sure if this is the right way that you're going about it. but can't you pass a collection object to another routine?
Code:
send_button_code(myCollection as collection)
    ' code here
furthermore, your collection is a collection of variants. i believe your vFile argument is taking a string, isn't it? if it is, you'll have to convert the data type otherwise it'll throw an error.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:26
Joined
May 21, 2018
Messages
8,463
well I'm not sure if this is the right way that you're going about it. but can't you pass a collection object to another routine?
You can, but you cannot send it to the Button_Click event. If you change the signature of an event procedure it should not compile.
If they turn the btnMsgYard_Click into a stand alone procedure
Public Sub MsgYard(MsgCol as collection)
end sub
then they can.
 

vba_php

Forum Troll
Local time
Today, 07:26
Joined
Oct 6, 2019
Messages
2,884
If they turn the btnMsgYard_Click into a stand alone procedure
Public Sub MsgYard(MsgCol as collection)
end sub
then they can.
much noted, mr. wheelhouse. :D
 

RevJeff

Registered User.
Local time
Today, 08:26
Joined
Sep 18, 2002
Messages
125
But won't this still keep everything running off of the "Send" button? I currently have it setup that way and it runs great. They click the checkbox, just to show there will be an attachment, then they type their message, then when they click the "Send" button it allows them to select the files.

I was asked to separate the functions. First they click the "Attachment" checkbox to select the files. Then the can write a message in the textbox and then they click the "Send" button.

Thanks again!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:26
Joined
May 21, 2018
Messages
8,463
much noted, mr. wheelhouse.
Trust me this is an important one for you to put in your wheelhouse as well, because chasing the error down can be hard really hard if you do not know what the error message means.

Imagine you create a beforeUpdate event.
Private Form_beforeUpdate(cancel as integer)
and you decide you want an after update and change it to
Private Form_afterUpdate(cancel as integer)
But an after update does not have a cancel argument.

The code will compile, but when you run it you get the below message. However, it does not break in the code, so you may have no idea what is wrong. You have to go into VBE and hit debug to find it.
 

Attachments

  • BeforeUpdate.jpg
    BeforeUpdate.jpg
    24.6 KB · Views: 217

theDBguy

I’m here to help
Staff member
Local time
Today, 05:26
Joined
Oct 29, 2018
Messages
21,358
Hi Jeff. Pardon me for jumping in, but this could be a case where you would use a variable with a wider scope. For example, you can declare a Form level collection and store the selected files there. In the send button code, you can then use it. Just a thought...
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:26
Joined
May 21, 2018
Messages
8,463
If you are not going to have the procedures call each other, then the only way to do that would be to persist the documents in a variable.

At the top of the module
Private MsgDocs as collection

First event save to MsgDocs
The other Event uses the public variable then sets the MsgDocs to nothing.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:26
Joined
May 21, 2018
Messages
8,463
@RevJeff,
theDBGuy and I are saying the same thing, he beat me.
 

RevJeff

Registered User.
Local time
Today, 08:26
Joined
Sep 18, 2002
Messages
125
Thank you all for all of your help! That worked perfectly!
 

Users who are viewing this thread

Top Bottom