August 31st, 2011
So you merely would like to navigate to a given folder! Well, as usual, there are numerous ways to accomplish this.
Method 1 – FollowHyperlink Method
The first method is to use the FollowHyperlink Method. This is a great technique because it requires minimal coding and will open the folder in the user’s default software of choice. The code would look something like:
Application.FollowHyperlink "C:\Program Files\Microsoft Games" |
Method 2 – Shell Function
You can use the Shell Function to specify which program to use and pass it whatever variable that application accepts. So for our needs, we can merely use Windows explorer to open the folder we are interested in. The code would look something like:
Dim sPath as String
sPath = "C:\Program Files\Microsoft Games"
Shell "C:\WINDOWS\explorer.exe """ & sPath & "", vbNormalFocus |
In the case of the FollowHyperlink Method, you obviously have to ensure that your users’ actually have the program you are trying to utilize to open the folder and be careful with the exe location since it may change depending on OS versions, but you can easily build a more robust function to handle all these exceptions/cases.
Share and Enjoy
MS Access VBA Programming | 
No Comments »
August 19th, 2011
Another interesting question I was once asked on an Access forum was how can one retrieve a random record in a form?
I was actually perplexed as to how to approach this request, but it really isn’t that complicated at the end of the day. The code below demonstrates one possible method.
'---------------------------------------------------------------------------------------
' Procedure : GetRndRec
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Goto/retrieve a random record
' Copyright : The following may be altered and reused as you wish so long as the
' copyright notice is left unchanged (including Author, Website and
' Copyright). It may not be sold/resold or reposted on other sites (links
' back to this site are allowed).
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2008-Dec-21 Initial Release
'---------------------------------------------------------------------------------------
Function GetRndRec()
On Error GoTo Error_Handler
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim tblName As String 'Table to pull random record from
Dim iRecCount As Long 'Number of record in the table
Dim iRndRecNum As Integer
tblName = "YourTableName"
Set db = CurrentDb()
Set rs = db.OpenRecordset(tblName, dbOpenSnapshot, dbReadOnly, dbReadOnly)
If rs.RecordCount <> 0 Then 'ensure there are records in the table before proceeding
With rs
rs.MoveLast 'move to the end to ensure accurate recordcount value
iRecCount = rs.RecordCount
iRndRecNum = Int((iRecCount - 1 + 1) * Rnd + 1) 'Get Random Rec Number to use
rs.MoveFirst
.Move CLng(iRndRecNum)
GetRndRec = ![YourFieldName]
End With
End If
Resume Error_Handler_Exit
On Error Resume Next
'Cleanup
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: GetRndRec" & vbCrLf & _
"Error Description: " & Err.Description _
, vbOKOnly + vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function |
Share and Enjoy
MS Access Forms, MS Access Tables, MS Access VBA Programming | No Comments »
August 18th, 2011
This was the question put forth by someone on an Access Forum recently and I thought I’d share one possible way to determine this.
This is a brute force method, but it works! I simply loop through all the tables one by one and loop through all the fields within each table one by one. It is that simple. Here is the code.
'---------------------------------------------------------------------------------------
' Procedure : WhereFieldLocated
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Determine/Locate in which Table(s) a field is located
' Copyright : The following may be altered and reused as you wish so long as the
' copyright notice is left unchanged (including Author, Website and
' Copyright). It may not be sold/resold or reposted on other sites (links
' back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFieldName: The name of the field you are trying to locate
'
' Usage:
' ~~~~~~
' WhereFieldLocated "Filed1"
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2011-Aug-17 Initial Release
'---------------------------------------------------------------------------------------
Function WhereFieldLocated(sFieldName As String)
Dim db As DAO.Database
Dim td As DAO.TableDefs
Dim fld As Field
Set db = CurrentDb()
Set td = db.TableDefs
For Each t In td 'loop through all the tables in the database
If Left(t.Name, 4) = "MSys" Then GoTo Continue
For Each fld In t.Fields 'loop through all the fields of the table
If fld.Name = sFieldName Then
Debug.Print t.Name
End If
Next
Continue:
Next
Set td = Nothing
Set db = Nothing
End Function |
Share and Enjoy
MS Access Tables, MS Access VBA Programming | 1 Comment »
August 17th, 2011
I recently helped an individual in an Access Forum who wanted to know how to open a password protected Excel workbook/spreadsheet. Although the question was Access specific, the code can easily be used in Word, PowerPoint,…
'---------------------------------------------------------------------------------------
' Procedure : OpenPwdXLS
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Open a password protected Excel Workbook
' Copyright : The following may be altered and reused as you wish so long as the
' copyright notice is left unchanged (including Author, Website and
' Copyright). It may not be sold/resold or reposted on other sites (links
' back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' strWrkBk : Full path and Filename of the Excel Workbook to open
' sPwd : Password to unlock/open the Workbook in question
'
' Usage:
' ~~~~~~
' OpenPwdXLS "C:\Testing\book1.xls", "MyPassword"
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2009-Jun-11 Initial Release
'---------------------------------------------------------------------------------------
Function OpenPwdXLS(strWrkBk As String, sPwd As String)
'Use late binding so no reference libraries are required
On Error GoTo Error_Handler
Dim xlApp As Object
Dim xlWrkBk As Object
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application") 'Bind to existing instance of Excel
If Err.Number <> 0 Then
'Could not get instance of Excel, so create a new one
Err.Clear
On Error GoTo Error_Handler
Set xlApp = CreateObject("excel.application")
Else
On Error GoTo Error_Handler
End If
xlApp.Visible = True 'make excel visible to the user
Set xlWrkBk = xlApp.Workbooks.Open(strWrkBk, , , , sPwd)
'... the rest of your code goes here
Error_Handler_Exit:
On Error Resume Next
Set xlWrkBk = Nothing
Set xlApp = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: OpenPwdXLS" & vbCrLf & _
"Error Description: " & Err.Description _
, vbOKOnly + vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function |
Share and Enjoy
MS Access - Excel Automation, MS Access VBA Programming, MS Word VBA Programming | No Comments »
