[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1120
  • Last Modified:

Get the current Computer name in Access VBA

In my program I need the current user name and computer name,  Out of the blue I am having computers that fail on my call for the Computer Name.  This is the error that it reports

The expression On Open you entered as the event property produced the following error:  
The form action was canceled the expression may not result in the name of a macro, the name of a user defined function or event Procedures
There may have been an error evaluating the function, event or macro

Computers on the same network work are running correctly, so I think I can rule out any network related issues.  To the best of my knowledge, no MS updates are causing it, even though I suspect it.  All the work stations run the same 2003 Access runtime.    

The code runs in my office and other computers correctly.  I will post the offending code, I have tried 2 different ways to collect the computer name through the function I posted and   Environ$("computername") both fail.
The computer names are not long or any odd characters.  The one I am dealing with is “Wkstn15”

This code has been working for 4+ years and in the last 6 weeks I am having this problem sporadically.  I am at a loss.

Thank you

3 Solutions
Well as you say it should work.  Try taking the code out of the function you are calling (getTheCurrEstNum1()) and putting it inline in the form_open event so you can see exactly what line within that function is failing.  If it does fail while using environ$("computername") you may want to run CMD and type SET<enter> to make sure the computername environment setting exists (which it should)
This should work for you without using Environ
Private Declare Function GetComputerNameA Lib "kernel32" (ByVal lpBuffer As String, nSize As Long) As Long

Public Function GetComputerName() As String
On Error GoTo Err_GetComputerName
Dim Username As String * 255
Call GetComputerNameA(Username, 255)
GetComputerName = Left$(Username, InStr(Username, Chr$(0)) - 1)
gCmpNm = Left(GetComputerName, 4)
'Forms.frmFindInvoices.txtComputerName = gCmpNm
    Exit Function

        MsgBox Err.Description
        Resume Exit_GetComputerName

End Function

Open in new window

Richard DanekeTrainerCommented:
Could the other workstations be running in 'sandbox' mode and not evaluating an expression outside of access?
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Try this code, which uses a different DLL.

Private Declare Function GetComputerNameA Lib "kernel32" (ByVal lpBuffer As String, nSize As Long) As Long  
Private Declare Function GetUserNameA Lib "advapi32.dll" (ByVal lpBuffer As String, nSize As Long) As Long  
Public Function WhoAmI(bReturnUserName As Boolean) As String  
        ' Function returns either user name or computer name  
        Dim strName As String * 255  
10      If bReturnUserName = True Then  
20        GetUserNameA strName, Len(strName)  
30      Else  
40        GetComputerNameA strName, Len(strName)  
50      End If  
60      WhoAmI = left$(strName, InStr(strName, vbNullChar) - 1)  
End Function 

Open in new window

mdlpAuthor Commented:
This is hard, because I got pulled away from my computer, and I did not get to answer you as the answers came in.
DoDahD, I have all my clients on Security Certificates, I am sorry for not making this clear, and actually run the sand box for security and virus issues.
KmsLogic, I did not think about the Env….$ pulling from the local environment, but that was not my issue, but it will be stored in memory for future issues.

Jmoss111 and Jdettman, It would appear that the declaration for the Kerne32 was where my problem was, and both of you used the same declaration, which fixed my problem, In fairness Jmoss111 posted first, But I believe the suggestion of a different Dll and the function by JDettman was very good, and if I could award you both full points I would, but to my knowledge, I have to split them.
Thank you again, mdlp

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now