Transaction Log for Microsoft Access Database

Here is one of the most useful pieces of code I have every come accross the Internet for programming an access database. This code will track all changes made by a user, meaning it will keep track of what data was changed, who changed it and when it was changed. This functionality is usually called a transaction log or a change log. I have copied the code directly from this page:http://www.tek-tips.com/faqs.cfm?fid=291 . The code was written by Michael Red a regular contributor at Tek-tips, which is a wonderful website that I have used many times for finding answers to many programming questions.

I have learned a few things not explained in his original code:

1) Put this code in a Module.
2) Call the basLogTrans function from the BeforeUpdate event of any form that you want to track changes.
3) Pass 3 values to the basLogTrans function: FormName, Name of Field with Unique ID, and the Unique ID.
In my implementation, I use Me as the form name, "CustomerID" as the Unique ID field, and CustomerID to pass the actual customer id value. So here is how I would call the basLogTrans function:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Call basLogTrans(Me, "CustomerId", CustomerId)

End Sub

4) Mike says the code will work only on a form formated as single form, but I see it also works on a datasheet form.
5) He alos says that Access Security needs to be enabled, but I'm not sure it's true. When I used his original code for CurrentUser(), I got back "Admin" as the user that was making changes. But what I really want to know is the NT User ID of whoever is making the changes, therefore, I use Environ("Username"). If you have problems with the Environ function, please see here how to enable the Environ Function properly.
6) The rest of the instructions are pretty clear, but just to be safe, remember to add a reference for DAO, bind the form to a table, but who's to say this won't work on a Select Query? I don't know, why don't you tell me later? Make 2 tables, one called tblHist and the other tblHistMemo. First create tblHist, with the fields indicated in code comments in the function called basAddHist, then make a copy of it, name it tblHistMemo and change the OldVal and NewVal fields to datatype Memo.

That's it, it should work easily.



OK, HERE IS THE ORIGINAL VBA CODE:
All this needs to be placed in a Module, and called from the BeforeUpdate event of a form.


Public Function basLogTrans(Frm As Form, MyKeyName As Variant, MyKey As Variant) As Boolean

'Michael Red 8/1/2002 Transaction log for Ms. Access BOUND forms.

'The following three functions and table definitions are
'(should be) sufficient to establish a 'poor mans' transaction
'log for Ms. Access bound forms.

'As noted in basAddHist, you will need to create the tables
'for History (general fields) and Memo History (history of
'JUST memo fields).

'Also, note that these functions are designed to work in
'conjunction with Ms. Access Bound forms where the view is
'set to single form. Further, the history will only capture
'the user if the db App is "secured".

'In the following, "MyKey" refers to the Record ID (Unique value)
'for the single record which the form is 'tied' to.

Dim MyDb As DAO.Database
Dim MyCtrl As Control
Dim MyMsg As String
Dim Hist As String


'In the Statement block below, basFlgValidRec is a routine which
'includes validation of all fields necessary to 'define' a record
'is valid. Commented out here, as I do not know what validation
'rules need to be checked/enforced.

' If (Not basFlgValidRec) Then
' basLogTrans = False 'Let User know of FAILURE!!
' Exit Sub
' End If


For Each MyCtrl In Frm.Controls
If (basActiveCtrl(MyCtrl)) Then 'Consider replacing w/ test for "ControlSource"
If ((MyCtrl.Value <> MyCtrl.OldValue) _
Or (IsNull(MyCtrl) And Not IsNull(MyCtrl.OldValue)) _
Or (Not IsNull(MyCtrl) And IsNull(MyCtrl.OldValue))) Then
If (MyCtrl.ControlType = dbMemo) Then
Hist = "tblHistMemo"
Else
Hist = "tblHist"
End If
Call basAddHist(Hist, Frm.Name, MyKey.Name, MyCtrl)
End If
End If
Next MyCtrl

basLogTrans = True 'Let User know sucess

End Function
Public Function basActiveCtrl(Ctl As Control) As Boolean
Select Case Ctl.ControlType

Case Is = acTextBox
basActiveCtrl = True

