define error

[metalman]

am getting a undefined error on the 'EntryTable' but i didnt think that i had to define the table names.


Option Explicit

Public RowsCount As Integer
Public ColsCount As Integer

Public gColNameArr() As Variant
Public gRowNameArr() As Variant
Public gItemNumsArr() As Integer
Public gWhereStrArr() As String

Public fields As Collection

Public Sub Run()
Initialize
Import
End Sub
Public Sub Initialize()
Dim sht As Worksheet
Dim i As Integer
Dim j As Integer
Dim str As String
Dim where_str As String

ColsCount = 88
RowsCount = 42

ReDim gColNameArr(1 To ColsCount)
ReDim gRowNameArr(1 To RowsCount)
ReDim gItemNumsArr(1 To RowsCount, 1 To 7)
ReDim gWhereStrArr(1 To RowsCount)

'set columns array
Set sht = Worksheets("detail&quot
With sht
For i = 1 To 88
gColNameArr(i) = .Cells(5, i + 1)
Next i

For i = 1 To 42
gRowNameArr(i) = .Cells(i + 8, 1)
Next i
End With
Set sht = Nothing

Set sht = Worksheets("data&quot
With sht
For i = 1 To RowsCount
where_str = "WHERE ("
For j = 1 To 7
str = .Cells(i, j + 1)
If str = "" Then
gItemNumsArr(i, j) = 0
Else
gItemNumsArr(i, j) = CInt(str)
where_str = where_str & "EntryTable.[Item Number] =" & str & " OR "
End If
Next j
gWhereStrArr(i) = Left$(where_str, Len(where_str) - 4) & &quot "
Next i
End With
End Sub


Public Sub Import()
Dim sht As Worksheet
Dim conn_str As String
Dim sql_str As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim i As Integer
Dim j As Integer

conn_str = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Documents and Settings\rhinson\My Documents\reporting.mde"
Set cn = New Connection
cn.Open conn_str

Set sht = Worksheets("Detail&quot
For i = 1 To RowsCount
For j = 1 To ColsCount
sql_str = "SELECT EntryTable(amount) as m " & _
"FROM EntryTable INNER JOIN HeaderTable ON EntryTable.[Reference #] = HeaderTable.[REference #] " & _
gWhereStrArr(i) & "And ItemTable.[Item Number] = " & gColNameArr(j) & " "



Set rs = New Recordset
Debug.Print sql_str
rs.Open sql_str, cn, adOpenStatic, adLockReadOnly

sht.Cells(i + 7, j + 1) = rs.fields("m&quot

rs.Close
Set rs = Nothing
Next j
Next i

cn.Close
Set cn = Nothing
End Sub

 

[johnwm]

Do a debug.Print on your sql_str and check that there are spaces in the right places, including both sides of the '=' sign.

If that doesn't show the problem then cut and paste from the debug window into SQL Query analyser and see what you get from there. Let me know if this helps
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'

 

[metalman]

i am getting an error message of undefined function ' EntryTable' in expression. i have the debug.print sql_str in the code but dont give me anything.


sql_str = "SELECT EntryTable(Amount) as m " & _
"FROM EntryTable INNER JOIN HeaderTable ON EntryTable.[Reference #] = HeaderTable.[REference #] " & _
gWhereStrArr(i) & "And ItemTable.[Item Number] = " & gColNameArr(j) & " "

 

[CajunCenturion]

Given the following SQL snippet

SELECT EntryTable(Amount) as m

EntryTable(Amount) is being interpreted as a function with Amount as its parameter. If EntryTable is an array which contains ffield names, and Amount is the subscript into that array, then I would try the following:

SELECT " & EntryTable(Amount) & " as m

On the other hand, if EntryTable(Amount) is meant to imply that Amount if a field name within the Table EntryTable then I would try the following:

SELECT EntryTable.Amount as m

If EntryTable(Amount) means something else, the please share with us what it is supposed to mean. Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[metalman]

-have changed it but now getting a no value in required parameter error again. entryTable is a table and amount is a feild in that table. all i am want is to read the files in the tables and add all the amounts and put them into and excell spreadsheet under the right location.

sql_str = " SELECT EntryTable.Amount as m " & _
"FROM EntryTable INNER JOIN HeaderTable ON EntryTable.[Reference #] = HeaderTable.[REference #] " & _
gWhereStrArr(i) & "And ItemTable.[Item Number] = " & gColNameArr(j) & " "