Ken Puls
02-16-2006, 12:55 PM
Hi guys,
I'm trying to pass a 13x2 array to a subroutine, but I keep getting a "ByRef Argument Type Mismatch". I'm not sure what to do about it...
Here's the code that is receiving the array:
Sub Invoice_InsertDetail(lInvoiceID As Long, aryInvDetail() As Variant)
'Date Created : February 16,2006 11:31
'Author : Ken Puls
'Macro Purpose: Place an array of invoice detail data into the database
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim colHead As String
Dim lDeptID As Long
Dim indx As Long
Dim sSQL As String
'Concatenate a string with the names of the column headings
colHead = "(ExpTypeID,Amount,DeptID,InvID)"
lDeptID = Application.WorksheetFunction.Index(_
ActiveWorkbook.Worksheets("Tables").Range("tblDepts"), _
ActiveSheet.Range("VBA_Dept"))
'Open connection to the database
cnt.Open sConnect
'Build the SQL statement
For indx = 0 To 13
sSQL = "INSERT INTO tblInvDetail " & colHead & " VALUES (" & _
GetExpTypeID(aryInvDetail(indx, 0)) & "," & _
aryInvDetail(indx, 1) & "," & lDeptID & "," & lInvoiceID & ");"
rst.Open sSQL, cnt
Next indx
'Close the ADO objects
cnt.Close
Set rst = Nothing
Set cnt = Nothing
End Sub
Highlighted code is this part: aryInvDetail(indx, 0)
I've tried:
Sub Invoice_InsertDetail(lInvoiceID As Long, ByRef aryInvDetail() As Variant)
Sub Invoice_InsertDetail(lInvoiceID As Long, ByRef aryInvDetail As Variant)
Sub Invoice_InsertDetail(lInvoiceID As Long, aryInvDetail As Variant)
Can anyone help out on this? It's driving me crazy!
Thanks!
I'm trying to pass a 13x2 array to a subroutine, but I keep getting a "ByRef Argument Type Mismatch". I'm not sure what to do about it...
Here's the code that is receiving the array:
Sub Invoice_InsertDetail(lInvoiceID As Long, aryInvDetail() As Variant)
'Date Created : February 16,2006 11:31
'Author : Ken Puls
'Macro Purpose: Place an array of invoice detail data into the database
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim colHead As String
Dim lDeptID As Long
Dim indx As Long
Dim sSQL As String
'Concatenate a string with the names of the column headings
colHead = "(ExpTypeID,Amount,DeptID,InvID)"
lDeptID = Application.WorksheetFunction.Index(_
ActiveWorkbook.Worksheets("Tables").Range("tblDepts"), _
ActiveSheet.Range("VBA_Dept"))
'Open connection to the database
cnt.Open sConnect
'Build the SQL statement
For indx = 0 To 13
sSQL = "INSERT INTO tblInvDetail " & colHead & " VALUES (" & _
GetExpTypeID(aryInvDetail(indx, 0)) & "," & _
aryInvDetail(indx, 1) & "," & lDeptID & "," & lInvoiceID & ");"
rst.Open sSQL, cnt
Next indx
'Close the ADO objects
cnt.Close
Set rst = Nothing
Set cnt = Nothing
End Sub
Highlighted code is this part: aryInvDetail(indx, 0)
I've tried:
Sub Invoice_InsertDetail(lInvoiceID As Long, ByRef aryInvDetail() As Variant)
Sub Invoice_InsertDetail(lInvoiceID As Long, ByRef aryInvDetail As Variant)
Sub Invoice_InsertDetail(lInvoiceID As Long, aryInvDetail As Variant)
Can anyone help out on this? It's driving me crazy!
Thanks!