MS Access

IIF
It gives back a value according to the expression.

Syntax:

Example: SELECT IIf(tb_mytable.contract_type='L',worktable.after,plantable.after) FROM tb_mytable

DLookUp
In Access, the DLookup function returns a value from a specified set of records (or domain).

Syntax:


 * expression is a field, calculation, control on a form, or function that you wish to return.
 * domain is the set of records. This can be a table or a query name.
 * criteria is optional. It is the WHERE clause to apply to the domain.

For example: DLookup("[UnitPrice]", "Order Details", "OrderID = 10248")

In this example, you would be retrieving the UnitPrice field from the Order Details table where the OrderID is 10248. This is the same as the following SQL statement: SELECT UnitPrice AS Expr1 FROM [Order Details] WHERE ((([Order Details].OrderID)=10248));

User Defined Functions
You can use your public functions declared in the modules, like this:

Public Function TestNull(p_wert1, p_wert2) As Variant If IsNull(p_wert1) Then TestNull = p_wert2 Else TestNull = p_wert1 End If End Function

Then you can use your function in one view:

SELECT tb_Local.local_ID, TestNull(tb_Local.local_segment,) AS local_abrev FROM tb_Local

Move to the last cell
To avoid that a user edit a MDB or ADP file use the following code to protect your code and data:

Excel.Application.ActiveCell.SpecialCells(xlLastCell).Select

Retrieving the last auto generated number
To retrieve the identity value of a table that has a primary key as auto number, you can use the following SQL statement:

SELECT @@IDENTITY AS ident

Reports with columns
You can create reports with many columns to display information in the same line. It’s specially important to create labels:


 * Click on File -> Page setup
 * Click on the Tab "Columns"
 * Set how many columns do you want
 * Set the column width and height

Problems with SQL Syntax, table names, field names
If you receive a syntax error in your program and you know that the syntax is correct then try to look for reserved words like USER, LOGIN, PASSWORD, AND, OR, etc. If it does not help, take a look on these articles:


 * Reserved words in Microsoft Access 97
 * Reserved words in Access 2002 and later

Protecting your source code and data:
You have to set the "Start Properties" of the MDB file. Follow the instructions below:


 * Click on the menu "Extras -> Start"
 * Uncheck the following Checkboxes like the image below:




 * You should generate a MDE file to avoid the user to access the source code. But the user can still access and change the data.
 * You should also create a password for your database. Click on the menu "Extras -> Security -> Password"
 * In the next step you will learn how to avoid the SHIFT-key.

Avoiding the SHIFT-Key
To avoid that a user edit a MDB or ADP file use the following code to protect your code and data:

Public Sub SetProperty(ByVal strPropName As String, varPropType As Variant, varPropValue As Variant) Const conPropNotFoundError = 3270 Dim dbs As Database Dim prp As Property On Error GoTo ErrorSet Set dbs = CurrentDb dbs.Properties(strPropName) = varPropValue 'Property setzen ExitSet: Exit Sub ErrorSet: If Err = conPropNotFoundError Then 'Fehler "Property nicht gefunden -> Property erstellen" Set prp = dbs.CreateProperty(strPropName, DAO.DataTypeEnum.dbBoolean, varPropValue) dbs.Properties.Append prp Resume Next Else 'unbekannter Fehler -> abbrechen 'Resume ExitSet MsgBox "Beim Setzen der Grundeinstellungen" & vbCrLf & "ist ein Fehler aufgetreten!", vbCritical End If End Sub Public Sub VerifyPropertyForAllowByPassKey On Error GoTo Fatal Debug.Print "AllowBypassKey = " & CurrentDb.Properties("AllowBypassKey").Value ' CommandBars("Form Design").Visible = bValue ' CommandBars("Database").Visible = bValue Exit Sub Fatal: If Err.Number = 3270 Then   ' ' Eigenschaft wurde nicht gefunden. SetProperty "AllowBypassKey", DB_Boolean, True End If End Sub Public Function getDBPropertyForAllowByPassKey As Boolean Dim dbs As Object Dim propertyAsInt As Integer On Error GoTo Fatal Set dbs = CurrentDb propertyAsInt = dbs.Properties("AllowBypassKey").Value getDBPropertyForAllowByPassKey = IIf(propertyAsInt = -1, True, False) Exit Function Fatal: MSGError Err End Function Create a Form or a Menu Option with a checkbox and add the code to the open event of the form:

Private Sub Form_Open(Cancel As Integer) VerifyPropertyForAllowByPassKey Me.chkBoxAllowByPasskey.Value = getDBPropertyForAllowByPassKey End Sub

Force a Form to save the data into the table
Execute the following instruction:

DoCmd.RunCommand acCmdSaveRecord

Most used commands and common situations in MS Access. This document describes some problems and solutions using the database MS Access and the language Visual Basic for Application (VBA). 