Case Is = acLabel
Case Is = acRectangle
Case Is = acLine
Case Is = acImage
Case Is = acCommandButton
Case Is = acOptionButton
Case Is = acCheckBox
basActiveCtrl = True
Case Is = acOptionGroup
Case Is = acBoundObjectFrame
Case Is = acListBox
basActiveCtrl = True
Case Is = acComboBox
basActiveCtrl = True
Case Is = acSubform
Case Is = acObjectFrame
Case Is = acPageBreak
Case Is = acPage
Case Is = acCustomControl
Case Is = acToggleButton
Case Is = acTabCtl

End Select
End Function
Public Function basAddHist(Hist As String, Frm As String, MyKeyName As String, MyCtrl As Control)

'tblHist
'FrmName Text 80 Name of the form where change Occured
'FldName Text 80 Field Name of the changed value
'dtChg Date/Time 8 Date/Time of Change (MACHINE value!!)
'OldVal Text 255 Field Value BEFORE change
'NewVal Text 255 Field Value after change
'UserId Text 50 User who Made Change
'MyKey Variant ?? KeyField as Indentified by Caller
'MyKeyName Text 80 'Key Field Contents

'tblHistMemo is the same structure except the "type" for the fields
'OldContents and NewContents are Memo (and therfore the length is "??")

Dim dbs As DAO.Database
Dim tblHistTable As DAO.Recordset

Set dbs = CurrentDb
Set tblHistTable = dbs.OpenRecordset(Hist, dbOpenDynaset)

With tblHistTable
.AddNew
!MyKey = Forms(Frm).Controls(MyKeyName)
!MyKeyName = MyKeyName
!frmName = Frm
!FldName = MyCtrl.ControlSource
!dtChg = Now()
' !UserId = Environ("Username") 'Orlando's Way
!UserId =CurrentUser() 'Mike's way, example of current user would be Admin
!OldVal = MyCtrl.OldValue
!NewVal = MyCtrl
.Update
End With

End Function

First and Last Access Function not supported in SQL Server

In a Microsoft Access database, in order to return the first or last record in the result set returned by a query, you can use the First and Last Functions. The syntax is simple: First(expr) or Last(expr). If you try to use these functions in SQL Server, however, they will not work. You will get a message similar to this:

Server: Msg 195, Level 15, State 10, Line 2
'First' is not a recognized function name.

In order to repeat the desired outcome in SQL Server, simply use the MIN and MAX function.

ACCESS SQL VERSION:
SELECT
ContractorID, FIRST(Contractor)
FROM
tblContractor
WHERE
ContractorID = "ABC6783"
GROUP BY
ContractorID

(T-SQL) SQL SERVER VERSION:
SELECT
ContractorID, MIN(Contractor)
FROM
tblContractor
WHERE
ContractorID = 'ABC6783'
GROUP BY
ContractorID

Loop in VBA Easy Example

There are different versions of looping in VBA. Here is an example of the Do While version.

Sub letsLoop()
Dim intCounter As Integer

intCounter = 1 'Set the counter to 1

Do While intCounter <3'Loop while value of intCounter less then 3
MsgBox intCounter 'show message box with value of intCounter
intCounter = intCounter + 1 ' increase value of intCounter by 1
Loop 'loop again

End Sub

Capture Signature and Date Stamp in Excel Worksheet

With all the Sarbanes-Oxley (SOX) requirements going around lately, there has been a big push to making staff attest that they have reviewed the various Excel reports they are sent. One way to do that is to have them sign and date the report and save the report somewhere on the network. Later I will run an Excel macro that goes and looks for everyone’s signature and produces a report of who has signed and when.

Anywho, for the signature portion, I've added a sheet in the Excel reports, the sheet is called Signature. This sheet has a check box. Clicking this check box called chkSignature, populates cells C2 and C3 with the Windows User ID and the Date.

For the user's Windows User ID, we use the method Environ("username"). And for the Date we use well, the Date function.

Here is the code behind the click event of check box chkSignature:

Private Sub chkSignature_Click()

If chkSignature.Value = True Then
Sheets("Signature").Range("C2").Value = Environ("username")
Sheets("Signature").Range("C3").Value = Date
Else
Sheets("Signature").Range("C2").Value = ""
Sheets("Signature").Range("C3").Value = ""
End If

End Sub

Automatically Maximize Access Form

To maximize an Access form automatically, use the "Maximize" method of the DoCmd Object.

Example, let's say I have a form called MainForm, on the "On Load" Event property of the form, I would use this sub procedure:

Sub MainForm_Load()

DoCmd.Maximize

End Sub

Find the Number of Series in Excel Chart

