Run-time Error 5 "Invalid Procedure Call or Argument" (1 Viewer)

cursedeye

Registered User.
Local time
Today, 10:01
Joined
Oct 7, 2009
Messages
50
It runs perfectly on my laptop and my desktop, but always gives run-time error 5 on 2 of my co-workers computers.
This is where it stops.

If strfilter <> strOldFilter Then
Private Sub CheckFilter()
Dim strfilter As String
Dim strOldFilter As String

strOldFilter = Me.Filter


'Building - number
If Me!Building > "" Then _
strfilter = strfilter & _
" AND ([Building.Building ID]=" & _
Me!Building & ")"

'Location - Numeric
If Me!Location > "" Then _
strfilter = strfilter & _
" AND ([Location.location ID]=" & _
Me!Location & ")"
'Debug.Print ".Filter = '" & strOldFilter & "' - ";
'Debug.Print "strFilter = '" & strFilter & " '"
'Tidy up results and apply IF NECESSARY
If strfilter > "" Then strfilter = Mid(strfilter, 6)
If strfilter <> strOldFilter Then
Me.Filter = strfilter
Me.FilterOn = (strfilter > "")
End If
End Sub
Can anyone help please? Thanks!

I have checked reference on all computers made sure they have the same order.

just checked again, we have the same versions of Access (2003 SP3), Visual Basic(6.5) and same OS Windows XP Pro SP3
 
Last edited:

cursedeye

