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

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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)President / OwnerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.