While adding a trend line to a group of charts, I needed to know how many data series where in the Graph's Series Collection. I will add an equal amount of trend lines.


Sub countseries()

MsgBox ActiveChart.SeriesCollection.Count

End Sub

Using a Combo Box in Excel

From the top menu, navigate thru: View / Toolbars / Control Toolbox



When the Control Toolbox appears, Select the Combo Box control. With your mouse, make a rectangular shape on the excel sheet.



Define the choices that will be available in the Combo Box. In this example, the names of fruits are written on Column F from row 1 thru 4.




Tell the combobox where to get it's data from. Right click on the combobox to get it's properties.




Then in the ListFillRange, enter the range of cells that hold the combox values. In this case F1:F4.



That's all there is to it, close design view and your combo box should be working fine.

Securing an Access Database

A reader from Kuwait wrote the other day...


Orlando,

I was browsing for some help on the net and just saw
your name, I had a question, could you please help me.

My question is, i have developed a database, and i
want to give it to my friend, the question is, i want
to secure the Queries, Form & Reports from being
edited or re-designed, i dont want these to be visible
whenever he uses it. he should be able to print &
update database, but he should not be able to see what
queries i have set as well he should not be able to go
to the design view to modify my forms & Reports.

Could you please help.

Regards,

Oswald
Kuwait


Oswald...

There are a couple of different ways that the above can be accomplished.

1) User Level Security. But I don't recommend it because it is difficult to maintain.

2) A combination of setting the Start Up options, tweaking the properties of the Forms, and making an mde file.

You will have to play around with option number 2 to get the desired results. But here are a few pointers.

From the menu bar, go to Tools / Start Up and un-check "Display Database Window". Next time the database starts, the database window will not be available. If you want to see it, then you would go to Window / Unhide and select the database window. If you choose to not see the database window, you would have to have some form that would serve as the main menu for your database.




Another thing that you could do is hide your database objects, but this is the least effective way, because in the Database Options there is the option to View Hidden Objects. But anyway, here is how you could hide them: Right Click on the object to arrive at the Properties window and check Hidden.




Another alternative is to allow Design Changes on Forms Only in Design View. And to not allow viewing the form any other way then you intended. Forms can be viewed as FORMS, DATASHEETS, PIVOT TABLES, PIVOT CHART. So make sure that in the Forms Format Properties you unselect the view types you do not want the user to use.







Finally, after you have implemented a combination of the tips above, make the database an MDE file. Go to Tools / Database Utilities / Make MDE file.

How to Filter Access Subforms based on a control from a Main Form

If you are reading this then you have probably noticed that filtering a Subform based on a control from the Main form is a pain in the neck.

Here's one way to filter the subform by changing the underlying form.

Sub frameTaskStatus_Click()
Dim intStat As Integer
intStat = frameTaskStatus.Value
' 1 = all
' -1= closed
' 0 = open
' 2 = new open frmCreateNewRequest

Select Case intStat
Case -1
Me!sbfrmMainRequests.SourceObject = "subfrmRequestsFiltered"
Case 0
Me!sbfrmMainRequests.SourceObject = "subfrmRequestsFiltered"
Case 1
Me!sbfrmMainRequests.SourceObject = "subfrmRequestsAll"
Case 2
Me!sbfrmMainRequests.SourceObject = "subfrmRequestsNew"
End Select

End Sub


Cases -1 and 0 (closed and open, respectively) make subfrmRequestsFiltered the underlying subform. This subform's datasource is a query that applies a filter also based on the value selected in frameTaskStatus.

Case 1 makes subfrmRequestAll the underlying subform in the Main form. subfrmRequestAll is also based on a query but it is not filtered.

Case 2 makes subfrmRequestsNew the underlying subform in the Main form. subfrmRequestsNew is a data entry only form.

How to Determine the Version or Edition of SQL Server

Open Query Analyzer, connect to any database on your server and type one of the following:

SELECT SERVERPROPERTY('ProductLevel')
SELECT @@VERSION
SELECT SERVERPROPERTY('ProductVersion')

If you are not sure whether you are running an edition of the SQL Server 2000 database engine or MSDE 2000, execute the following query:
SELECT SERVERPROPERTY('Edition')

How to run an Access Macro from Access VBA

DoCmd.RunMacro "macroName"

A word of Advice for my International Programming Brothers

Collation is the way in which data will be sorted in SQL Server.

