How Do I Turn Off Compact On Close and Name Auto Correct in Access on Start Up?

Create a function called SetMyOptions()

Function SetMyOptions()

'turn off Compact On Close
    Application.SetOption "Auto compact", False

'turn off Name Auto Correct
    Application.SetOption "perform name autocorrect", False

End Function

Run this function in an AutoExec macro.

How do I run a macro or code when the database starts?

How Do I Disable Layout View for Forms and Reports in Access at Start Up?

Create a function called SetMyOptions()

Function SetMyOptions()

'disable Layout View for forms and reports
    Application.SetOption "DesignwithData", False

End Function

Run this function in an AutoExec macro.

How do I run a macro or code when the database starts?

How Do I Maximize an Access Database at Start Up?

Create a function called SetMyOptions()

Function SetMyOptions()
'maximize Access
    Application.SetOption "maximized", True
End Function

Run this function in an AutoExec macro.

How do I run a macro or code when the database starts?

How Do I Minimize the Ribbon In Access at Start Up?

Create a function called SetMyOptions()

Function SetMyOptions()
'minimize Ribbon

     If Not (CommandBars("Ribbon").Controls(1).Height < 100) Then
        CommandBars.ExecuteMso "MinimizeRibbon"
     End If

End Function

Run this function in an AutoExec macro.

How do I run a macro or code when the database starts?

How Do I Minimize the Access Navigation Pane on Start Up?

Create a function called SetMyOptions()

Function SetMyOptions()
'Open Nav Pane collapsed
    CurrentDb.Properties("Startupshowdbwindow") = False
    SendKeys ("{F11}")
    SendKeys ("{F11}")
End Function

Run this function in an AutoExec macro.

How do I run a macro or code when the database starts?

Access 101: How Do I Replace System Error Messages With Custom Messages?

Access provides a number of error messages, but many are confusing to the users. I find it sometimes useful to replace the system error messages with custom error messages. The Null primary key is a case in point.

When you try leave a record on a form without a primary key value, the form will display the following error.

But you might want to replace this with your own error message, identifying the actual field.

At it's simplest, you can use the Form_Error event to trap for this and otherrecord-level errors. Something like this:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

If DataErr = 3058 Then

MsgBox "You must give Account Number a value."

Response = acDataErrContinue

Else 'msgbox DataErr

Response = acDataErrDisplay

End If

End Sub

3058 is the error number for the null primary key. The "Else" section will let any other error display the normal system error message.

If you want to know the number for another error, just un-comment the msgbox and you'll get the error number. Add any additional errors as an ElseIf.

Other errors you can trap:

Limit to List: 2237

Input Mask: 2279

Required field: 3314

Validation Rule: 2107

Bad Data Value: 2113

Access 101: How Do I Bypass Start Up Options?

In a previous post, ( How Do I Configure My Database Start Up? ) I discussed how to set Start Up options for Access. But what if you want the database to open without running those options?

Access provides a Bypass Key to do this. Simply hold the Shift Key while opening the database, and the start up options will not run. Depending on your security settings, you might see one or more security messages. You must continue to hold the Shift Key down while you answer the security messages.

It is also possible to disable the Bypass Key if you don't want your users to be able to bypass the options. To do this, set the AllowBypassKey property to False in a macro or VBA module.

Here are some of good, advanced resources:

• http://www.databasedev.co.uk/disable_shift_bypass.html
• http://www.tek-tips.com/faqs.cfm?fid=1429
• http://www.mvps.org/access/general/gen0040.htm

But be careful. It's possible to lock yourself out if you don't know what you're doing. In general, I discourage novice users from turning off the bypass key until they know more about the implications of doing so. At the very least, test it on a copy of your database.

How do I run a macro or code when the database starts?

Access has a special macro called Autoexec, which automatically executes when the database opens. You can use this macro to do practically anything: open one or more forms in hidden mode, relink your tables, pop up a tickler list, or whatever startup functions your application requires.

At its simplest, simply create a new macro, choose the Actions and associated arguments required, and save it under the name Autoexec. The next time you open your application, this macro will execute.

However, most experienced developers agree that macros are not the optimal solution. Most prefer VBA code to macros. Fortunately, it is simple to convert a macro to VBA code (see: How Do I Convert A Macro to VBA Code?) .

So a much more elegant solution would be to create an Autoexec macro that does what you want it to do. After you test it, convert it to a VBA function.

The conversion will create a new function called Autoexec() in a general module. Next delete all the actions from your existing Autoexec macro and replace it with the RunCode action, with Autoexec() as the function name. Like this:

Access 2010 and 2013

 

In Access 2000 - 2007

How Do I Convert A Macro to VBA Code?

Although Microsoft has made great strides in improving the capabilities of macros in Access, most experienced developers agree that Visual Basic for Applications (VBA) code is still far superior to using macros in Access client apps. (Web apps, of course, cannot use VBA, so the following is only pertinent to applications that run in the Access Client.)

So suppose you have a completed macro that you want to convert to VBA? Fortunately, Access makes it easy.

Access 2010 and 2013

Open the Macro in Design View

 

Click the File Tab then

1. Click Save As
2. Save Object As
3. Save As

You will get a pop-up Dialog box.  Give the macro a meaningful name in the top box and in the AS box, choose Module. Click OK.  

Click OK.  A second box will appear asking you if you want error trapping and comments.

Leave both checked and click Convert.

The macro will be converted and saved in a Module.

'------------------------------------------------------------
' MyMacro
'------------------------------------------------------------
Function MyMacro()
On Error GoTo MyMacro_Err

    DoCmd.TransferSpreadsheet acExport, 8, "table7", "table7.xls", True, ""

MyMacro_Exit:
    Exit Function

MyMacro_Err:
    MyMacroError$
    Resume MyMacro_Exit

End Function

'------------------------------------------------------------

In Access 2007

Open the macro in Design View, Click the Office Button (Pizza), Choose Save As, and you will immediately be given the Save As dialog.

A second box will appear asking you if you want error trapping and comments. Leave both checked and click Convert.

In Access 2003 or previous

In the Database Window, right-click on the macro you want to convert and choose Save As. Everything else works the same.

How Do I Configure My Access Database Start Up?

This article has moved: How Do I Configure My Access Database Start Up?

Searching for a Wildcard in a “LIKE” Criteria

In a query, the LIKE operator allows the user to query for a character or group of characters anywhere within a text field.  It does this by matching the field to a string which mixes the character(s) you want to match with wildcard characters like * and ?. Some examples:

• FirstName LIKE "C*"  will return any name that starts with a C (i.e. Carlton, Clark)
• LastName LIKE "*-*"  will return any last name that has a hyphen anywhere in the field (i.e. Flickema-Carlson, Smith-Jones)
• PONum LIKE "C????"   will return and PO number that starts with a C and has exactly 5 characters. (i.e. CSIDF, C24DG)
• PONum LIKE "C##"  returns all values that start with a C, is exactly 3 characters long, and characters 2 and 3 MUST be numbers. (i.e. C45, C16)

So how can I search for a wildcard character itself?  I can simply enclose the wildcard character with brackets [].

For instance, if I wanted to find any value that has an asterisk (*) anywhere in it, I could do this:

• PO LIKE "*[*]*"

If I wanted to find any value that starts with a hash mark(#), I could do this:

• Check LIKE "[#]*"

If I wanted to find any value ending with a question mark (?), I could do this:

• Comment LIKE "*[?]"