Current User and Current PC

In this post I thought I'd show two very useful functions which use Windows API calls, the username and PC name functions.  Windows API calls allow you to get information or features from the operating system which Excel and Access do not have.
The first step in using an API call is to tell VBA the function exists, where to find it, what arguments it takes and what data type it returns. This is done using the Declare statement, such as the example below.

Private Declare Function GetUserNameA _
  Lib "advapi32.dll" _
  (ByVal lpBuffer As String, nSize As Long) As Long

This statement tells the VBA interpreter that there is a function called GetUserNameA located in the file advapi32.dll that takes two arguments, the first a string and the second a Long value and returns a Long value. Once defined, we can call GetUserNameA in exactly the same way as if it is the VBA function:

  lng = GetUserNameA(strUserName, intLength)

The Function below uses the GetUserNameA API function and turns the results into a string which shows the current users Windows login.

Public Function CurrentUser() As String
'-- used to get current user's login id --
    Dim strUserName As String
    Dim lngLength As Long
    Dim lngResult As Long
    ' Set up the buffer
    strUserName = VBA.String(255, 0) 
    lngLength = 255
    ' Make the call 
    lngResult = GetUserNameA(strUserName,  lngLength)
    ' Assign the value
    CurrentUser = Left(strUserName, _
        InStr(1, strUserName, VBA.Chr(0)) - 1)
End Function

You can add this this code to any normal VBA module within either Excel or Access.  The API ‘Private Declare Function’ should be placed at the top of the module, before any other Sub Routines or Functions.
The current PC function, below, works in much the same way.

Private Declare Function GetComputerNameA Lib "kernel32" _
    (ByVal lpBuffer As String, nSize As Long) As Long

Public Function CurrentPC() As String
'-- used to get current user's login id --
    Dim strPCName As String
    Dim lngLength As Long
    Dim lngResult As Long
    ' Set up the buffer
    strPCName = VBA.String(255, 0)
    lngLength = 255
    ' Make the call
    lngResult = GetComputerNameA(strPCName, lngLength)
    ' Assign the value
    CurrentPC = Left(strPCName, _
        InStr(1, strPCName, VBA.Chr(0)) - 1)
End Function

Google / Yahoo as always will be able to provide more information on using API calls, this site is also probably worth a read.
I’ll add a sorry about the colour scheme, guess that’s what happens when you choose black as a background.

Access and conditional formatting reports

This isn't the important, in-depth blog post I'd hoped to start with, but is a subject which has come up in discussions a couple of times recently, so thought it worth posting.
Access 2007 allows you to apply conditional formatting to reports.  This means you can highlight one field, either because of it's value, or the values within in other fields.  You do this by right clicking the relevant field and selecting ‘Conditional Formatting’.
The next step is to choose which field(s) you want the formatting to rely upon.  To do this choose either ‘Field Value Is’ or ‘Expression Is’.  The first choice makes the conditional format dependant on the selected field, the second allows you to enter a formula which can then look at other fields.
In the example that follows I have chosen to highlight the ‘Product’ field where the ‘Quantity’ is higher then 2 using ‘Expression Is’.

When using IF statements, Access, unlike Excel wants to see IIF.

And now for the reason I’m posting this.  When choosing to change the back colour you need to ensure that the control’s (Label, TextBox etc) back style is set to ‘Normal’.  When creating a report the default back style is ‘Transparent’, this means the back colour format is not shown. 

Below are two pics, the first where the control’s back style is ‘Transparent’.
And the  second after changing the back style to ‘Normal.

Well, I hope that proves useful to someone, if you have any comments or questions let us know.

Brand new blog.

Hi and welcome to the brand new blog from the guys at Zypher.co.uk.  
This blog will predominantly be about automating Microsoft Office and building MS Office tools and applications.  The occasional post may well cover over areas of ICT (Information Communication Technology) including, but not limited to our ICT care package for home and business - ICT support, Network design, management and upgrades, Database development, Website development and ICT Training.

We'll be putting our first few articles up over the coming days so hope you visit us again soon.

The team at Zypher.


You can also visit our site by clicking here.