VBA Error is gone After did "Compact and Repair Database"

[Sam Yi]

All,

Can any one help me explain the following situation:
When I try to set the RecordSource to current Form
Me.RecordSource = "select * from filestodownload where clientname in " & _
"(select distinct clientname from v_CapStockFundInformation where
SSCFundNumber in (" & vFundAccess & ") and live=1)"
It will raise error with description: the record source "...." specified on
this form or report doesnt exist.

And after I 'compact and repair database', the error is gone.

The database is SQL Server, and use ODBC connection

Thanks for any comment!

 

[Sam Yi]

All,

Can any one help me explain the following situation:
When I try to set the RecordSource to current Form
Me.RecordSource = "select * from filestodownload where clientname in " & _
"(select distinct clientname from v_CapStockFundInformation where
SSCFundNumber in (" & vFundAccess & ") and live=1)"
It will raise error with description: the record source "...." specified on
this form or report doesnt exist.

And after I 'compact and repair database', the error is gone.

The database is SQL Server, and use ODBC connection

Thanks for any comment!

Later I digged more deep and found the root error is raise at following
comparing point
If tepName = strTableName Then
Exit Function
End If
tepName and strTableName are both String type, and the err description is
'invalid procedure call or argument'

Any idea for this strange thing?

THanks a ton!

 

[Tom van Stiphout]

On Wed, 19 Aug 2009 02:39:01 -0700, Sam Yi

Do you have "Option Explicit" at the top of this module? If not it may
be a simple typo. Add it and compile the application.

-Tom.
Microsoft Access MVP

 

[Sam Yi]

And invalid procedure call or argument error will go after I did "compact and
repair databasse"

The problem still there, any suggestion?

 

[AccessVandal via AccessMonster.com]

No, not the compact and repair. Go to your VBA editor and on the Debug -
Compile "YourDataBaseName"

And invalid procedure call or argument error will go after I did "compact and
repair databasse"

The problem still there, any suggestion?

Do you have "Option Explicit" at the top of this module? If not it may
be a simple typo. Add it and compile the application.

[quoted text clipped - 31 lines]

 

[Sam Yi]

As you suggested, I have compiled and fixed all compile error, but the
problem still there. And it seems have sth to do with the access version env.
For instance:
DEV_1: has access 2002 and 2003 installed
DEV_2: only have access 2003 installed
user's machine: has 2003 and 2000 installed
MDB source is developed base on 2000 version

User cant open the mdb application compacted by DEV_1 with err 'invalid
procedure call or argument' on code "If tepName = strTableName Then"
And the mdb compacted by DEV_2 can be open by user.

I am close to crazy by Access's version caused strange thing.

Thanks a lot!

No, not the compact and repair. Go to your VBA editor and on the Debug -
Compile "YourDataBaseName"

And invalid procedure call or argument error will go after I did "compact and
repair databasse"

The problem still there, any suggestion?

Do you have "Option Explicit" at the top of this module? If not it may
be a simple typo. Add it and compile the application.

[quoted text clipped - 31 lines]

THanks a ton!

 

[AccessVandal via AccessMonster.com]

I can’t say much about the dual install of different version conflicts in
Access as it depends on how you install them. Yes, they might cause problem,
as some of the code in 2003 will be recognize by 2000.

'invalid procedure call or argument' Do you have a name conflict with a same
name function or subroutine written somewhere in the form’s module or in the
standard module?
Just making a guess here.

 

[Sam Yi]

I think it's not the duplicated name related, as I have tried to put '
"a"="a" 'at the error occurring line 'If tepName = strTableName Then ', it
still has problem.

Thank you very much!

 

[AccessVandal via AccessMonster.com]

No, I mean you might have a variable in the code that might also having the
same name with a Function/Subroutine name.

Is "tepName" being used somewhere? I think the error to this.