Compiler error on a non-existent line

[JoeS01]

I have installed my fully operational Access 2010 data base from another computer. When I run it on my work laptop I get a "Compiler error. in Query Expression Round(Val(func..... ' etc.

The trouble is, this formula does not exist anywhere in my VBA code. I have used Ctrl F and selected the whole project in which to do the search.

I am using windows 7 Professional with 64 bit OS, and with VBA 7.0 on my work laptop.

Can anyone please suggest where the problem might be?

 

[SydneyGeek]

Have you searched the queries?
Also, look in the References to see if any are marked as MISSING. If you find one, deselect it then browse down the list until you find the latest available match, and select that. OK to get out and you should be up and running.

Denis

 

[JoeS01]

Thanks SydneyGeek. I have opened VBA, then did a Ctrl F to search for the relevant line given in the error message, without success. There are no missing References. All the References on the two computers are identical, as is the Access 2010 and VBA 7.

The only difference is that one is Win 7 Ultimate ( Access database works OK there), and Win 7 Professional ( errors there). Both are 64 bit.

Can you please advise how I search the Queries?

 

[JoeS01]

The other interesting error is that when I run the Excel workbook on my work computer to transfer data, it opens the Access database OK, but can't find the relevant Sub to transfer the data.

This doesn't happen on the home computer where the software was developed. The Sub is entered, data is transferred immediately to the Excel workbook, and then the sub closes.

I would welcome any and all suggestions on how to find the problem

 

[Derek Brown]

This may not be the cause of your particular problem but if there is an add-in or third-party software involved you may have problem if you installed the 64-bit version of Office.

Microsoft recommends the 32-bit version of Office on 64-bit machines for most people:

http://office.microsoft.com/en-us/w...-version-of-microsoft-office-HA010369476.aspx

 

[SydneyGeek]

I've used this to search queries:

Note -- Set a reference to DAO before running this code (Microsoft DAO 3.6 Object Library)

Function FindContentInQueries()
    Const STR_FIND = "NZ"
    Dim qdf As DAO.QueryDef
 
    For Each qdf In CurrentDb.QueryDefs
        If InStr(1, qdf.SQL, STR_FIND) > 0 Then
            Debug.Print qdf.Name & "; " & qdf.SQL
        End If
    Next qdf
End Function

Change the constant to the string you are trying to find.
Run the code, then check your Immediate window (Ctrl+G to display) for any query names and their corresponding SQL.

Denis

 

[JoeS01]

Thanks for the information from MS, Derek. That could be part of the problem. How do I tell if it is Offcie 64 bit or 32 bit? Both the C:\Program Files and C:\Program Files(x86) both contain MS Office Folders on the work computer.

Many thanks for the code to search the queries, Denis, much appreciated

 

[Derek Brown]

To find out the version etc. in Access, on the 'File' tab select 'Help' and you will see the 'About Access' details on the right.

 

[JoeS01]

Thanks Derek.

mmmmmm..... the problematic work computer uses 64 bit office, whereas my home computer uses 32 bit office - that is the computer I used to develop the software.

I am confident that is the problem, thanks to your advice and links, Derek.

 

[boblarson]

While this problem may have to do with 64bit vs 32bit, I just wanted to point out that if you download the free V-Tools, you can use its TOTAL DEEP SEARCH tool, which not only searches code but also tables, queries, forms, reports, (not sure about macros).

And for about US $40, you can get Rick Fisher's Find & Replace which is an awesomely good tool for searching items as it gets into properties and options and all of that too. I use it at work and would love to buy a copy for home as soon as I can afford the extra.