Why do you have LoopReport as a function when it is not returning any
variables. Why not make it a Sub. I believe the crashing is occuring
because you have a stack error. When you declare the routine a function it
is expecting something to be put on the stack and something to be taken of
the stack. You are calling LoopReport from excel like a Sub which isn't
putting anything on the stack. Then Access is leaving a space on the stack
for a return variable where excel didn't make room for the return variable on
the stack. This create a stack mis-match and the cause of the crash.

"Michelle" wrote:

Joel,

Thank you for your idea. I actually can't use the line
Results = LoopReport(NumberofAccounts, accts, startDate)
because LoopReport is a function in Access not Excel where I'm trying to
call it from. Excel won't recognize LoopReport and error out using the above
line. I did, however, figure out the syntax to properly pass parameters into
Access from Excel.

Dim A As Access.Application
Set A = New Access.Application
A.Visible = True
A.OpenCurrentDatabase "G:PC Reports New\PC Report.mdb"
A.Run "LoopReport", ans, accts(), startDate, endDate

Where the function is set up as
Function LoopReport(NumAccts As Integer, accts() As String, startDate As
Date, endDate as Date)

Now for my other question as to why Access doesn't always close, I'm still a
little perplexed. I have tried running the procedure directly from Access
and not using Excel. (I was testing my function before moving on to the
Excel portion.) I never received any errors in Access. Everything seemed to
run properly. Do you think it has something to do with the fact that I have
the database automatically compact and repair the .mdb file every time it
closes? I do this because otherwise the .mdb file seems to grow very big
very quickly. I neglected to mention this before, my apologies. Your
thoughts?

--
Cheers,
Michelle
"Anyone who says he can see through women is missing a lot." Groucho Marx


"Joel" wrote:

I suspect that Access isn't closing because you are getting some errors in
Access and it is not returning to excel

Does the code below help with the parameter passing? With the IsMissing you
can either call the function with or without parameters. The call wil be
with

Sub ExcelCall
Dim Accts as Variant

NumberofAccounts = 10
Redim Accts(5)
Accts(0) = "Michelle Account"

startDate = "3/1/09"

Results = LoopReport(NumberofAccounts, accts, startDate)

end Sub


Function LoopReport(Optional NumberofAccounts as Integer, Optional accts As
Variant, Optional startDate as String)

If IsMissing(NumberofAccounts) Then
NumberofAccounts = InputBox("How many accounts would you like to look
at?" & _
vbNewLine & vbNewLine & _
"Please note that the more accounts you choose the longer the " & _
"queries will take to run.", "PC Report")
End If

If IsMissing(accts) Then
ReDim accts(1 To NumberofAccounts)
For i = 1 To NumberofAccounts
accts(i) = InputBox("Enter account " & i & ":", "Account Parameters")
Next i
End If
If IsMissing(startDate) Then
startDate = InputBox("Enter the start date for the range of the report."
& _
vbNewLine & vbNewLine & _
"Please do not input more than 1 month.", "Date Range")
End If


End Function