passing an array to another subroutine

[EAB1977]

Is it posible to pass an array to a subroutine? I am trying with the
below code and I get a ByRef argument type mismatch Compile Error. Here
is my code below.

Private Sub btn10Week_Click()
Dim dbs As DAO.Database, rst As DAO.Recordset, i As Integer
Dim Recipients() As String

DoCmd.OpenForm "frmCriteria", , , , , acDialog, "Three"
If fIsLoaded("frmCriteria") = 0 Then Exit Sub
msg = MsgBox("Do you want to preview the report or upload it?
Selecting No will upload the report to CQA Reports database.", vbYesNo)
If msg = vbYes Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryTbl10Week"
DoCmd.SetWarnings True
DoCmd.OpenReport "rptGloss10Weeks", acViewPreview
Else
strRTF = "\\files-2k1\ENG\QA\Database\Gloss\Reports\10Week.rtf"
Kill strRTF 'Delete the previous report
DoCmd.OutputTo acOutputReport, "rptGloss10Weeks", "Rich Text
Format (*.rtf)", strRTF 'Regenerate the report

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT Name FROM tblGlossDistList
WHERE Report = 3")
rst.MoveLast

ReDim Recipients(0 To 10) As String
i = 0
rst.MoveFirst
Do Until rst.EOF
Recipients(i) = rst!Name
i = i + 1
Loop
rst.Close
Set rst = Nothing
Set dbs = Nothing

Call LotusNotes(strRTF, "Gloss Testing", Recipients, "Bi-Weekly
Gloss Report", "Please report to Eric Brenner if any problems arise.")

End If
End Sub

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Sub LotusNotes(File As String, strReportName As String, ByRef strSendTo
As String, _
Optional strDecription As String, Optional Comments As
String)

Dim session As Object, doc As Object, db As Object, rtitem As Object,
DocUID As String

Set session = CreateObject("Notes.Notessession")
'Set db = session.GetDatabase("MAS-NS2/MAS/DCC", "CQAReprt.nsf")
'Production Server
Set db = session.GetDatabase("DEV-NS1/MAS/DCC", "CQAReprt.nsf")
'Development Server

If Not db.IsOpen Then
MsgBox "CQA Reports database did not open or is not found.", ,
"Lotus Notes Error"
GoTo Unload
End If

Set doc = db.CREATEDOCUMENT
Call doc.ReplaceItemValue("Form", "MainTopic") 'Actual Name = "Main
Topic"
Call doc.ReplaceItemValue("ReportName", strReportName)
Call doc.ReplaceItemValue("From", "d" & NetworkUserName)
Call doc.ReplaceItemValue("PlantName", "CQA")
Call doc.ReplaceItemValue("ReportDate", Now)
Call doc.ReplaceItemValue("Description", strDecription)
Call doc.ReplaceItemValue("SendTo", strSendTo)

Set rtitem = doc.CREATERICHTEXTITEM("Report")
Call rtitem.EMBEDOBJECT(1454, "", File)
Call rtitem.ADDNEWLINE(2, True)
If Not IsNull(Comments) Or Not Comments = "" Then Call
doc.ReplaceItemValue("Comments", Comments)

Call doc.Save(True, True)

DocUID = doc.UniversalID
Call SendLinkedMessage(DocUID)

Unload:
Set doc = Nothing
Set db = Nothing
Set session = Nothing
End Sub

Private Sub SendLinkedMessage(sUID As String)

'SENDING LINKED e-MAIL
Dim NotesSession As Object
Dim NotesDb As Object
Dim NotesView As Object
Dim NotesAgent As Object
Dim NotesDocument As Object
Dim CurrentDocument As Object
Dim sFileNumber As String

Set NotesSession = CreateObject("Notes.NotesSession")
'Set NotesDb = NotesSession.GetDatabase("MAS-NS2/MAS/DCC",
"CQAReprt.nsf")
Set NotesDb = NotesSession.GetDatabase("DEV-NS1/MAS/DCC",
"CQAReprt.nsf")

Set NotesView = NotesDb.GetView("HiddenDocs")
Set CurrentDocument = NotesView.GetDocumentByKey(sUID, True)

Set NotesView = NotesDb.GetView("DocUID")
Set NotesDocument = NotesView.GetFirstDocument

Call NotesDocument.ReplaceItemValue("UID", sUID)

Call NotesDocument.Save(True, True)

Set NotesAgent = NotesDb.GetAgent("Notify")
NotesAgent.Run

MsgBox "An e-mail notification was sent to everyone in the
Notification list " & vbCr & _
"informing them that the Bi-Weekly Gloss Report was
published.", vbInformation, "CQA Reports"

End Sub

Function NetworkUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
NetworkUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(NetworkUserName, lngLen)
If (lngX > 0) Then
NetworkUserName = Left$(NetworkUserName, lngLen - 1)
NetworkUserName = Right$(NetworkUserName, lngLen - 2)
End If
End Function

 

[Klatuu]

Change the array's data type to Variant and pass it by Value.

 

[Stephen Lebans]

The signature for the LotusNotes function is incorrect. You've declared the
param as String but the variable you want to pass is an array of strings.

I'll guess you want to pass a single instance of a string, not an array of
strings. Therefore just pass the desired element of the array of strings.
For example:
Recipients(2)

If you really want to pass an array of strings to a function, you just have
to ensure that the function signature declares the param as an array of
strings. For example:
Private Function SubTest(sArray() As String)



Create a form. Add a CommandButton named "cmdTest" and and unbound TextBox
control named "text1".


Dim sSend(1 To 10) As String

Private Sub cmdTest_Click()
On Error GoTo Err_cmdTest_Click

Me.Text1.Value = ""
Call SubTest(sSend())

Exit_cmdTest_Click:
Exit Sub

Err_cmdTest_Click:
MsgBox Err.Description
Resume Exit_cmdTest_Click

End Sub


Private Function SubTest(sArray() As String)
Dim x As Long
Dim s As String
For x = LBound(sArray) To UBound(sArray) Step 1
sArray(x) = "level:" & x
s = s & sArray(x) & vbCrLf
Next x

Me.Text1.Value = s
End Function

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.