When building SQL Server databases for American clients, be sure to define the database collation as SQL_Latin1_General_CP1_CI_AS, which means Latin characters, case insensitive, accent sensitive. This is the default setting for Windows Desktops in America. If you are developing in another country, your installation of Windows will probably have a different default language setting. If when you created the SQL Server database, you chose “default” for the collation settings, the database will have been created with a different collation setting, a non-American collation setting.

If you are developing a multi-language capable database, an international database, define your table fields as nvarchar whenever possible. Nvarchar ia a UNICODE character, which means it uses two bytes per character, as opposed to ASCII characters which are one byte in length and thus limits the number of possible ASCII characters

Access: How to Refresh Form Data or Data on a Control

Simple:

use the .Requery Method

Forms!FormName.ControlName.Requery

or if you want to refresh all controls in the form, then

Forms!FormName.Requery

User Friendly Prompt for Requesting Required Information in Access Form

I have a form which has a SAVE RECORD button, which runs a Save Record Macro. The underlying form's table has a Required field. When the Required field is not populated and the SAVE RECORD button is clicked, a popup message prompts the user for the Required field. The problem is that the popup message prompts for the field name as defined in the table and in some cases this might be confusing to the user.

In order to make a user friendly popup message, you could add a condition to the Save Record Macro which checks whether the Required field control is populated or not with: is null(Required Field). If it is true, exit the macro, else continue.

How to Run an Access Macro from Excel VBA

In excel you would need the following Sub procedure:

Sub RunMacroInAccess()

Dim Acc as Object
Set Acc = CreateObject("Access.Application")
Acc.OpenCurrentDatabase("C:\My Documents\AccessDatabase.mdb")
Acc.DoCmd.RunMacro "AccessMacroName"

End Sub

Run an Excel Macro from an Access Database

If you need to run an Excel Macro from within an Access database, place this vba code on the click event of a command button called cmdExcel_Macro. There is no need to add an Excel Reference Library in Access.

Private Sub cmdExcel_Macro_Click()

Dim excelApp As Object ' Declare Excel Application as Object
Dim workbook As Object ' Declare Excel Workbook as Object
Set excelApp = CreateObject("Excel.Application")
Set workbook = excelApp.Workbooks.Open("C:\Temp\FileName.xls") ' Full path of Excel book that has Macro
excelApp.Run "ExcelMacroName" ' Name of the Excel Macro to run
workbook.Close False
excelApp.Quit
Set excelApp = Nothing

End Sub

Loop Through Charts in Excel Worksheet

Here’s a quick piece of code that will generate message boxes for every chart in the active worksheet with the name and top position of the chart

Sub LoopThruCharts()

For i = 1 To ActiveSheet.ChartObjects.Count
ActiveSheet.ChartObjects(i).Select
MsgBox ActiveSheet.ChartObjects(i).Name
MsgBox ActiveChart.Parent.Top
Next i

End Sub

Detect and Remove Spyware

Run the free version of this program called Ad-aware on your computer and I bet you will be surprised by the amount of spyware or tracking cookies that have been installed on your computer without your knowledge. I ran this product after I started noticing that whenever I Googled something, pop-ups would appear on my screen advertising products or services related to my search terms. I thought first of all, Google does not have pop-ups, and second, how the heck is it that these pop-up screens seem to know exactly what I'm searching for.

Anyway, I have used this product on different types of operating systems and it has never created any problems.

You can find the program at this site: www.lavasoftusa.com

Using Shortcut Keys in Windows XP

When I'm typing, I hate reaching over for my mouse; it "breaks the flow of things".

Solution: Create a shortcut key for frequently used programs or websites.

Basically you right click on the file for which you want to create a shortcut key in order to access the file's properties window.

Set the cursor (mouse) in the shortcut key textbox and press any letter. If for example you press the letter "A", you will see that the textbox value has changed to Ctrl + Alt + A. In order to launch the file, typically an *.exe file, you would then press 3 keys at the same time: Ctrl + Alt + A.

The same can be done to launch a website; in this case you would first add the website to your favorites, then right click on the favorites entry and follow the process as described above.

Instant Messaging Aggregator

If you use multiple instant-messaging services, such as AIM, ICQ, MSN, and Yahoo! you might want to aggregate them into one tool. My favorite is called:Trillian. You can download it for free at www.ceruleanstudios.com