(Note many code examples here are in Access 2 format some syntax adjustment may be required for Access 95 or 97)
Table Topics | Queries and Recordsets
FormTopics | Report Topics| General Access Topics
![]() | Pausing Code to Wait Until a Shelled Process Is Finished in Access 2 |
If you need to shell to another program from Access, such as a zipping program, a communications or mail program, you'll often want to stop your code while the shelled process operates and then resume your code once the process is finished. However, once Access starts the other program, or process, it will continue on its merry way running your function unless you build in explicit code which causes your function to wait until the external program finishes. Here's an example of how to do this in Access 2 using the Win api:
1. Paste the following declaration into your module: Declare Function GetModuleUsage% Lib "Kernel" (ByVal hModule%)2. Try out this test function, which launches any app you want to and waits until it is finished to display a message box (Note an " _ " underscore means line continuation): Function LaunchApp (MYAppname As String) as Integer Dim ModuleHandle As Integer LaunchApp=-1 ModuleHandle = Shell(appname, 1) If (Abs(hMod) > 32) Then While (GetModuleUsage(hMod)) DoEvents Wend Else MsgBox "ERROR : Unable to start " & MyAppname LaunchApp=0 End If MsgBox "This code waited to execute until " _ & MyAppName & " Finished",64 End Function |
![]() |
Pausing Code to Wait Until a Shelled Process Is Finished in Access 7 & 8 |
If you need to shell to another program from Access, such as a zipping program, a communications or mail program, you'll often want to stop your code while the shelled process operates and then resume your code once the process is finished. However, once Access starts the other program, or process, it will continue on its merry way running your function unless you build in explicit code which causes your function to wait until the external program finishes. Unlike using Access 2, the Win32 api doesn't have the function "GetModuleUsage" to measure whether the shelled process is still running. Instead in Win32, you must use the function "WaitforSingleObject", and "OpenProcess". Below we've provided a sample function to launch an application and wait for it to finish.
1. On the declarations page of your module, add the following functions: Private Declare Function OpenProcess Lib "kernel32.dll" (ByVal _ dwAccess As Long, ByVal fInherit As Integer, ByVal hObject _ As Long) As Long Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal _ hHandle As Long, ByVal dwMilliseconds As Long) As Long Private Declare Function CloseHandle Lib "kernel32" (ByVal _ hObject As Long) As Long 2. Try out this test function, which launches any app you want to and waits until it is finished to display a message box (Note an " _ " underscore means line continuation): Function LaunchApp32 (MYAppname As String) As Integer On Error Resume Next Const SYNCHRONIZE = 1048576 Const INFINITE = -1& Dim ProcessID& Dim ProcessHandle& Dim Ret& LaunchApp32=-1 ProcessID = Shell(MyAppName, vbNormalFocus) If ProcessID<>0 then ProcessHandle = OpenProcess(SYNCHRONIZE, True, ProcessID&) Ret = WaitForSingleObject(ProcessHandle, INFINITE) Ret = CloseHandle(ProcessHandle) MsgBox "This code waited to execute until " _ & MyAppName & " Finished",64 Else MsgBox "ERROR : Unable to start " & MyAppname LaunchApp32=0 End If End Function 3. It is important to note that your function must include the code to close the process handle after the shelled application is complete, otherwise you will have a memory leak until you shut down Windows. |
![]() |
Presenting a List of Directories to a User in Access 7 & 8 using the Windows Shell |
In Access 7 and 8 when run under Windows 95 or Windows NT 4, you can provide Users with a simple Directory dialog rather than using the standard File Open or File Save As dialogs from the common dialog suite which shows both files and directories. To do this you use the Directory dialog built into the Shell OLE container. Here's the code to do it:
In the declarations page of a module, add the following declares (an "_" means line continuation): Type shellBrowseInfo hWndOwner As Long pIDLRoot As Long pszDisplayName As Long lpszTitle As String ulFlags As Long lpfnCallback As Long lParam As Long iImage As Long End Type Const BIF_RETURNONLYFSDIRS = 1 Const MAX_PATH = 260 Declare Sub CoTaskMemFree Lib "ole32.dll" (ByVal hMem As Long) Declare Function SHBrowseForFolder Lib "shell32" (lpbi As shellBrowseInfo) As Long Declare Function SHGetPathFromIDList Lib "shell32" (ByVal pidList As Long, _ ByVal lpBuffer As String) As Long Then use the following function, supplying it the title you want to use for the dialog, and the handle of the calling form. (use the Me.hwnd property of the form): Public Function GetFolder(dlgTitle As String, Frmhwnd as Long) As String Dim Nullchr As Integer Dim IDList As Long Dim Result As Long Dim Folder As String Dim BI As shellBrowseInfo With BI .hWndOwner = Frmhwnd .lpszTitle = dlgTitle .ulFlags = BIF_RETURNONLYFSDIRS End With IDList = SHBrowseForFolder(BI) If IDList Then Folder = String$(MAX_PATH, 0) Result = SHGetPathFromIDList(IDList, Folder) Call CoTaskMemFree(IDList) 'this frees the ole pointer to IDlist Nullchr = InStr(Folder, vbNullChar) If Nullchr Then Folder = Left$(Folder, Nullchr - 1) End If End If GetFolder = Folder End FunctionThis function will return the path to the folder selected, so long as it is not a system folder such as the printers folder. |
![]() ![]() | How to Get the Name of the Currently Open Database. |
To get the directory path of the currently open database in any version of Access, get the Name property of the database in VBA. Here's the simple code to do it:
Function GetDbPath() As String Dim MyDb as Database Set MyDb = CurrentDB() GetDbPath = MyDb.Name |
![]() | How to Get the Network Log In Name and Computer Name of the Currently Logged In User in Access 95 & 97 |
Two easy api calls can provide you with network log in name of the current user of the workstation and the network name of the computer itself for use in your Access application. Declare the following functions in your module and add the following function:
Private Declare Function api_GetUserName Lib "advapi32.dll" Alias _ "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long Private Declare Function api_GetComputerName Lib "Kernel32" Alias _ "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long Public Function CNames(UserOrComputer As Byte) As String 'UserorComputer; 1=User, anything else = computer Dim NBuffer As String Dim Buffsize As Long Dim Wok As Long Buffsize = 256 NBuffer = Space$(Buffsize) If UOrC = 1 Then Wok = api_GetUserName(NBuffer, Buffsize) CNames = Trim$(NBuffer) Else Wok = api_GetComputerName(NBuffer, Buffsize) CNames = Trim$(NBuffer) End If End FunctionOur System Information sample mdb in the Free file library contains this an other useful system api functions. |
![]() ![]() | How to Get the Network Log In Name of the Currently Logged In User on a Novell Network |
If you need to get the network log in name of the workstation user on a Novell Network, the easiest way to do so is to set an environment variable in the Novell log in script and then call that variable using Access' "Environ" function. Here how to do it:
First add an environment variable to your Novell Log In script which captures the User Name as in: DOS SET USERNAME = LOGIN_NAME Then add the following function to a module:
Function GetNetworkUser() as String GetNetworkUser = Environ("USERNAME") End FunctionWhile setting an environment variable is relatively easy to gather this information, it also exposes certain security holes. A more complete and secure approach is to use the Novell API's to gather this information. An extensive sample Access 2 database, contributed by Doug Steele is available in the ACG Free Files library which provides a complete example of using the Novell API's to get the user name, group of the user, servers available etc. |
![]() ![]() | How to Create your own Input Box forms and pause code for User input. |
In your application you may need to get information or a selection from a user that an a standard Input Box isn't designed to handle.
As an example, you may want to offer the user only a selection of one of two choices. Since there's no way to restrict what the user can enter into an input box, an input box won't work well. To get around this limitation you can design a form that acts as psudo input box, opening the form to get input, and then when closed, resuming your code.
Here's how:
DoCmd OpenForm "MyForm", A_Normal While SysCmd(SysCmd_GETOBJECTSTATE, A_FORM, _ "MyForm Name") = OBJSTATE_OPEN DoEvents 'Do Nothing Wait for Closing Wend [Resume Code here] |
![]() ![]() | Code to Return the Last Day of the Month, the Beginning of a Quarter or End of a Quarter Based on a Specific Date |
Access' Date Add and DateDiff functions generally fill most needs for date manipulation. However, there are a few instances where you need to return a date for comparison purposes which are not simple additive date result from a current date. Two examples are specifying the date for the end of the current month (e.g. for billing purposes), or gathering data which occurs, or is scheduled to occur based on whether it is after the beginning of a quarter, or before the end of a quarter. The following two functions provide the code to determine the end of the month from a specific date and the first or last day of any yearly quarter based on a specific date. (The second function below, providing the quarterly dates relys upon the end of the month function so both must be included in your module.) Here's the end of the month function:
Function EOMonth (Anydate) '------------------------------------------------------------------------------- 'Purpose: Returns the last day of the month for the date specified 'Accespts: A Date or Date Variable. 'Returns: VarType 7 Date '------------------------------------------------------------------------------ On Error GoTo Err_EOM Dim NextMonth, EndofMonth NextMonth = DateAdd("m", 1, Anydate) EndofMonth = NextMonth - DatePart("d", NextMonth) EOMonth = EndofMonth Exit_EOM: Exit Function Err_EOM: MsgBox "Error" & " " & Err & " " & Error$ Resume Exit_EOM End FunctionHere's the Function to determine the beginning date or ending date of any quarter base on a date supplied: Function BEQuarter (ByVal Anydate, BeginOrEnd As Integer) As Variant '------------------------------------------------------------------- 'Purpose: Returns the beginning or the end of a quarter 'Uses: EOMonth() Function 'Input: AnyDate: A date value, use of #'s to signify a date when variable ' from a query or the immediate window. '' BeginOrEnd: 0 Finds Beginning of Quarter, -1 Finds End of Quarter ''Returns: VarType 7 date '--------------------------------------------------------------------- On Error GoTo Err_EOQ If BeginOrEnd <> 0 And BeginOrEnd <> -1 Then MsgBox "Error: BeginOrEnd must be 0 or -1" GoTo Exit_EOQ End If Dim EndofQuarter, BeginofQuarter, PrevQuarter Static MonthVar(12) As Integer If MonthVar(12) = 0 Then MonthVar(1) = 2 MonthVar(2) = 1 MonthVar(3) = 3 MonthVar(4) = 2 MonthVar(5) = 1 MonthVar(6) = 3 MonthVar(7) = 2 MonthVar(8) = 1 MonthVar(9) = 3 MonthVar(10) = 2 MonthVar(11) = 1 MonthVar(12) = 3 End If Anydate = Anydate - DatePart("d", Anydate) EndofQuarter = DateAdd("M", MonthVar(DatePart("M", Anydate)), Anydate) EndofQuarter = EOMonth(EndofQuarter) If DatePart("m", EndofQuarter) = 6 Then BeginofQuarter = DateAdd("q", -1, EndofQuarter) + 2 Else BeginofQuarter = DateAdd("q", -1, EndofQuarter) + 1 End If If BeginOrEnd = -1 Then BEQuarter = EndofQuarter Else BEQuarter = BeginofQuarter End If Exit_EOQ: Exit Function Err_EOQ: MsgBox "Error" & " " & Err & " " & Error$ Resume Exit_EOQ End Function |
![]() ![]() | How to Test Whether an Instance of the Application is Already Running when the Application is Launched |
Users being users may at times forget that they have a copy of the application running (hidden tool bar in Win 95/NT or a Maximized Program Manager in Win 3.1x) when they launch a second copy from the desk top. To prevent the second instance from loading, you can run code from your autoexec macro to test whether the app is already running and terminate the launch if a copy of it is already open.
To do this, you will need to incorporate the two following simple functions in your database and call the Function IsRunning below: Function IsRunning() as integer Dim db As Database Set db = CurrentDB() If TestDDELink(db.Name) Then IsRunning = -1 Else IsRunning =0 End If End Function ' Helper Function Function TestDDELink (ByVal strAppName$) As Integer Dim varDDEChannel On Error Resume Next Application.SetOption ("Ignore DDE Requests"), True varDDEChannel = DDEInitiate("MSAccess", strAppName) ' When the app isn't already running this will error If Err Then TestDDELink = False Else TestDDELink = True DDETerminate varDDEChannel DDETerminateAll End If Application.SetOption ("Ignore DDE Requests"), False End Function This code works in all versions of Access. |
![]() | Add pizzaz to your Message Boxes in Access 95 and 97 by varying the font weight. |
Developer's since Access 1.x have used the combination of Chr(10) & Chr(13) & Chr(10) to break message boxes into paragraphs.
In Access 95 and 97 you could use the intrinsic constants "vbCrlf" and "vbCr" to do the same thing.
There is a new feature of message boxes in Access 95 and 97 which allows you to not only break your message box into paragraphs, so that the action item is quickly identified, but also allows you to bold the first paragraph in the Message Box, just like Access itself does. The "@" symbol added to your message text will break the message into paragraphs, with the first paragraph in bold. You are limited to two "@" symbols (three paragraphs,) and the "@" symbol should follow each of the first two paragraphs. What this also allows you to do is that if you want your message box to have only bold text (a single paragraph), then add two "@" symbols at the end of the text separated by a space (e.g. @ @). So if you had a Message Box statement like the following (note the " _ " means the line continues, remove when pasted into your app because the message box function can not span mutiple lines): If MsgBox("You have just deleted the current record.@ _ Click ""OK"" to confirm your delete or ""Cancel"" to undo your deletion.@ ", _ vbOKCancel, "My AppName") = vbOK Then 'Do somthing here End IfThe message box would bold the first paragraph "You have just deleted the current record" and then start a new non-bold paragraph for the action statement "Click OK to confirm your delete or Cancel to undo your deletion."
|
![]() |
How to Hide the Main Access Window when Running a Form or Report Shortcut from the Desktop |
If you have placed a short cut to an Access form or report on the Windows desktop, you may not want the main Access window to be displayed when your form or report is opened. There is no way to hide the window entirely if a form or report is launched from the desktop, Access will flash on the screen momentarily; but you can quickly hide using the following code and steps:
Const SW_HIDE = 0 Private Declare Function ShowWindow Lib "user32" (ByVal hwnd As Long, _ ByVal nCmdShow as Long) As Long Dim dwReturn as Long dwReturn = ShowWindow(Application.hwndAccessApp, SW_HIDE) Application.QuitThis last piece of code, to quit the application, is required to be added to what ever form will last be visible, be it a switch board form, single form or just a report. If you don't add this code, the form or report will be closed, but Access will still be running in the background, hidden from view. This tip submitted by Larry Christopher |
![]() ![]() |
How Do I Open My Application's Help File to the Contents or to a Specific Item in the Index/Search Section |
Often you may want to take specific actions when opening the help file for your application. Access provides the ability to open the help file to a specific topic. But if you want to make sure that when you open the file it opens the search index on a specific topic or
that it opens to the Contents (under Win95/NT) then you need to use the WinHelp api function. Here's how to do it:
To open to a specific help search/index topic call your help file as follows, where "strHelpVal" is the name of an index topic: Private Declare Function WinHelp Lib "user32" Alias _ "WinHelpA" (ByVal hWnd As Long, ByVal lpHelpFile As String, _ ByVal wCommand As Long, ByVal dwData As Any) As Long Const HELP_PARTIALKEY = &H105& Public Function WHPartialKey() Dim dwReturn% Dim strHelpVal$ strHelpVal = "Active Window" & Chr$(0) 'vbnullstring in A 95 and 97 dwreturn = WinHelp(Application.hWndAccessApp, "acmain80.hlp", _ HELP_PARTIALKEY, strHelpVal) End FunctionTo open the help file to the general contents use the following (note if the user last had the index showing this will open the file on the index tab, if the contents were showing it will open the contents): Private Sub cmdHelp_Click() On Error Resume Next Dim dwReturn& dwReturn= WinHelp(Me.hwnd, "acmain80.hlp", &HB, 0&) End Sub |
![]() ![]() | How Can I Check If a Network Drive Is Available? |
Your application may need to access a file on a network drive or attach to tables for a backend database on a network drive. Prior to actually
performing that action you may want to check to see if the network connection is available. Here's a function to do just that (Note this is in Win32 format, Win16 apis for Access 2 work identically
but you have to change the api declarations as noted below):
Private Declare Function prn_WNetGetConnection Lib "mpr.dll" _ Alias "WNetGetConnectionA" (ByVal LocalName$, ByVal RemoteName$, _ cbRemoteName&) As Long Private Declare Function prn_WNetAddConnection Lib "mpr.dll" Alias _ "WNetAddConnectionA" (ByVal NetPath$, Password, LocalName&) As Long Const ERROR_NO_ERROR = 0 Const ERROR_ACCESS_DENIED = 5 Const ERROR_BAD_NET_NAME = 67 Const ERROR_ALREADY_ASSIGNED = 85 Const ERROR_INVALID_PASSWORD = 86 Const ERROR_MORE_DATA = 234 Const ERROR_INVALID_ADDRESS = 487 Const ERROR_BAD_DEVICE = 1200 Const ERROR_CONNECTION_UNAVAIL = 1201 Const ERROR_DEVICE_ALREADY_REMEMBERED = 1202 Const ERROR_NO_NET_OR_BAD_PATH = 1203 Const ERROR_NO_NETWORK = 1222 Const ERROR_NOT_CONNECTED = 2250 Public Function at_CheckNet%(DriveOrPrinter$) '------------------------------------------------------------ 'Purpose: To check to see if a drive or printer on the network is available 'Accepts: Drive as "C:","LPT3:" or "\\network_server\drive" 'Returns: True (-1) on Success, False (0) on Failure '------------------------------------------------------------- On Error GoTo Err_CN Dim dwError& Dim RemoteNamesz& Dim RemoteName$ at_CheckNet = True If Instr(DriveOrPrinter, "\\") < 1 Then 'local named resource RemoteName = String(255, 0) RemoteNamesz = Len(RemoteName) dwError = prn_WNetGetConnection(DriveorPrinter, RemoteName, RemoteNamesz) If dwError = ERROR_CONNECTION_UNAVAIL or _ dwError = ERROR_NOT_CONNECTED Or _ dwError = ERROR_NO_NETWORK Then at_CheckNet = 0 GoTo NetMsg End If Else 'a network address is supplied to the function 'we supply a null password, which may be required & a null connection name 'since we're not actually connecting, just checking the connection 'will return ERROR_DEVICE_AREADY_REMEMBERED if available dwError = prn_WNetAddConnection(DriveOrPrinter, Null, 0&) If dwError = ERROR_NO_NETWORK Or _ dwError = ERROR_NOT_CONNECTED Or _ dwError = ERROR_CONNECTION_UNAVAIL Or_ dwError = ERROR_NO_NET_OR_BAD_PATH Or _ dwError = ERROR_ACCESS_DENIED Or _ dwError = ERROR_BAD_NET_NAME Then at_CheckNet = 0 GoTo NetMsg End If End If GoTo Exit_CN NetMsg: MsgBox "The required network device is not currently available.", 16, "Check Net" Exit_CN: Exit Function Err_CN: MsgBox "Error: " & Err & " " & Error$, 16, "Check Net" Resume Exit_CN End FunctionThe 16 bit Access 2 declarations are as follows for Windows 95/98, some of the consts also change (Note: the _ is a line continuation and it should be removed in Access 2): Const ERROR_NO_NET_OR_BAD_PATH = 2 Const ERROR_BAD_PASSWORD = 6 Const ERROR_ACCESS_DENIED = 7 Const ERROR_NOT_CONNECTED = 48 Const ERROR_BAD_NET_NAME = 50 Const ERROR_BAD_LOCAL_NAME = 51 Const ERROR_DEVICE_ALREADY_REMEMBERED =52 Declare Function WNetGetConnection _ Lib "User" (ByVal LocalName$, ByVal RemoteName$, _ cbRemoteName As Integer) As Integer Declare Function WNetAddConnection _ Lib "User" (ByVal netpath$, Password as Any, _ LocalName%) As IntegerNote: If you want to actually create a network connection using WNetAddConnection, change the last parameter call in the declarations to ByVal LocalName as String, rather than a long or integer, and pass your local drive or port connection like "c:". |
![]() ![]() | How to Save Memory When Writing VBA Code |
Access and VBA generally do a good job of "cleaning up" memory when they are done running your code. But there are a number of
common coding methods used by developers which hinder Access' release of memory. Here's some steps to take in writing functions to save memory (or how to become a good C++ programmer
without learning C++):
|