arrays and loop question

  • Thread starter Thread starter Krzysztof via AccessMonster.com
  • Start date Start date
K

Krzysztof via AccessMonster.com

Good Afternoon,

I have a question i cannot seem to answer.

I have 2 arrays and a loop that perform certain functions, and when i call
this sub, i get and error message stating: "Compile Error: byRef Argument
Type Mismatch".
I am not sure what that means, but i have tried replacing the array
identifier with a variable, but whatever i do, i get the same message.

Here is a copy of the code:

Here is my function
Public Function TableExists(strTableName As String) As Boolean
On Error Resume Next

TableExists = IsObject(CurrentDb.TableDefs(strTableName))

End Function

And here is the sub
Public Sub CaptureData()

Dim Tables(3, 0), Queries(3, 0) As String
Tables(0, 0) = "tbl_INVOICE"
Tables(1, 0) = "tbl_WOITEMS"
Tables(2, 0) = "tbl_WRKORDER"
Tables(3, 0) = "tbl_EMPLOYEE"
Queries(0, 0) = "mtqry_Invoice"
Queries(1, 0) = "mtqry_WOItems"
Queries(2, 0) = "mtqry_Wrkorder"
Queries(3, 0) = "mtqry_Employee"

'===============================================
'Create local tables
'Check to see if local table exists and if so, delete it
Me.bonus_progress.Visible = True 'activate the progress bar
Me.bonus_progress.value = 0 'reset the progress bar

Dim i As Integer
For i = 0 To 3
If TableExists(Tables(i,0)) = True Then 'check to see if table
exists
DoCmd.DeleteObject acTable, Tables(i,0) 'if so, deletes it
Else
'do nothing
End If
DoCmd.OpenQuery Queries(i,0), acViewNormal 'rerun the query to
create new, updated table
Me.bonus_progress.value = Me.bonus_progress.value + 25
'increment the progress bar
Loop
'===============================================

Me.bonus_progress.Visible = False 'deactivate the progress bar

End Sub

Right after i set up the loop "Tables(i,0)" is what is highlighted.

Any ideas?
 
Krzysztof said:
Good Afternoon,

I have a question i cannot seem to answer.

I have 2 arrays and a loop that perform certain functions, and when i call
this sub, i get and error message stating: "Compile Error: byRef Argument
Type Mismatch".
I am not sure what that means, but i have tried replacing the array
identifier with a variable, but whatever i do, i get the same message.

Here is a copy of the code:

Here is my function
Public Function TableExists(strTableName As String) As Boolean
On Error Resume Next

TableExists = IsObject(CurrentDb.TableDefs(strTableName))

End Function

And here is the sub
Public Sub CaptureData()

Dim Tables(3, 0), Queries(3, 0) As String
Tables(0, 0) = "tbl_INVOICE"
Tables(1, 0) = "tbl_WOITEMS"
Tables(2, 0) = "tbl_WRKORDER"
Tables(3, 0) = "tbl_EMPLOYEE"
Queries(0, 0) = "mtqry_Invoice"
Queries(1, 0) = "mtqry_WOItems"
Queries(2, 0) = "mtqry_Wrkorder"
Queries(3, 0) = "mtqry_Employee"

'===============================================
'Create local tables
'Check to see if local table exists and if so, delete it
Me.bonus_progress.Visible = True 'activate the progress bar
Me.bonus_progress.value = 0 'reset the progress bar

Dim i As Integer
For i = 0 To 3
If TableExists(Tables(i,0)) = True Then 'check to see if table
exists
DoCmd.DeleteObject acTable, Tables(i,0) 'if so, deletes it
Else
'do nothing
End If
DoCmd.OpenQuery Queries(i,0), acViewNormal 'rerun the query to
create new, updated table
Me.bonus_progress.value = Me.bonus_progress.value + 25
'increment the progress bar
Loop
'===============================================

Me.bonus_progress.Visible = False 'deactivate the progress bar

End Sub

Right after i set up the loop "Tables(i,0)" is what is highlighted.

Any ideas?
Click to expand...


Ok, so i messed up on one thing, i replaced "Loop" with "Next i".
i still get the error though :(
 
Dim both your variables as string (Tables is dimmed as a Variant)
ie

Dim Tables(3, 0) As String, Queries(3, 0) As String

Personally I'd Use (matter of taste <g>):

Public Function TableExists(ByVal TableName As String) As Boolean
On Local Error Resume Next

Dim TDef As DAO.TableDef
Set TDef = Access.CurrentDb.TableDefs(TableName)
TableExists = Not (TDef Is Nothing)
Set TDef = Nothing
End Function

Also:
CurrentDb.Execute Queries(i,0), DAO.DbSeeChanges ' instead Of
DoCmd.OpenQuery

Pieter
 
Back
Top