Registered User.
Local time
Today, 10:01
Joined
Oct 7, 2009
Messages
50
sadly I can't even debug this thing on my own computers since they don't get this error. =(
 

vbaInet

AWF VIP
Local time
Today, 13:01
Joined
Jan 22, 2010
Messages
26,374
Here's your code tidied up:
Code:
Private Sub CheckFilter()
    Dim strfilter As String
    Dim strOldFilter As String
    
    strfilter = vbNullString
    strOldFilter = Me.Filter & vbNullString
    
    'Building - number
    If Len(Me!Building & vbNullString) <> 0 Then
        strfilter = "[Building.Building ID] = " & Me!Building
    End If

    'Location - Numeric
    If Len(Me!Location & vbNullString) <> 0 Then
        If Len(strfilter) <> 0 Then
            strfilter = strfilter & " AND [Location.location ID] = " & Me!Location
        Else
            strfilter = "[Location.location ID] = " & Me!Location
        End If
    End If
    
    If strfilter <> strOldFilter Then
        Me.Filter = strfilter
        Me.FilterOn = (Len(strfilter) <> 0)
    End If
End Sub
 

cursedeye

Registered User.
Local time
Today, 10:01
Joined
Oct 7, 2009
Messages
50
Hi
Thank you! I tested it. It works fine on mine(as always)...but it still gives the same error at the same line on my co-workers computers!

This is killing me. sad face...
 

boblarson

Smeghead
Local time
Today, 06:01
Joined
Jan 12, 2001
Messages
32,059
1. Is this database split - backend with tables only and a COPY of the frontend on each user's machine?

2. If not, it needs to be.

3. Open the database on the user that is having the problem (you'll have to do this for each) and go to the VBA Window.

4. Go to TOOLS > REFERENCES and see if any are marked MISSING in the list. If there are, uncheck them and close the dialog and then reopen the database. If there aren't, try unchecking the DAO 3.x (where x is 51 or 6) and close the dialog and then reopen the dialog and recheck it. If you don't have a DAO reference checked and only have an ADO reference checked, do the same thing on it.

5. Then see if it will work.
 

hk1

Registered User.
Local time
Today, 07:01
Joined
Sep 1, 2009
Messages
121
Does your code compile without a problem? I suspect not.

Debug > Compile
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 13:01
Joined
Sep 12, 2006
Messages
15,613
it might be that one of the filters is null - A2007 seems less forgiving of nulls than A2003 eg

so try

if nz(strfilter,"")<>nz(stroldfilter,"") then


or better still
strfilter = nz(strfilter,"")
stroldfilter=nz(stroldfilter,"")

etc
 

vbaInet

AWF VIP
Local time
Today, 13:01
Joined
Jan 22, 2010
Messages
26,374
I doubt this would be the case because strFilter is declared as String. So it would have bomed with a Null error.

Also, the revised code ensures that Null assignment never occurs using the Len() check prior to assignment.
 

cursedeye

Registered User.
Local time
Today, 10:01
Joined
Oct 7, 2009
Messages
50
I want to thank you first!:) but the problem is not going away!

1. Is this database split - backend with tables only and a COPY of the
frontend on each user's machine?

2. If not, it needs to be.

Yes. We use access as frontend, SQL backend. The access file is located on a network drive where I save the latest version.
My co-workers copy the latest version to their computers, which means that
everyone use EXACTLY the same thing.


3. Open the database on the user that is having the problem (you'll have to do this for each) and go to the VBA Window.

4. Go to TOOLS > REFERENCES and see if any are marked MISSING in the list. If there are, uncheck them and
close the dialog and then reopen the database. If there aren't, try unchecking the DAO 3.x (where x is 51 or 6)
and close the dialog and then reopen the dialog and recheck it.
If you don't have a DAO reference checked and only have an ADO reference checked, do the same thing on it.

As I stated at the beginning I checked the reference on all the computers, there was nothing tagged "MISSING",
so unchecked everything and rechecked them again.

I followed your instruction: unchecked DAO3.6 and close the whole program restart it,
got an error when it's opening because of the missing DAO, then I rechecked DAO.
Sadly, The runtime error 5 is still there at the same damn line!:confused:


5. Then see if it will work.
It doesn't. Isn't it the strangest thing ever!?
 
Last edited by a moderator:

vbaInet

AWF VIP
Local time
Today, 13:01
Joined
Jan 22, 2010
Messages
26,374
I'm beginning to suspect your problem is not within that function but from a function that is calling that function. Step through your code from the entire process.
 

vbaInet

AWF VIP
Local time
Today, 13:01
Joined
Jan 22, 2010
Messages
26,374
This is what I'm using to call that function.
Try calling the code in a command button instead and rename your controls so that they aren't the same names as the fields. So the Location textbox should be called txtLocation.
 

cursedeye

Registered User.
Local time
Today, 10:01
Joined
Oct 7, 2009
Messages
50
it might be that one of the filters is null - A2007 seems less forgiving of nulls than A2003 eg

so try

if nz(strfilter,"")<>nz(stroldfilter,"") then


or better still
strfilter = nz(strfilter,"")
stroldfilter=nz(stroldfilter,"")

etc

the problem occurs when they both are not empty

 

cursedeye

Registered User.
Local time
Today, 10:01
Joined
Oct 7, 2009
Messages
50
Try calling the code in a command button instead and rename your controls so that they aren't the same names as the fields. So the Location textbox should be called txtLocation.

I know I don't have very standardized filed names, but I guess that shouldn't be the cause, should it? It works perfectly on 3 other computers.:confused:
 

boblarson

Smeghead
Local time
Today, 06:01
Joined
Jan 12, 2001
Messages
32,059
It works perfectly on 3 other computers.:confused:
Which is why it appears to either be a reference problem OR perhaps a bad Access installation. But this is the only database that the 2 other computers has a problem with?
 

boblarson

Smeghead
Local time
Today, 06:01
Joined
Jan 12, 2001
Messages
32,059
We use access as frontend, SQL backend.
Also, one other thing that may have to do with it. Does each user have a DSN set up on their computer? If so, is it exactly the same as the others (did it get set up with the same ODBC driver?)? Which version of SQL Server are you connecting to and which ODBC driver are you using?
 

vbaInet

AWF VIP
Local time
Today, 13:01
Joined
Jan 22, 2010
Messages
26,374
It doesn't seem to like string comparisons using <>. Try this too:
Code:
If Len(strFilter) <> 0 And Nz(strcomp(strfilter, strOldFilter), 0) <> 0 Then
Which could mean a reference problem like Bob mentioned.
 

boblarson

Smeghead
Local time
Today, 06:01
Joined
Jan 12, 2001
Messages
32,059
It doesn't seem to like string comparisons using <>. Try this too:
Code:
If Len(strFilter) <> 0 And Nz(strcomp(strfilter, strOldFilter), 0) <> 0 Then

That comparison would be meaningless really, unfortunately. Because the desire is to check to see if the actual string matches or not, not the length. You could have two filter strings 20 characters in length and be completely different. :D

Unless you meant just for testing purposes for this error. :)
 

vbaInet

AWF VIP
Local time
Today, 13:01
Joined
Jan 22, 2010
Messages
26,374
It's in two parts - the first part ensures that a new filter has been set and the second part ensures that there's no match.
 

cursedeye

Registered User.
Local time
Today, 10:01
Joined
Oct 7, 2009
Messages
50
Also, one other thing that may have to do with it. Does each user have a DSN set up on their computer? If so, is it exactly the same as the others (did it get set up with the same ODBC driver?)? Which version of SQL Server are you connecting to and which ODBC driver are you using?

SQL server 2008 R2
It is native drive all have the same setup.
 

Users who are viewing this thread

Top Bottom