Missing reference? 4

[ZOR]

A user gets this error

Function is not available in query expressions 'Val(Right([Barcode],9))'.

Is there a missing reference somewhere. Thanks

 

[boblarson]

Glad we could both help


Bob Larson
A2K,A2K3,A2K7,SQL Server 2000/2005,Crystal Reports 10/XI,VB6, WinXP, and Vista
Free Quick Tutorials and Samples:

http://www.btabdevelopment.com

 

[ZOR]

If it any help, I also found this TekTips thread

http://www.tek-tips.com/viewthread.cfm?qid=903662

Regarding the addrefs bit, anyone know how I would build a list to add;

Visual Basic for Applications
Microsoft Access 11.0 Library
OLE Automation
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.5 Library
Microsoft Comm Control 6.0

I can then shunt them in.

I know the DAO 3.6 is already shown in the list in the thread, but I want to make sure there all there. Many thanks

 

[ZOR]

I think I have rounded them up. Is there any danger having the function in a module which gets called each time the database is opened?

Function AddRefs()
Dim loRef As Access.Reference
Dim intCount As Integer
Dim intX As Integer
Dim blnBroke As Boolean
Dim strPath As String

On Error Resume Next

With Access.References
.AddFromFile "C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll"
.AddFromFile "C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\vbe6.dll"
.AddFromFile "C:\Program Files\Microsoft Office\Office\msacc9.olb"
.AddFromFile "C:\Program Files\Common Files\System\ado\msado15.dll"
.AddFromFile "C:\Program Files\Common Files\System\ado\msado25.tlb"
.AddFromFile "C:\Program Files\Common Files\System\ado\msadox.dll"
.AddFromFile "C:\WINNT\System32\stdole2.tlb"
.AddFromFile "C:\WINNT\System32\scrrun.dll"
.AddFromFile "C:\WINDOWS\system32\scrrun.dll"
.AddFromFile "C:\WINDOWS\system32\MSCOMM32.OCX"
.AddFromFile "C:\WINDOWS\system32\MSCAL.OCX"
End With

' Call a hidden SysCmd to automatically compile/save all modules.
Call SysCmd(504, 16483)
End Function

Regards

 

[boblarson]

The code I provided should work for any computer, but the code you are providing is at just as much risk of failing than just leavning it without any code. If a path for a particular computer is not applicable you will have the same problem as if you didn't even have any code.

You do not need to specify to the program WHERE the stuff is, all you need to do is refresh the references and Access will refresh based on wherever the actual file is. The only thing that won't work is if a reference is used for something that isn't actually on the computer.


Bob Larson
A2K,A2K3,A2K7,SQL Server 2000/2005,Crystal Reports 10/XI,VB6, WinXP, and Vista
Free Quick Tutorials and Samples:

http://www.btabdevelopment.com

 

[ZOR]

Thanks Bob. I found if all references were in the db, then the earlier code went through, however if I removed the DAO reference, then the module code would not run, and the macro halted.

What I am going to do is first check the users PC for library paths, and when I find those then will then hardcode them in. I thought then running this action each time would ensure they were always there. I wondered what side effect there may be running it each time. Regards